insta.gator has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks. I have a comma delimited file. There are always values in fields 0-2 and always in 3 OR 4 but not both 3 AND 4. What I am trying to do is simply read each line and write it out as double-quoted comma delimited data, which I have accomplished. What I don't know how to do is not write out the empty field 3 or 4. Here is my code

open(INFILE1, "$ARGV[0]") or exit(101); open (OUTFILE1, ">" . "delimited_$ARGV[0]") or exit(201); $coid="$ARGV[1]"; if ("$coid" eq ""){$coid="***COID***";} while(<INFILE1>) { @file_line_contents = split (/\,/,$_); print OUTFILE1 "\"$coid\"\,\"".join ('","', @file_line_contents); }

For example, if I have this: AKP,45,MILITARY DIFF,,Exclude , I want to write out this: "***COID***","AKP","45","MILITARY DIFF","Exclude" (notice field 3 was not written out).

Is there a way to do this with a join command? If not, suggestions on how to best do it?

Thanks for any help.

Replies are listed 'Best First'.
Re: Converting a delimited file.
by Corion (Patriarch) on May 20, 2015 at 19:27 UTC

    Maybe you want to remove the empty fields before you output them?

    my @output= grep {/\S/} @file_line_contents; print OUTFILE1 "\"$coid\"\,\"".join ('","', @output);

    But for writing CSV, you really want to look at Text::CSV.

Re: Converting a delimited file.
by Laurent_R (Canon) on May 20, 2015 at 20:19 UTC
    Hi insta.gator

    we are using a lot of CSV format at our job. Most of the time, we can control both the generation of the CSV file in one application and its later use in another application. So we can use a very simple CSV format (ASCII characters, no quotes, no escape char, almost always the same separator, etc.), and we are happily using join and split to generate and decypher our CSVs. It can really make coding very simple. For example, just today, I wrote a line of code looking like this:

    my $err_code = (split /;/, $line)[8];
    I only needed that field 8 of every line to decide what to do with this line, it is really great to be able to do it in just one code line. The other programming language from which I was translating the code to Perl needed, if I remember right, 4 or 5 lines of code.

    But some of our partners sometimes send us more complicated CSV files, with quote and escape characters, and so on, then, really, using the Text::CSV module is handy and is really, IMHO, the right way to go.

Re: Converting a delimited file.
by hdb (Monsignor) on May 20, 2015 at 20:03 UTC

    From your description of your data I imply that there are two consecutive commas in each line, either after field 3 or before field 4. You could just s/,,/,/; on each line.

    Update: While you are at it you could also add quotes around the remaining commas and print the line:

    while(<INFILE1>) { chomp; s/,,/,/; s/,/","/g; print OUTFILE1 "\"$coid\",\"$_\"\n"; }
Re: Converting a delimited file.
by Tux (Canon) on May 21, 2015 at 06:09 UTC

    Use Text::CSV_XS' csv function with the on_in callback:

    $ cat test.pl use 5.20.0; use warnings; use Text::CSV_XS "csv"; csv (in => "test.csv", blank_is_undef => 1, on_in => sub { splice @{$_[1]}, 2, 2, $_[1][2] // $_[1][3]; }); $ cat test.csv 1,2,,,5,6 1,2,3,,5,6 1,2,,4,5,6 1,2,3,4,5,6 $ perl test.pl 1,2,,5,6 1,2,3,5,6 1,2,4,5,6 1,2,3,5,6

    update: That also surfaced a small buglet in the code, as I wanted to show that that all fields on output could be forced quoted in the same call, but somewhere that option currently gets lost. The workaround however is easy:

    $ cat test.pl use 5.20.0; use warnings; use Text::CSV_XS "csv"; csv (quote_always => 1, in => csv (in => "test.csv", blank_is_undef => + 1, on_in => sub { splice @{$_[1]}, 2, 2, $_[1][2] // $_[1][3]; })); $ perl test.pl "1","2",,"5","6" "1","2","3","5","6" "1","2","4","5","6" "1","2","3","5","6"

    Working on a fix for 1.18 ...

    update 2: fixed. I'll release after going through the long test procedure.

    $ perl -MText::CSV_XS=csv -e'csv (in => "test.csv", blank_is_undef => +1, quote_always => 1, on_in => sub { splice @{$_[1]}, 2, 2, $_[1][2] +// $_[1][3]; });' "1","2",,"5","6" "1","2","3","5","6" "1","2","4","5","6" "1","2","3","5","6"

    Enjoy, Have FUN! H.Merijn
Re: Converting a delimited file.
by boftx (Deacon) on May 21, 2015 at 09:55 UTC

    This might be gross, but here is the brute force approach that I see:

    while(<INFILE1>) { @file_line_contents = split (/\,/,$_); # this assumes that $file_line_contents[3] never contains the valu +e '0' $file_line_contents[3] ||= $file_line_contents[4]; # now get rid of the unneeded element (assumes this is the last el +ement in the array) pop @file_line_contents; print OUTFILE1 "\"$coid\"\,\"".join ('","', @file_line_contents); }
    It should be simple to adapt if the assumptions being made are not applicable.

    You must always remember that the primary goal is to drain the swamp even when you are hip-deep in alligators.