0

I want to re-arrange the data (Header and Detail) in multiple pipe delimited text files, in the Field|Order specified in a reference master file and create a single output file, using shell script. I have the code below, but not working as expected. The while loop is getting the order of the column from master, and rearranging. But need to account for below functionality. Looking for a Solution in Shell Script/awk.

There could be columns missing or new columns in the input files, when compared to the Reference master file. The order in the Reference master file could change in future and columns could be added to or deleted from any position. Accept multiple input files.

Sample Input file A:

Name|Amount|Vehicle|City|Visits|Indicator

Jack|123.22|Volkswagen|Altamonte|1|Y

Sam|23.1|Audi||3|N

|21.4|Ford|NewYork||Y

Sample Input file B:

Suffix|Name|Visits|Vehicle|State|Indicator|City|Gender

Mr|Jack|1|Volkswagen|NJ|Y|Hoboken|M

Mr|Rob|6|Buick||N|Stamford|

Reference Master File:

State|1

City|2

Vehicle|3

Gender|4

Name|5

Amount|6

Output File:

State|City|Vehicle|Gender|Name|Amount

|Altamonte|Volkswagen||Jack|123.22

||Audi||Sam|23.1

|NewYork|Ford|||21.4

NJ|Hoboken|Volkswagen|M|Jack|

|Stamford|Buick||Rob|

Shell script:

/*Comment::Reads the input column names and assign number based on order for*/
/*Comment::each column in the input file.*/
cat infile.out | head -1 | tr -s '|' '\n' | cat -n > infile_tmp.out
rm -f final.out

/*Comment:compare the master file column names with input file column names and 
/*Comment:get the column number from master file*/ 
while read line; do 
touch final.out
vChkClmn=`echo $line | cut -d "|" -f1`
vMasterClm=`grep -i $vChkClmn infile_tmp.out | cut -d " " -f1`
echo "$vMasterClm" >> final.out
done < master_file.out

rm -f final_master_list.out
echo `cat final.out` | sed 's/[^ ]* */\$&/g' | sed 's/ /\,/g' > final_master_list.out
vOrder=`cat final_master_list.out`
echo "Value of order is $vOrder"
awk -F\| '{print '$vOrder'}' OFS=\| infile.out

1 Answer 1

0
#!/usr/bin/perl

use strict;

my $master = shift;
open(M,"<","$master") || die "couldn't open $master:$!\n";
chomp(my @master = split(/\|/,<M>));
close(M);

print join("|",@master),"\n";

my @fields=();
while (<>) {
  if ($. == 1) {
    chomp(@fields = split /\|/);
    next;
  };

  chomp(my @row = split/\|/);
  my @out = ();

  foreach my $m (@master) {
    my $o='';
    for (my $i=0; $i < $#row; $i++) {
      if ($fields[$i] eq $m) {
        $o = $row[$i];
      };
    }
    push @out, $o;
  };

  print join("|",@out),"\n";
} continue {
    close ARGV if eof; # reset the line counter after each input file
}

This reads in the reference master file (which must be the first argument on the command line) and splits it into an array (@master) holding the field names.

After that, it reads all remaining filename arguments and:

  • if it is the first line of a file, get the field headers by splitting the line into array @fields
  • otherwise, split the line into array @row, and loop though both @master and each element of @row. When the field name of a row element matches an element in @master, add that field's value to the output array (@out), otherwise add the empty string.
  • print the @out array joined by | characters.
  • the @out array is reset to empty on every input line.

Save it as, e.g., rearrange.pl, make it executable with chmod +x rearrange.pl and run it as:

$ ./rearrange.pl master.txt input1.txt input2.txt 
State|City|Vehicle|Gender|Name|Amount
|Altamonte|Volkswagen||Jack|123.22
||Audi||Sam|23.1
|NewYork|Ford|||21.4
NJ|Hoboken|Volkswagen||Jack|
|Stamford|Buick||Rob|

Note: this is not limited to just two input files. It can process any number of input files at a time. There are only two restrictions:

  1. The master reference file (e.g. master.txt) MUST be the first argument on the command line.

  2. There must be at least one input file or the input file must be piped into the script.

    Note: only one input file can be piped - the script has no way of knowing which lines are header lines and which are data lines if more than one file is piped in.

8
  • FYI you could re-implement this algorithm in bash (or some other sh with arrays) if you really insisted on doing so. I wouldn't recommend it. At all. shell is not a good language for processing text. It's a good language for wrapping and automating the execution of other programs. awk would be a decent alternative to perl, but you'd have to write your own join() function.
    – cas
    Sep 25, 2019 at 5:38
  • Thanks a lot for the solution in Perl. I only had to change to "$i <= $#row" and it worked. I would have to use awk/shell and re-write this due to special constraints at our end. I got something as below. It is numbering the columns in the input file based on the column number in the master file, but no re-arranging finally. And it does not account for the scenario of missing and new columns in the source file. Sep 25, 2019 at 18:39
  • <code> cat infile.out | head -1 | tr -s '|' '\n' | cat -n > infile_tmp.out rm -f final.out while read line; do touch final.out vChkClmn=echo $line | cut -d " " -f2 vMasterClm=grep -i $vChkClmn master_file.out | cut -d "|" -f2 echo "$vMasterClm" >> final.out done < infile_tmp.out rm -f final_master_list.out echo cat final.out | sed 's/[^ ]* */\$&/g' | sed 's/ /\,/g' > final_master_list.out vOrder=cat final_master_list.out echo "Value of order is $vOrder" awk -F\| '{print '$vOrder'}' OFS=\| infile.out </code> Sep 25, 2019 at 18:42
  • Input File: first_name|last_name|dob|strt_dt|clm_id Sam|Clark|03221993|05062010|a342d Andrew|John|10181997|08112017|a342d Sep 25, 2019 at 18:46
  • Master File: dob|1 clm_id|2 last_name|3 first_name|4 strt_dt|5 Sep 25, 2019 at 18:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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