Re^3: Need help in reading csv files >1GB for converting into xlsx files in Solaris 10

Hi ... this is the code i have used to

 
#use 5.18.2;
use warnings;
use Benchmark qw( cmpthese );
use Text::CSV_PP;
use Text::CSV_XS;

my ($fh, $csv);
sub fh  { open $fh, "<", "Proj20101111.csv"; }
sub cxs { $csv = Text::CSV_XS->new ({ binary => 1, sep_char => "|" }) 
+}
sub cpp { $csv = Text::CSV_PP->new ({ binary => 1, sep_char => "|" }) 
+}

cmpthese (4, {
    perl => sub { fh;      while (my @row = split /\|/ => <$fh>) {} },
    c_xs => sub { fh; cxs; while (my $row = $csv->getline ($fh)) {} },
    c_pp => sub { fh; cpp; while (my $row = $csv->getline ($fh)) {} },
    cbxs => sub { fh; cxs; $csv->bind_columns (\my($a,$b,$c,$d,$e,$f))
+;
                           while (          $csv->getline ($fh)) {} },
    });
    
    close($fh);
[download]

and my sample data would be like

those datas would be in GB size csv files .... :( help ! thanks !

Comment on Re^3: Need help in reading csv files >1GB for converting into xlsx files in Solaris 10 - Perl v-5.8.4 Download Code

Replies are listed 'Best First'.
Re^4: Need help in reading csv files >1GB for converting into xlsx files in Solaris 10 - Perl v-5.8.4 by Tux (Canon) on Feb 12, 2015 at 14:25 UTC
unless that data example can be public, please remove it again: it has e-mail addresses. If it is world-viewable, please add readmore-tags. Now back to the testing example. Explaining the extreme diff in cbxs is easy: you bind for just 6 columns, but you feed it way more (132), so the geline will return false. Having created a 30001 line version of your data example, and binding with the correct number of columns, I get `Rate c_pp c_xs cbxs perl c_pp 3.03e-02/s -- -97% -97% -98% c_xs 1.07/s 3434% -- -1% -34% cbxs 1.08/s 3453% 1% -- -33% perl 1.61/s 5229% 51% 50% --` [download] which is what I would expect. Now that I have seen your data, note that it actually resembles CSV: it only uses a pipe instead of a comma, but the fields between the pipes are quoted, so making a correct parser with split will (eventually) fail `""\|201407\|"Jul\|2014"\|" "\|"BLANK"\|" "\|" "\|" "\|"CAN01"\|" "\|" "\|" "\|" "\|" +09/09/9999 00:00:00"... ^` [download] See where I manually inserted a pipe? And what if this data allows embedded newlines between the quotes? As you are inserting the data between the pipes into XLS, your data will contain the quotes if you just split on pipes. I don't think that is what you want. These are the reasons why you need a CSV parser and not split. To parse this data quick and reliable, you could go with something like this: `$ cat test.pl use strict; use warnings; use Text::CSV_XS; my $csv = Text::CSV_XS->new ({ binary => 1, sep_char => "\|", auto_diag + => 1 }); open my $fh, "<", "Proj20101111.csv" or die $!; my @hdr = map { lc } @{$csv->getline ($fh)}; my %rec; $csv->bind_columns (\@rec{@hdr}); my %count; while ($csv->getline ($fh)) { $count{$rec{client_id}}++; } printf "%-8s %7d\n", $_, $count{$_} for sort keys %count; $ perl test.pl 104167 1001 116571 4004 BLANK 24024` [download] HTH Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^4: Need help in reading csv files >1GB for converting into xlsx files in Solaris 10 - Perl v-5.8.4
by Tux (Canon) on Feb 12, 2015 at 14:25 UTC

unless that data example can be public, please remove it again: it has e-mail addresses. If it is world-viewable, please add readmore-tags.

Now back to the testing example. Explaining the extreme diff in cbxs is easy: you bind for just 6 columns, but you feed it way more (132), so the geline will return false.

Having created a 30001 line version of your data example, and binding with the correct number of columns, I get

           Rate  c_pp  c_xs  cbxs  perl
c_pp 3.03e-02/s    --  -97%  -97%  -98%
c_xs     1.07/s 3434%    --   -1%  -34%
cbxs     1.08/s 3453%    1%    --  -33%
perl     1.61/s 5229%   51%   50%    --
[download]

which is what I would expect. Now that I have seen your data, note that it actually resembles CSV: it only uses a pipe instead of a comma, but the fields between the pipes *are* quoted, so making a correct parser with split will (eventually) fail

""|201407|"Jul|2014"|" "|"BLANK"|" "|" "|" "|"CAN01"|" "|" "|" "|" "|"
+09/09/9999 00:00:00"...
              ^
[download]

See where I manually inserted a pipe? And what if this data allows embedded newlines between the quotes?

As you are inserting the data between the pipes into XLS, your data will contain the quotes if you just split on pipes. I don't think that is what you want.

These are the reasons why you need a CSV parser and not split.

To parse this data quick and reliable, you could go with something like this:

$ cat test.pl
use strict;
use warnings;

use Text::CSV_XS;

my $csv = Text::CSV_XS->new ({ binary => 1, sep_char => "|", auto_diag
+ => 1 });

open my $fh, "<", "Proj20101111.csv" or die $!;

my @hdr = map { lc } @{$csv->getline ($fh)};
my %rec;
$csv->bind_columns (\@rec{@hdr});
my %count;
while ($csv->getline ($fh)) {
    $count{$rec{client_id}}++;
    }
printf "%-8s %7d\n", $_, $count{$_} for sort keys %count;
$ perl test.pl
104167      1001
116571      4004
BLANK      24024
[download]

HTH

Enjoy, Have FUN! H.Merijn

[reply]
[d/l]
[select]