Checking Row and Column Counts of a CSV Before Loading

"How many rows are in this file?" sounds like a question with one answer. It has at least six. wc -l counts newline characters, awk counts lines, a CSV parser counts records, pandas counts what it manages to parse, and DuckDB counts whatever its sniffer decided the file was. They agree on tidy files and disagree on exactly the files you need to check. This article takes one small, deliberately awkward file and shows every answer, then the same checks on a 193,709 line file, and settles on a pair of checks that I run before every load: the number of records and a histogram of fields per record. They are the cheapest validation there is, and the first layer of the validation guide.

One small file, built to be awkward

Here is the file, printed with cat -A so that line endings show as $ and a missing one shows as nothing:

cat -A lab/data/val/counts.csv

id,name,note,amount$
1,Ana,"line one$
line two",10.00$
2,Bo,plain,5.00$
3,Cy,"has, comma",7.50$
4,Di,short$
5,Ed,extra,1.00,oops$
$
6,Fay,last,2.00

It contains the things that break counting, one each:

• record 1 has a quoted newline inside note, so it takes two physical lines

• record 3 has a quoted comma

• record 4 (4,Di,short) has three fields instead of four

• record 5 (5,Ed,extra,1.00,oops) has five

• a blank line sits between records 5 and 6

• the last line, 6,Fay,last,2.00, has no line terminator

RFC 4180 permits the quoted newline and the quoted comma, and also allows the last record to have no line break. The short row, the long row and the blank line are what real exports produce when something upstream went wrong.

What the line counters say

The two shell tools count lines, and they count different things:

wc -l lab/data/val/counts.csv
awk 'END {print NR " lines by awk"}' lab/data/val/counts.csv
awk -F, '{print NF}' lab/data/val/counts.csv | sort -n | uniq -c

8 lab/data/val/counts.csv
9 lines by awk
      1 0
      1 2
      2 3
      3 4
      2 5

wc -l printed 8. It counts newline characters, and the file has 8 of them, because the final line has none. awk printed 9, since it counts every line it reads, terminated or not. Neither is the number of records, and neither is off by exactly one in a way you could correct for: the quoted newline adds a line, the blank line adds one, and the missing terminator removes one from wc. A tidy file hides this because the errors cancel or vanish. wc -l minus one for the header is the usual way to say "how many rows" and it is right for files that have no quoted newlines, no blank lines and a final newline, which is most of them, until one is not.

The awk -F, field histogram is worse. It reported widths of 0, 2, 3, 3, 4, 4, 4, 5 and 5. The row 3,Cy,"has, comma",7.50 came out as 5 fields because awk splits on every comma, quoted or not, and the first record was split in two lines of 3 and 2 fields. A histogram of fields per line is a histogram of something nobody cares about. It is useful on a file you know has no quoted commas or newlines, and the customer file below happens to be one.

What the CSV parser says

The Python csv module understands quoting, so its answer is the one that matches the format. Open the file with newline="", as the module documentation requires, so that the reader sees the line endings inside quoted fields as written:

import csv, collections, io, sys
import pandas as pd

path = sys.argv[1]
raw = open(path, "rb").read()
print("bytes", len(raw), "newline characters", raw.count(b"\n"), "ends with newline", raw.endswith(b"\n"))
with open(path, newline="", encoding="utf-8") as f:
    rows = list(csv.reader(f))
print("csv records:", len(rows), "including the header")
print("empty records:", sum(1 for r in rows if r == []))
hist = collections.Counter(len(r) for r in rows if r)
print("fields per record:", dict(sorted(hist.items())))
for i, r in enumerate(rows, 1):
    if r and len(r) != len(rows[0]):
        print(f"  record {i} has {len(r)} fields: {r}")
try:
    df = pd.read_csv(path, dtype=str, keep_default_na=False)
    print("pandas rows:", len(df))
except Exception as e:
    print("pandas:", type(e).__name__, str(e).splitlines()[0])

python lab/val/t0123.py lab/data/val/counts.csv

bytes 139 newline characters 8 ends with newline False
csv records: 8 including the header
empty records: 1
fields per record: {3: 1, 4: 5, 5: 1}
  record 5 has 3 fields: ['4', 'Di', 'short']
  record 6 has 5 fields: ['5', 'Ed', 'extra', '1.00', 'oops']
pandas: ParserError Error tokenizing data. C error: Expected 4 fields in line 6, saw 5

The file has 139 bytes and 8 newline characters, and does not end with a newline. The reader found 8 records including the header. One of them is an empty record, which csv.reader returns as [] for a blank line. That leaves 6 data records. The histogram is {3: 1, 4: 5, 5: 1}: five records of width 4 (the header, three good rows and the last row), one of width 3 and one of width 5, and the two ragged ones are named by record number. The record numbers are not the same as editor line numbers, because the first data record takes two lines. Record 6, the oops row, is on editor line 7, a difference you should tell anybody you report to.

Pandas gave a different kind of answer. pd.read_csv raised ParserError: Error tokenizing data. C error: Expected 4 fields in line 6, saw 5. Two things are visible in that message. It stops at the first row with too many fields; it did not complain about the short row, because pandas pads missing fields with empty values and fails only on extras. And the "line 6" it reports is the sixth record, not the sixth editor line. Which is a point for the habit of printing both the record number and the offending content, as the csv script does, so nobody has to count.

What DuckDB says

DuckDB's reader sniffs the file's dialect before it reads, and on a file this irregular the sniffer's choice becomes part of the answer. Four queries on the same file:

duckdb -box -c "SELECT count(*) AS rows FROM read_csv('lab/data/val/counts.csv', header=true, all_varchar=true, ignore_errors=true)"
duckdb -box -c "SELECT Delimiter, Quote, Columns FROM sniff_csv('lab/data/val/counts.csv')"
duckdb -box -c "SELECT count(*) AS rows FROM read_csv('lab/data/val/counts.csv')"
duckdb -box -c "SELECT * FROM read_csv('lab/data/val/counts.csv', delim=',', quote='\"', escape='\"', header=true, all_varchar=true, null_padding=true, parallel=false, ignore_errors=true)"
duckdb -c "SELECT count(*) FROM read_csv('lab/data/val/counts.csv', delim=',', quote='\"', escape='\"', header=true, all_varchar=true, parallel=false)" 2>&1 | head -n 3

┌──────┐
│ rows │
├──────┤
│ 4    │
└──────┘
┌───────────┬─────────┬────────────────────────────────────────────────────┐
│ Delimiter │  Quote  │                      Columns                       │
├───────────┼─────────┼────────────────────────────────────────────────────┤
│ |         │ (empty) │ [{'name': 'id,name,note,amount', 'type': VARCHAR}] │
└───────────┴─────────┴────────────────────────────────────────────────────┘
┌──────┐
│ rows │
├──────┤
│ 8    │
└──────┘
┌────┬──────┬────────────────────┬────────┬─────────┐
│ id │ name │        note        │ amount │ column4 │
├────┼──────┼────────────────────┼────────┼─────────┤
│ 1  │ Ana  │ line one\nline two │ 10.00  │ NULL    │
│ 2  │ Bo   │ plain              │ 5.00   │ NULL    │
│ 3  │ Cy   │ has, comma         │ 7.50   │ NULL    │
│ 4  │ Di   │ short              │ NULL   │ NULL    │
│ 5  │ Ed   │ extra              │ 1.00   │ oops    │
│ 6  │ Fay  │ last               │ 2.00   │ NULL    │
└────┴──────┴────────────────────┴────────┴─────────┘
Invalid Input Error: Error when sniffing file "lab/data/val/counts.csv".
It was not possible to automatically detect the CSV parsing dialect
The search space used was:

• With ignore_errors=true it returned 4 rows. Four rows are the good ones; the ragged rows and the blank line were skipped without any message, the same behavior the validation guide warns about.

• With the defaults it returned 8 rows, and the sniffer explains why: it chose | as the delimiter and no quote character, so the whole file is one column named id,name,note,amount, and the 8 rows are the physical lines after the header. It is a count of lines presented as a count of rows.

• With the delimiter, quote and escape set, header=true, null_padding=true, parallel=false and ignore_errors=true, it returned 6 rows: the four good rows, the short row padded with nulls and the five-field row, whose extra value landed in a column named column4. The blank line is dropped.

• The same dialect settings without null_padding and ignore_errors fail with Error when sniffing file, and the message lists the search space it tried.

The lesson is not that DuckDB is wrong. On a normal file it is fast and correct, and the options article shows how to set a dialect explicitly. The lesson is that a count from a reader that tolerates errors depends on its tolerance settings. That count is only a validation if you know exactly what it skipped.

The answers side by side

Six is the answer I would give if asked: six records have content, and two of them are the wrong width. A report that says "6 rows, 2 with the wrong number of fields (records 5 and 6), 1 blank line" is useful. A report that says "7 rows" is not.

The same checks at scale

The faulty customer file has 193,709 lines. The checks that matter, with their time:

f=work/val/customers.csv
time wc -l $f
time python -c "import csv; print(sum(1 for _ in csv.reader(open('$f', newline='')))-1, 'records')"
time duckdb -noheader -list -c "SELECT count(*) FROM read_csv('$f', header=true, all_varchar=true)"
time qsv count $f

193709 work/val/customers.csv

real	0m0.045s
user	0m0.003s
sys	0m0.005s
193708 records

real	0m0.562s
user	0m0.259s
sys	0m0.020s
193708

real	0m0.112s
user	0m0.108s
sys	0m0.023s
193708

real	0m0.046s
user	0m0.029s
sys	0m0.010s

wc -l printed 193,709 in 0.026 s. That is the number of data records plus one for the header, because this file has no quoted newlines. The csv module counted 193,708 records in 0.558 s. DuckDB took 0.126 s and qsv count 0.070 s, both reporting 193,708 and neither counting the header. All four agree once you remember which includes the header. For a file of this size the slowest of them is still half a second, so there is no reason to use the least trustworthy. For a file of tens of gigabytes, qsv count and DuckDB read in parallel, and wc -l remains the quick first look.

For the widths:

awk -F, '{print NF}' work/val/customers-bad.csv | sort -n | uniq -c
awk -F, 'NF != 12 {print NR ": " NF " fields"}' work/val/customers-bad.csv
qsv count work/val/customers-bad.csv 2>&1 | tail -n 2
qsv validate work/val/customers-bad.csv 2>&1 | head -n 6

      1 10
 193707 12
      1 13
502: 13 fields
602: 10 fields
csv error: CSV error: record 501 (line: 502, byte: 42202): found record with 13 fields, but the previous record has 12 fields
Validation error: CSV error: record 501 (line: 502, byte: 42202): found record with 13 fields, but the previous record has 12 fields.
Use `qsv fixlengths` to fix record length issues.

The histogram from awk was 193,707 records of 12 fields, one of 13 and one of 10, and the awk filter put them at lines 502 and 602. It matched what qsv validate reported: found record with 13 fields, but the previous record has 12 fields, at record 501 and line 502. And awk is trustworthy here only because the customer file has no quoted commas or newlines, something I knew from the file's structure and not from awk.

What to compare the count with

A count of 193,708 is information only if there is something to compare it to. In order of usefulness:

1. The sender's count, from a manifest or a trailer line. This is the best check, since it comes from the system that produced the data. See the contract article.

2. The source system. A SELECT count(*) on the table that was exported, run at the same moment as the export.

3. The previous delivery. Not equal, but of the same order. A file that is 3% of last week's size is a failed export.

4. The width. The number of columns should match the header in every record. This one needs no outside reference, which is why it comes first.

How to tell it worked

Run the checks on a file you know is right and confirm that the record count, the histogram and the sender's number agree. Run them on the awkward file and confirm that you can name every discrepancy, which is the real test: if you can explain 7, 8, 7, an error, 4 and 8 for one file, you know what your own tool is counting. Then add one more row to a good file by hand, rerun, and confirm the count moves by exactly one.

What a matching count does not prove

A row count only says the number of records is the same. A file truncated mid-record has the right number of records, as the truncated-file article shows. A file with every value shifted one column to the right has the right count and the right widths. A file with the same rows in a different order, or with 200 rows duplicated and 200 rows missing, also matches. Counts are the first filter and the cheapest. They keep the wrong files out, and the checks on values, keys and checksums do the rest.