in reply to Extracting fields from CSV

Thanks all for your valuable suggestions and feedbacks.

Now, here is another requirement, i have the file of format as below,(from a sql query output)

    BED001  |137CM BUDGET BLUE B/SET  |BED001  |BASE & MATTRESS  |  |E
+a  |0  |0  |39.47  |0.00
    BED0015  |183CM BODY CUSHION BASE SET  |BED0015  |BASE & MATTRESS 
+ |  |Ea  |0  |0  |1,099.91  |0.00

[download]


The expected output is

BED001,137CM BUDGET BLUE B/SET,BED001,BASE & MATTRESS,,Ea,0,0,39.47,0.
+00
BED0015,183CM BODY CUSHION BASE SET,BED0015,BASE & MATTRESS,,Ea,0,0,"1
+,099.91",0.00

[download]


The code i tried is

use strict;
use Text::CSV;

my $csv     = Text::CSV->new( { sep_char => "|", allow_whitespace => 1
+ } );
my $infile  = "s.txt";
my $outfile = "s.csv";

open( my $fh,  "<", $infile );
open( my $out, ">", $outfile );
while ( my $array = $csv->getline($fh) ) {
    local $" = ",";
    local $\ = "\n";

    #$csv->combine(@{$array});
    #my $line=$csv->string();
    print $out "@{$array}";

    #print $out $line;
}

[download]
The actual output:

BED001,137CM BUDGET BLUE B/SET,BED001,BASE & MATTRESS,,Ea,0,0,39.47,0.
+00
BED0015,183CM BODY CUSHION BASE SET,BED0015,BASE & MATTRESS,,Ea,0,0,1,
+099.91,0.00

[download]

I need to quote 1,099.91 in the second line of the output csv since the string contains sep_char ",".Is there any method to define output sep_char ?? or How to achieve the expected result??

Replies are listed 'Best First'.
Re^2: Extracting fields from CSV
by Tux (Canon) on Aug 31, 2010 at 06:36 UTC

    If you are able to connect to the database from perl, using DBI, you don't need an intermediate file at all.

    Use Text::CSV_XS (or Text::CSV) directly to generate the output and it will correctly deal with the quoting:

    use strict;
use Text::CSV_XS;

my $csv_in  = Text::CSV_XS->new ({ binary   => 1,   auto_diag => 1,
                                   sep_char => "|", allow_whitespace =
+> 1 });
my $csv_out = Text::CSV_XS->new ({ binary   => 1,   eol => "\n" });
my $infile  = "s.txt";
my $outfile = "s.csv";

open my $fh,  "<", $infile  or die "$infile: $!";
open my $out, ">", $outfile or die "$outfile: $!";
while (my $row = $csv_in->getline ($fh)) {
    $csv_out->print ($out, $row);
    }

    [download]

    update: corrected $fh to $out in while loop.


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
      Thanks. This is what i really wanted :)

      There is a small typo in the above code

      $csv_out->print ($fh, $row);
      Filehandle $out is needed in place of $fh for writing

      $csv_out->print ($out, $row);

      ~suhail
[reply]
[d/l]
[select]
Re^2: Extracting fields from CSV
by aquarium (Curate) on Aug 31, 2010 at 04:55 UTC
    unless you really need the programmatic interface for the sql export, it's typically much better to use the database export utilities provided by the vendor, e.g. mysqldump and similar. these utilities have a tonne of switches to tweak the output as you require, without re-inventing the wheel. then you can concentrate on any actual program logic that does something more useful than import/export. but that's just my take on the situation.
    the hardest line to type correctly is: stty erase ^H
[reply]

In Section Seekers of Perl Wisdom