How do I count the number of rows in a large CSV file with Perl?

Question

I have to use Perl on a Windows environment at work, and I need to be able to find out the number of rows that a large csv file contains (about 1.4Gb). Any idea how to do this with minimum waste of resources?

Thanks

PS This must be done within the Perl script and we're not allowed to install any new modules onto the system.

Answer 1

Yes, don't use perl.

Instead use the simple utility for counting lines; wc.exe

It's part of a suite of utilities ported from unix originals.

http://unxutils.sourceforge.net/

For example;

PS D:\> wc test.pl
     12      26     271 test.pl
PS D:\>

Where 12 == number of lines, 26 == number of words, 271 == number of characters.

If you really have to use perl;

D:\>perl -lne "END{print $.;}" < test.pl
12

Answer 2

Do you mean lines or rows? A cell may contain line breaks which would add lines to the file, but not rows. If you are guaranteed that no cells contain new lines, then just use the technique in the Perl FAQ. Otherwise, you will need a proper CSV parser like Text::xSV.

Answer 3

perl -lne "END { print $. }" myfile.csv

This only reads one line at a time, so it doesn't waste any memory unless each line is enormously long.

Answer 4

Upvote for edg's answer, another option is to install cygwin to get wc and a bunch of other handy utilities on Windows.

Answer 5

This one-liner handles new lines within the rows:

1. Considering lines with an odd number of quotes.
2. Considering that doubled quotes is a way of indicating quotes within the field.

3. It uses the awesome flip-flop operator.

   perl -ne 'BEGIN{$re=qr/^[^"]*(?:"[^"]*"[^"]*)*?"[^"]*$/;}END{print"Count: $t\n";}$t++ unless /$re/../$re/'
   

Consider:

• wc is not going to work. It's awesome for counting lines, but not CSV rows
• You should install--or fight to install--Text::CSV or some similar standard package for proper handling.
• This may get you there, nonetheless.

---

EDIT: It slipped my mind that this was windows:

perl -ne "BEGIN{$re=qr/^[^\"]*(?:\"[^\"]*\"[^\"]*)*?\"[^\"]*$/;}END{print qq/Count: $t\n/;};$t++ unless $pq and $pq = /$re/../$re/;"

The weird thing is that The Broken OS' shell interprets && as the OS conditional exec and I couldn't do anything to change its mind!! If I escaped it, it would just pass it that way to perl.

Answer 6

I was being idiotic, the simple way to do it in the script is:

open $extract, "<${extractFileName}" or die ("Cannot read row count of $extractFileName");
$rowCount=0; 
while (<$extract>)
{
 $rowCount=$rowCount+1;
}

close($extract);