Re^4: Need help in reading csv files >1GB for converting into xlsx files in Solaris 10 - Perl v-5.8.4

[Tux]

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%    --

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"...
              ^

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

HTH