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 · Accepted Answer · 2014-07-16 05:20:19Z

0

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.

answered Jul 16, 2014 at 5:20

ooga

15.6k2 gold badges23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

enigma Over a year ago

Thank you so much for the correction .. Yup it makes the awk command far better than the original one !! N d PERL code works gr8 too !!

Collectives™ on Stack Overflow

Interchange columns of an Excel file using perl

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related