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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.