Counting Lines in a Multi Gigabyte CSV Quickly and Correctly

How many rows are in this file? On a small file the question answers itself. On a file of a few gigabytes, it takes long enough to matter and has two answers that differ. A line count is cheap and exact: the number of newline bytes. A record count is what you want, and it needs a parser that knows which newlines are inside quotes. This article counts a 20,000,000 record CSV with nine tools, shows by how much the cheap answer is wrong and why, and ends with an estimate that costs 0.07 s.

Lines and records

A line ends with a newline byte (wc -l counts them; see the GNU coreutils manual). A record is a row of the table. The two are equal until a field contains a line break, which a quoted CSV field is allowed to do. The test file has such fields: about one note in 2,000 is two and lines separated by a newline. Counting everything:

bash run_0159.sh

wc -l (run 1)                      20009857 work/   1.35 s
wc -l (run 2)                      20009857 work/   0.83 s
wc -l (run 3)                      20009857 work/   0.85 s
grep -c '^'                        20009857         1.33 s
awk 'END {print NR}'               20009857         2.61 s
python, count b'\n' in 1 MiB blocks bytes 20009857   1.80 s
python mmap.find                   mmap 20009857    4.58 s
python csv.reader (records)        csv 20000001    14.74 s
qsv count (records)                20000000         2.20 s
duckdb count(*) (records)          20000000         0.99 s
last byte (tail -c 1)                \n             0.05 s

Every newline-counting tool (wc -l, grep -c '^', awk, the Python byte loop, mmap) reported 20,009,857. Every parser (qsv, DuckDB) reported 20,000,000, and Python's csv.reader reported 20,000,001, which includes the header. The gap:

echo "lines by wc -l minus the header: $(( $(wc -l < work/big/orders-20m.csv) - 1 ))"
echo "records by qsv:                  $(qsv count work/big/orders-20m.csv)"
echo "records by DuckDB:               $(duckdb -noheader -list -c "SELECT count(*) FROM read_csv('work/big/orders-20m.csv', all_varchar=true)")"
grep -c '^[^"]*,"two' work/big/orders-20m.csv | sed 's/^/rows whose note starts with the two-line text: /'

lines by wc -l minus the header: 20009856
records by qsv:                  20000000
records by DuckDB:               20000000
rows whose note starts with the two-line text: 9856

wc -l minus the header is 20,009,856. The true count is 20,000,000. The difference, 9,856, is the number of rows whose note contains a line break, which grep counted independently. So wc -l was too high by 0.05% here, and a script that uses it to check that a load kept every row will report 9,856 phantom rows, or will fail a check that should pass. The counting article shows the same effect at small scale.

How long each takes

Reading the table from the top:

The fastest line counter is wc -l, which is written to scan memory for one byte. grep and awk do more per line; the Python loop is limited by its block reads, and the mmap loop by a Python call for every newline (20 million of them). The record counters are the interesting ones: DuckDB took 0.99 s, about as long as a line count, because its CSV reader is multi-threaded and vectorized; qsv, a single-threaded Rust parser, took 2.20 s; and Python's csv.reader, which builds a list of strings for every row you do not use, took 14.74 s, fifteen times DuckDB. If you need the record count, DuckDB (see its CSV reader documentation) or qsv is the answer, and it costs about a second per gigabyte on this machine.

The first wc -l took 1.35 s and the second and third 0.83 and 0.85 s. The difference is the operating system's file cache: the first run read some of the file from the disk image and later runs read it from memory. With a cold cache, everything is bounded by disk speed (a file this size at 500 MB/s takes at least 2.6 s), and the ranking of tools matters less. Time your own case once cold.

The cheapest check of all

Before counting anything, one byte tells you whether the file is complete:

tail -c 1, the last line of the table above, printed \n in 0.05 s: the file ends with a newline, as a finished export should. A file whose last byte is not a newline was cut off or never finished, and a count of its lines is wrong by one before you start. The truncation article builds the full set of end-of-file checks on this.

An estimate in 0.07 s

Sometimes you only need the order of magnitude, to decide how to handle a file. File size divided by average row size gives that, and the quality of the answer depends on where you sample:

time python lab/big/estimate_rows.py work/big/orders-20m.csv

estimate from the first   1,000 lines:   21,526,280 rows (+7.63% against the true 20,000,000)
estimate from the first  10,000 lines:   21,194,910 rows (+5.97% against the true 20,000,000)
estimate from the first 100,000 lines:   20,823,903 rows (+4.12% against the true 20,000,000)
estimate from 20 random places x 500 lines:   20,052,856 rows (+0.26%)

real	0m0.062s
user	0m0.042s
sys	0m0.012s

Using the first 1,000, 10,000 and 100,000 lines gave 21,526,280, 21,194,910 and 20,823,903 rows: 7.6%, 6.0% and 4.1% too high. The start of the file is not representative: the first order ids are short (1, 2, 3), the row length grows as they gain digits, and the average row in the head is shorter than the average row overall. Sampling 500 lines from each of 20 random positions gave 20,052,856, 0.26% off, in 0.07 s for the whole script. The lesson applies to more than line counts: a sample from the beginning of a file is a sample of the beginning.

Compressed files

For a gzipped CSV, zcat file.gz | wc -l decompresses the whole file, and its speed is limited by decompression, not by counting. A pipeline hides a failed zcat unless pipefail is set, as in the gzip article. I did not time compressed counting here; gzip -t is the check that the stream is intact, and the record count still needs a parser.

How to tell it worked

Count records with two different tools and compare them. Check that lines minus records equals the number of rows with a line break, using a grep on the field you know has them. Compare the count with the source's own number, from a manifest or a SELECT count(*). Record the answer and the method, because "20,000,000" means something different if it came from wc -l.

What the file cache hides

All of these numbers are warm and from one run. They say how the tools compare and not what you will wait for. On a cold file, on a network file system, on an object store read over HTTP, the cost is the transfer, and a tool that can read only the part it needs (a footer, a range request, a statistics block) wins by a margin that none of these tests could show. That is an argument for formats with the count in the metadata. A Parquet file stores its row count in the footer (the Parquet article shows the metadata), and a manifest next to a CSV can do the same, as in the contract article. For a CSV on its own, the numbers above are what counting costs. The large-file hub puts counting in the context of the other first steps.