xuo has asked for the wisdom of the Perl Monks concerning the following question:

Hello Perl's Monks,

I've got the following issue :

I've got a csv file (where fields are separated with , (commas)) and that can have , (commas) inside the fields. This is the reason why I've used the Text::CSV module.

My goal is to remove some unused fields (columns) and to swap some of them.

Input file :

column1,column2,column3,column4,column5,column6
datar1c1,datar1c2,datar1c3,datar1c4,datar1c5,datar1c6
datar2c1,"data,r2c2",datar2c3,datar2c4,datar2c5,datar2c6
datar3c1,datar3c2,"data, r3c3",datar3c4,datar3c5,datar3c6

[download]

Expected output file :

column2,column1,column4,column3
datar1c2,datar1c1,datar1c4,datar1c3
datar2c2,datar2c1,datar2c4,datar2c3
datar3c2,datar3c1,datar3c4,"data, r3c3"

[download]

In this example, I want to remove the 2 last columns and swap columns 1 and 2 and columns 3 and 4.

The following code removes the columns 5 and 6 but I don't know how to swap the columns.

#!/usr/bin/perl

use strict ;
use warnings ;

use Text::CSV ;
use Getopt::Long qw(GetOptions) ; 

my $inputFile = '' ;
my $outputFile = '' ;
my $csv = Text::CSV->new ();

GetOptions(    'file:s' => \$inputFile,
        'out:s' => \$outputFile,
      ) or die("Error in command line arguments\n") ;

open(my $data, '<:encoding(utf8)', $inputFile) or die "Could not open 
+'$inputFile' \n" ;
open(my $out, '>:encoding(utf8)', $outputFile) or die "Could not open 
+'$outputFile' \n" ;

while (my $fields = $csv->getline( $data ))
{
    splice @$fields, 4, 2 ;
    $csv->print($out, $fields) ;
    print $out "\n" ;
}

close $data ;
close $out ;

[download]

Could you help me ?

Regards.

Xuo.

Replies are listed 'Best First'.
Re: How to swap columns in a csv file ?
by Tux (Canon) on Jan 05, 2025 at 14:23 UTC

    One way. There are many

    $ cat test.pl
#!/pro/bin/perl

use 5.012003;
use warnings;

use Text::CSV_XS qw( csv );

my $aoh = csv (in => *DATA, kh => \my @h);
say "Head was (@h)";

# Construct new header (out of old header)
my @nh = qw( column2 column1 column4 column3 );
csv (in => $aoh, headers => \@nh);

__END__
column1,column2,column3,column4,column5,column6
datar1c1,datar1c2,datar1c3,datar1c4,datar1c5,datar1c6
datar2c1,"data,r2c2",datar2c3,datar2c4,datar2c5,datar2c6
datar3c1,datar3c2,"data, r3c3",datar3c4,datar3c5,datar3c6

    [download]
    $ perl test.pl
Head was (column1 column2 column3 column4 column5 column6)
column2,column1,column4,column3
datar1c2,datar1c1,datar1c4,datar1c3
"data,r2c2",datar2c1,datar2c4,datar2c3
datar3c2,datar3c1,datar3c4,"data, r3c3"

    [download]

    update Fixed the output in above post. I mis-pasted


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

      If you're ok with dealing with header indexes instead of header names, the above the be reduced to a single command: 

      csv
   in => \*ARGV,
   out => \*STDOUT,
   after_parse => sub { $_[1]->@* = $_[1]->@[ 1, 0, 3, 2 ] };

      [download]

      The whole thing as a one-liner: 

      perl -MText::CSV_XS=csv -e'csv in => \*ARGV, out => \*STDOUT, after_pa
+rse => sub { $_[1]->@* = $_[1]->@[1,0,3,2] }' a.csv

      [download]
[reply]
[d/l]
[select]

      Hello,

      It works very well. Thank you very much for your help.

      Next steps are :
      1) to be able to manage comments in the csv file that can be split on several lines. I do it in a way I don't like using sed.
      2) to be able to sort alphabetically 2 columns
      I'll try first on my own before I ask your help again :)

      Regards

      Xuo
[reply]

        "to be able to sort alphabetically 2 columns"

        Hi, it's not completely clear what you want. Maybe it's as simple as the following, which sorts by column 1 and if there multiple instances of a value, then by column 3. See sort. 

        use strict;
use warnings;
use Text::CSV_XS 'csv';

my $aoa = csv( in => *DATA );
my @headers = shift @$aoa;

my @sorted = sort { $a->[0] cmp $b->[0] || $a->[2] cmp $b->[2] } @$aoa
+;

csv( in => \@sorted, headers => @headers );

__DATA__
column1,column2,column3
b,foo,v
a,bar,y
c,baz,w
b,qux,z
b,nif,x

        [download]

        Output: 

        column1,column2,column3
a,bar,y
b,foo,v
b,nif,x
b,qux,z
c,baz,w

        [download]

        Hope this helps.

        Update: added link to doc


        The way forward always starts with a minimal test.
[reply]
[d/l]
[select]

        Hi,

        Step 1 is managed by default with the code from Tux. I've removed all my dirty code.

        Xuo.
[reply]

Back to Seekers of Perl Wisdom