Interchange columns of an Excel file using perl

Question

I have a tab delimited Excel file with 65 columns and 350 rows. I need to rearrange it such that columns 60-65 are inserted after column1 , followed by col2, col3 ... col 59 using only PERL .. i.e.

Col1 Col60 Col61 Col62 Col2 Col3 Col4 Col5 Col6

I wrote a shell command to do it :

paste <(awk -F '\t' '{print $1}' part.xls ) <(awk -F '\t' '{print $60}' part.xls ) <(awk -F '\t' '{print $61}' part.xls ) <(awk -F '\t' '{print $62}' part.xls ) <(awk -F '\t' -v f=2 -v t=59 '{ for (i=f; i<=t;i++) printf("%s\t%s", $i,(i==t) ? "\n" : OFS) }' part.xls) > new.xls

part.xls = original file with 65 columns new.xls = file with reordered columns

I need to incorporate this into my PERL script . I tried using system command but it gave me errors.

system("paste <(awk -F '\t' '{print \$1}' part.xls ) <(awk -F '\t' '{print \$60}' part.xls ) <(awk -F '\t' '{print \$61}' part.xls ) <(awk -F '\t' '{print \$62}' part.xls ) <(awk -F '\t' -v f=2 -v t=59 '{ for (i=f; i<=t;i++) printf("%s\t%s", $i,(i==t) ? "\n" : OFS) }' part.xls) > new.xls");

Can someone plz point out my mistake ? Thanks in advance.

ooga ooga · Accepted Answer · 2014-07-16T05:20:19

Your awk code can be simplified:

awk -F"\t" '
  {
    printf("%s\t%s\t%s\t%s", $1, $60, $61, $62)
    for (i=2; i<=59; ++i) printf("\t%s", $i)
    print ""
  }
' part.xls > new.xls

But since you're already using perl (which is a kind of "super awk"!), you may as well just do something like this:

open(my $fin, "<", "part.xls") or die("Can't open part.xls: $!");
open(my $fout, ">", "new.xls") or die("Can't open new.xls: $!");
while (<$fin>) {
  my @f = split(/\t/);
  my $lineout = join("\t", $f[0], @f[59..61], @f[1..58]);
  print $fout $lineout, "\n";
}
close($fin);
close($fout);

Note that the field indices are zero-based in perl, whereas they are one-based in awk, so in perl they are all one less.

Interchange columns of an Excel file using perl

1 Answers