Add additional column to .csv using Perl.

Tech_20
Tech_20 used Ask the Experts™
on
I have a Perl script written to extract the 3rd column out of a beginning .csv file and then to place that content into a new .csv file. Now I want to take the 7th column from the original file (test.csv), add a column to new file and then input that 7th column data.

I have tested this using the splice command but only get the first column in output. Please refer to the attachment. Thanks.
textCSV.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ozo
Most Valuable Expert 2014
Top Expert 2015

Commented:
Can you give an example of an input file and the result you would want from it?

The first while loop goes until  $csv->getline ($data) is false, which means it is false at start of the next while loop so the second while loop is never entered.

If the second while loop had been entered, the splice would insert the field at position 7 into position 2, which would change
0, 1, 2, 3, 4, 5, 6, 7, 8, 9
into
0, 1, 7, 2, 3, 4, 5, 6, 7, 8, 9
it would then print the new position 7, which would be the 6 which was shifted down one position

Author

Commented:
I included a sample .csv. The first block of code (while loop) takes the 3rd column and creates a new .csv with that column in it. I want the 7th column to be in the 2nd column of the new .csv.

In other words, I want to go from this

252      989      charlesID#      ROOM#      SUITE#      ZIP CODE      BUILDING#
333      343      jamesID#      ROOM#      SUITE#      ZIP CODE      BUILDING#

to this....

charlesID# BUILDING#
jamesID# BUILDING#


I hope that clear things up. Thanks.
sample.csv
This should do what you want.  As ozo said, you can't use two separate loops.
#!/usr/bin/perl

use strict;
use warnings;
use feature 'say';
use Text::CSV;
use Text::CSV_XS;

my $file = $ARGV[0] or die "Need to get CSV file on the command line\n";

open my $data, "<", $file or die "Could not open '$file' $!\n";
open my $csv_out, ">", "new.csv" or die "new.csv: $!";

my $csv = Text::CSV->new ({
    binary => 1,
    auto_diag => 1,
});

while (my $fields = $csv->getline ($data)) {
    my $c1 = ($fields->[2] =~ m/.+/) ? $fields->[2] : '';
    my $c2 = ($fields->[7] =~ m/.+/) ? $fields->[7] : '';
    say {$csv_out} "$c1,$c2" if ($c1 =~ m/.+/ or $c2 =~ m/.+/);
}

close $data;
close $csv_out or die "new.csv: $!";

Open in new window

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

ozo
Most Valuable Expert 2014
Top Expert 2015
Commented:
$"=",";
while (my $fields = $csv->getline ($data)) {
    "@{$fields}[2,6]" ne $"  or next;
    say {$csv_out} "@{$fields}[2,6]";
}
Oops.  That should be $fields->[6] (not $fields->[7]) in my code (I copied that mistake from the original code).

Author

Commented:
Thanks. I tried the code (ID: 41371906, wilcoxon). I'm having trouble with the perl module Text::CSV on OS X. I get the following error after reinstalling the module several times. Please see attached file. Any advice?
cpan_error.txt
Did you install Text::CSV_XS as well as Text::CSV?  They are two separate modules.

Alternatively, you should be able to just remove the Text::CSV_XS line and it will still work (just with somewhat lower performance).

On the other hand, it looks like you aren't actually using Text::CSV_XS so you should either remove "use Text::CSV" or "use Text::CSV_XS" and, if you remove Text::CSV, you should switch line 14 to "my $csv = Text::CSV_XS->new".

Author

Commented:
Both wilcoxon and ozo had the "Best Solution" with their scripts but I could only choose one (which was entered first). Equal points for both scripts and some for the module advice. Thank you both so much!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial