Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

These are 2 common delimited, text file transformation problems I'd like to find scripts to do (I dont know if awk or perl is best for these tasks).

csh is the default shell on my Solaris10 unix server, so solutions that work for either the csh or sh shells would be preferred.

PROBLEM 1:

I have the following 2 delimited ASCII files: File1.dat and File2.dat

File1.dat (map file):

1|A  
2|B  
3|C  
4|D  
5|E  
6|F  
.  
.  
.  
26|Z  
27|Who  
28|What  
29|Where

File2.dat

Field1|Field2|Field3  
1|John|20120101  
3|Amy|20120103  
6|Larry|20120104  
3|Kevin|20120203  
8|Fred|20120623  
4|Mary|20120728  
26|Eric|20120819  
28|Larry|20120917

I want to use the map that is represented in File1.dat to transform File2.dat into a 3rd file File3.dat that looks like the following. Essentially I am using the map in File1.dat to create/populate a new column into my file so that at the end, the new transformed file has 4 columns, as follows:

File3.dat

Field1|Field2|Field3|Field4  
1|A|John|20120101  
3|C|Amy|20120103  
6|F|Larry|20120104  
3|C|Kevin|20120203  
8|H|Fred|20120623  
4|D|Mary|20120728  
26|Z|Eric|20120819  
28|What|Larry|20120917

PROBLEM 2:

I want to keep only the 1st record (in a series of sorted records) of a family of records that share the following commonality [Field 2, Field3, Field4 have the same value across records]

I have the following initial delimited file File1.dat (15 records each with 4 Fields). To the left is not part of the file its just a Record# so we can keep track of what is happening.

File1.dat

    Field1|Field2|Field3|Field4
1.    20120227|Andy|101|34  
2.    20120315|Andy|101|34  
3.    20120415|Andy|101|36  
4.    20120417|Andy|103|37  
5.    20120417|Andy|103|37  
6.    20120227|Jane|101|34  
7.    20120315|Jane|101|34  
8.    20120415|Jane|101|36  
9.    20120417|Jane|103|37  
10.   20120417|Jane|103|37  
11.   20120227|Bob|101|34  
12.   20120315|Bob|101|34  
13.   20120415|Bob|101|36  
14.   20120417|Bob|103|37  
15.   20120417|Bob|103|37

I want to convert this 15 record file (File1.dat) into a newfile (File2.dat) that looks like the following (keeps only these 9 records).

As you can see it takes out the duplicate records in File1.dat, where the definition of a duplicate record equals [ Field2 & Field3 & Field4 have equal values across any 2 or more records ].

File2.dat

1.   20120227|Andy|101|34
3.   20120415|Andy|101|36
4.   20120417|Andy|103|37
6.   20120227|Jane|101|34
8.   20120415|Jane|101|36
9.   20120417|Jane|103|37
11.  20120227|Bob|101|34
13.  20120415|Bob|101|36
14.  20120417|Bob|103|37

Conclusion:

Please let me know where in a solution code it addresses the following 2 things:

a) The fact the the delimiter in my files is a pipe | character
b) What part of the code refers to Field1, Field2, Field3, Field4, etc

share|improve this question
3  
Hi Andy and welcome to Unix & Linux site. Please review the FAQ. It's against the policy of the site to simply ask people to write scripts and do your work for you. We're here to help not do! What have you tried thus far? Looking at your questions these are very basic problems that you should be able to resolve with simple googling. –  slm Apr 7 '13 at 0:39
1  
That you use '|' as delimiter is awkward, but solvable (use "\|" if required, perhaps needs "\\|" if it goes through a round of mangling before). Read up on perl, you will to do a bit of programming. –  vonbrand Apr 7 '13 at 0:42
    
As a general rule, in the *nix world you should use a TAB or a simple space as a delimiter, many programs use that as default. In any case, never use |, it just makes your life harder. –  terdon Apr 7 '13 at 1:01

1 Answer 1

Perl solution:

Problem 1.

File1.dat is read into a hash, the first column is the key, the second column is the value. Then, File2.dat is processed, and the value from the hash is inserted into the array representing the values on one line according to the key taken from the first (in Perl, zeroth) column.

#!/usr/bin/perl
use warnings;
use strict;

my %tab;

open my $F1, '<', 'File1.dat' or die $!;
while (<$F1>) {
    chomp;
    my ($key, $value) = split /\|/; # Here, the delimiter is mentioned.
    $tab{$key} = $value;
}

open my $F2, '<', 'File2.dat' or die $!;
while (<$F2>) {
    chomp;
    my @cols = split /\|/;
    splice @cols, 1, 0, $tab{$cols[0]};
    print join('|', @cols), "\n";
}

You can get similar output with the standard utils:

join -a2 -t'|' <(sort File1.dat) <(sort File2.dat) | sort -n

Problem 2.

Remember what combination of fields you have already seen in a hash. If the combination is new, print the line, otherwise do nothing.

#!/usr/bin/perl
use warnings;
use strict;
use feature 'say';

my %seen;
while (<>) {
    chomp;
    my @fields = split /\|/, $_, 2;          # Only split into 2 fields.
    say unless exists $seen{$fields[1]};     # Do not print if already seen.
    undef $seen{$fields[1]};                 # Mark the key as seen.
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.