How to count rows in a csv file

Count four numbers, not one. On the export below, the file held 158 bytes, wc -l returned 8, an RFC 4180 parser found 7 records and 6 data rows, and the exporter reported 7. Run node csvcount.mjs four orders.csv 7. The number a tester needs is the data rows.

Why check this

A row count is the cheapest assertion in an export test and the one most often written against the wrong number. wc -l counts line terminators. A CSV record can span several of them, can end without one, and the first record is usually a header.

Run this on the first export of a release, and after any change to the writer.

The failure it prevents is a short export that reports success. Step 1 says seven rows and writes six. Step 2 holds the warning nobody reads. Step 12 loses two more on the way into a store. Nothing in that chain raises an error.

Prerequisites

• Node 22 for the four programs below. node:sqlite warns without --no-warnings.
• GNU wc 8.32, xxd and file 5.44, here from Git Bash on Windows 11.
• A scratch directory. export.mjs writes every fixture.
• Where a record ends is decided by How to escape quotes in csv.
• These counts test against RFC 4180 section 2.

csvlib.mjs is the parser.

export const LF = String.fromCharCode(10);
export const CR = String.fromCharCode(13);
export const Q = String.fromCharCode(34);

// RFC 4180 reader. Returns every record the bytes contain, including a record
// made only of an empty field, because that record is the thing being counted.
export function parseCsv(text) {
  const rows = [];
  let row = [];
  let field = '';
  let quoted = false;
  let i = 0;
  while (i < text.length) {
    const c = text[i];
    if (quoted) {
      if (c === Q && text[i + 1] === Q) { field += Q; i += 2; continue; }
      if (c === Q) { quoted = false; i += 1; continue; }
      field += c; i += 1; continue;
    }
    if (c === Q && field === '') { quoted = true; i += 1; continue; }
    if (c === ',') { row.push(field); field = ''; i += 1; continue; }
    if (c === CR && text[i + 1] === LF) { row.push(field); rows.push(row); row = []; field = ''; i += 2; continue; }
    if (c === LF || c === CR) { row.push(field); rows.push(row); row = []; field = ''; i += 1; continue; }
    field += c; i += 1;
  }
  if (field !== '' || row.length > 0) { row.push(field); rows.push(row); }
  return rows;
}

export const countChar = (text, ch) => text.split(ch).length - 1;

export.mjs writes the export and five fixtures.

import { writeFileSync, readFileSync } from 'node:fs';
import { DatabaseSync } from 'node:sqlite';
import { LF, CR, Q } from './csvlib.mjs';

const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, note TEXT, amount TEXT)');
const ins = db.prepare('INSERT INTO orders VALUES (?, ?, ?, ?)');
ins.run(1, 'Ada', 'ok', '10.00');
ins.run(2, 'Grace', 'line one' + LF + 'line two', '20.00');
ins.run(3, 'Linus', 'ok', '30.00');
ins.run(4, null, 'ok', '40.00');
ins.run(5, 'Barbara', 'note a' + LF + 'note b', '50.00');
ins.run(6, 'Alan', 'ok', '60.00');
ins.run(7, 'Edsger', 'plain', '70.00');

const quote = (v) => (v.includes(',') || v.includes(Q) || v.includes(LF) ? Q + v.split(Q).join(Q + Q) + Q : v);

// The defect: the counter reports the size of the result set, not the number
// of records written, and the last record gets no terminator.
const rows = db.prepare('SELECT * FROM orders ORDER BY id').all();
const out = ['id,customer,note,amount'];
for (const r of rows) {
  if (r.customer === null) { console.error('warn: row ' + r.id + ' has no customer, skipped'); continue; }
  out.push([r.id, r.customer, r.note, r.amount].map((v) => quote(String(v))).join(','));
}
writeFileSync('orders.csv', out.join(LF));
console.log('exported ' + rows.length + ' rows to orders.csv');

// Four fixtures that differ in one property each, and one file that is two
// daily exports joined end to end.
const H = 'id,customer,note,amount';
const A = '1,Ada,ok,10.00';
const B = '2,Grace,ok,20.00';
const C = '3,Linus,ok,30.00';
const MULTI = '2,Grace,' + Q + 'line one' + LF + 'line two' + Q + ',20.00';
writeFileSync('plain.csv', [H, A, B, C].join(LF) + LF);
writeFileSync('quoted.csv', [H, MULTI, C].join(LF) + LF);
writeFileSync('nonl.csv', [H, A, B, C].join(LF));
writeFileSync('crlf.csv', [H, A, B, C].join(CR + LF) + CR + LF);
const day2 = ['4,Kathleen,ok,40.00', '7,Edsger,plain,99.00', '8,Donald,ok,80.00'].join(LF);
writeFileSync('combined.csv', readFileSync('orders.csv', 'utf8') + LF + day2 + LF + LF);
console.log('wrote plain.csv quoted.csv nonl.csv crlf.csv combined.csv');

csvcount.mjs prints the counts.

import { readFileSync } from 'node:fs';
import { parseCsv, countChar, LF, CR } from './csvlib.mjs';

const mode = process.argv[2];
const pad = (v, w) => String(v).padStart(w);
const read = (f) => readFileSync(f, 'utf8');
const line = (label, v) => console.log(label.padEnd(24) + pad(v, 10));
const cps = (s) => [...s].map((c) => 'U+' + c.codePointAt(0).toString(16).toUpperCase().padStart(4, '0')).join(' ');

if (mode === 'four') {
  const file = process.argv[3];
  const text = read(file);
  const rows = parseCsv(text);
  line('file', file);
  line('bytes', Buffer.byteLength(text, 'utf8'));
  line('LF characters', countChar(text, LF));
  line('records parsed', rows.length);
  line('data rows (records - 1)', rows.length - 1);
  line('exporter reported', process.argv[4]);
} else if (mode === 'shapes') {
  console.log('file        bytes  wc -l   wc-1  split(LF)  records  data rows');
  for (const file of process.argv.slice(3)) {
    const text = read(file);
    const rows = parseCsv(text);
    const nl = countChar(text, LF);
    console.log(
      file.padEnd(11) + pad(Buffer.byteLength(text, 'utf8'), 5) + pad(nl, 7) + pad(nl - 1, 7) +
      pad(text.split(LF).length, 11) + pad(rows.length, 9) + pad(rows.length - 1, 11)
    );
  }
} else if (mode === 'crlf') {
  const text = read(process.argv[3]);
  const naive = text.split(LF);
  console.log('split(LF) parts        ' + naive.length);
  const show = (s) => '"' + s.split(CR).join('[CR]') + '"';
  console.log('part 1 raw             ' + show(naive[1]));
  console.log('part 1 last field      ' + cps(naive[1].split(',').pop()));
  console.log('part 4 raw             ' + show(naive[4]));
  const rows = parseCsv(text);
  console.log('parser records         ' + rows.length);
  console.log('parser row 1 amount    ' + cps(rows[1][3]));
} else if (mode === 'fields') {
  const file = process.argv[3];
  const text = read(file);
  const rows = parseCsv(text);
  const width = rows[0].length;
  const lines = text.split(LF);
  console.log('header width ' + width + ' fields: ' + rows[0].join(' | '));
  rows.forEach((r, i) => {
    console.log(
      'record ' + pad(i, 2) + '  fields ' + pad(r.length, 2) +
      '  ' + (r.length === width ? 'ok   ' : 'WRONG') +
      '  id ' + pad(JSON.stringify(r[0]), 4) +
      '   physical line ' + pad(i, 2) + ' split(",") gives ' + pad(lines[i] === undefined ? '-' : lines[i].split(',').length, 2) + ' fields'
    );
  });
  console.log('records at the header width: ' + rows.filter((r) => r.length === width).length + ' of ' + rows.length);
} else if (mode === 'header') {
  const rows = parseCsv(read(process.argv[3]));
  const head = rows[0];
  const isHeader = (r) => r.length === head.length && r.every((v, i) => v === head[i]);
  console.log('records                  ' + rows.length);
  console.log('records equal to head    ' + rows.filter(isHeader).length);
  console.log('data rows                ' + rows.filter((r) => !isHeader(r)).length);
  console.log('record 0 id ' + JSON.stringify(head[0]) + '   Number() ' + Number(head[0]) + '   integer ' + Number.isInteger(Number(head[0])));
  console.log('record 1 id ' + JSON.stringify(rows[1][0]) + '    Number() ' + Number(rows[1][0]) + '     integer ' + Number.isInteger(Number(rows[1][0])));
}

import.mjs reads a file back into a store.

import { readFileSync } from 'node:fs';
import { DatabaseSync } from 'node:sqlite';
import { parseCsv } from './csvlib.mjs';

const file = process.argv[2];
const db = new DatabaseSync(':memory:');
db.exec('CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, note TEXT, amount TEXT)');
const upsert = db.prepare(
  'INSERT INTO orders VALUES (?, ?, ?, ?) ON CONFLICT(id) DO UPDATE SET customer = excluded.customer, note = excluded.note, amount = excluded.amount'
);

const rows = parseCsv(readFileSync(file, 'utf8'));
const head = rows[0];
let rejected = 0;
let statements = 0;
for (const r of rows.slice(1)) {
  if (r.length !== head.length || !Number.isInteger(Number(r[0])) || r[0] === '') {
    console.error('warn: skipped record, fields ' + r.length + ', id ' + JSON.stringify(r[0]));
    rejected += 1;
    continue;
  }
  upsert.run(Number(r[0]), r[1], r[2], r[3]);
  statements += 1;
}
const stored = db.prepare('SELECT count(*) AS n FROM orders').get().n;
console.log('records parsed         ' + rows.length);
console.log('header records         1');
console.log('candidate data rows    ' + (rows.length - 1));
console.log('rejected and logged    ' + rejected);
console.log('upsert statements run  ' + statements);
console.log('rows in the store      ' + stored);
console.log('lost between the two   ' + (rows.length - 1 - stored));
console.log('id 7 amount            ' + db.prepare('SELECT amount FROM orders WHERE id = 7').get().amount);

Steps

1. 1

   Step 1.

   Run the exporter and keep its log.

   node --no-warnings export.mjs 2> export.log
   

   exported 7 rows to orders.csv
   wrote plain.csv quoted.csv nonl.csv crlf.csv combined.csv

   Seven rows, says the program that wrote the file. That is a claim, not a count.

2. 2

   Step 2.

   Read the log the same run produced.

   cat export.log
   

   warn: row 4 has no customer, skipped

   One row was dropped and the summary line did not change. A count from the exporter cannot see this.

3. 3

   Step 3.

   Print the four numbers.

   node csvcount.mjs four orders.csv 7
   

   file                    orders.csv
   bytes                          158
   LF characters                    8
   records parsed                   7
   data rows (records - 1)          6
   exporter reported                7

   Four measurements of one file, no two of them synonyms. The last line is step 1's claim, one higher than the file.

4. 4

   Step 4.

   Run wc -l on four files, one property apart.

   wc -l plain.csv quoted.csv nonl.csv crlf.csv
   

     4 plain.csv
   4 quoted.csv
   3 nonl.csv
   4 crlf.csv
   15 total

   Three report 4 and one reports 3. They hold 3, 2, 3 and 3 data rows.

5. 5

   Step 5.

   Put the same files through the parser.

   node csvcount.mjs shapes plain.csv quoted.csv nonl.csv crlf.csv
   

   file        bytes  wc -l   wc-1  split(LF)  records  data rows
   plain.csv     73      4      3          5        4          3
   quoted.csv    75      4      3          5        3          2
   nonl.csv      72      3      2          4        4          3
   crlf.csv      77      4      3          5        4          3

   wc -l minus one is right for two of the four, one too high for quoted.csv and one too low for nonl.csv. Splitting on the terminator in code is wrong on all four.

6. 6

   Step 6.

   Read the last bytes of the file wc -l undercounted.

   tail -c 20 nonl.csv | xxd
   

   00000000: 2e30 300a 332c 4c69 6e75 732c 6f6b 2c33  .00.3,Linus,ok,3
   00000010: 302e 3030                                0.00

   No 0a follows the digits of 30.00. plain.csv holds the same three rows in 73 bytes against 72, and that byte is the difference in step 4.

7. 7

   Step 7.

   Read a CRLF file two ways.

   node csvcount.mjs crlf crlf.csv
   

   split(LF) parts        5
   part 1 raw             "1,Ada,ok,10.00[CR]"
   part 1 last field      U+0031 U+0030 U+002E U+0030 U+0030 U+000D
   part 4 raw             ""
   parser records         4
   parser row 1 amount    U+0031 U+0030 U+002E U+0030 U+0030

   CRLF leaves wc -l alone, one 0a per record either way. It moves everything else: a split on the line feed leaves U+000D on every last field.

8. 8

   Step 8.

   Ask file which of these shapes it can see.

   file plain.csv nonl.csv crlf.csv
   

   plain.csv: CSV text
   nonl.csv:  CSV text
   crlf.csv:  CSV text

   CSV text three times. Terminator and missing final newline are both invisible to it.

9. 9

   Step 9.

   Count the fields of every record.

   node csvcount.mjs fields combined.csv
   

   header width 4 fields: id | customer | note | amount
   record  0  fields  4  ok     id "id"   physical line  0 split(",") gives  4 fields
   record  1  fields  4  ok     id  "1"   physical line  1 split(",") gives  4 fields
   record  2  fields  4  ok     id  "2"   physical line  2 split(",") gives  3 fields
   record  3  fields  4  ok     id  "3"   physical line  3 split(",") gives  2 fields
   record  4  fields  4  ok     id  "5"   physical line  4 split(",") gives  4 fields
   record  5  fields  4  ok     id  "6"   physical line  5 split(",") gives  3 fields
   record  6  fields  4  ok     id  "7"   physical line  6 split(",") gives  2 fields
   record  7  fields  4  ok     id  "4"   physical line  7 split(",") gives  4 fields
   record  8  fields  4  ok     id  "7"   physical line  8 split(",") gives  4 fields
   record  9  fields  4  ok     id  "8"   physical line  9 split(",") gives  4 fields
   record 10  fields  1  WRONG  id   ""   physical line 10 split(",") gives  4 fields
   records at the header width: 10 of 11

   Ten of eleven records carry the header's four fields. Record 10 carries one, the empty line at the end. A record off the header width means the reader and the writer disagree about where records end, so no count from that file holds.

10. 10

    Step 10.

    Separate the header from the data.

    node csvcount.mjs header orders.csv
    

    records                  7
    records equal to head    1
    data rows                6
    record 0 id "id"   Number() NaN   integer false
    record 1 id "1"    Number() 1     integer true

    Seven records, one equal to the header, six data rows. Record 0 matches the header strings and its key field is not a number.

11. 11

    Step 11.

    Import the export and count what the store holds.

    node --no-warnings import.mjs orders.csv
    

    records parsed         7
    header records         1
    candidate data rows    6
    rejected and logged    0
    upsert statements run  6
    rows in the store      6
    lost between the two   0
    id 7 amount            70.00

    Six records in, six rows stored. The row dropped in step 1 is still gone: seven reported, six arrived.

12. 12

    Step 12.

    Import two exports joined end to end.

    node --no-warnings import.mjs combined.csv
    

    warn: skipped record, fields 1, id ""
    records parsed         11
    header records         1
    candidate data rows    10
    rejected and logged    1
    upsert statements run  9
    rows in the store      8
    lost between the two   2
    id 7 amount            99.00

    Ten candidate rows, eight rows stored. One was the empty final line, rejected to stderr. One was a second id 7 the upsert folded into the first, taking the amount from 70.00 to 99.00. Neither raised an error.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | wc -l minus one equals the parsed data rows | No field spans a terminator and the file ends with one | The shortcut is safe on this writer's output. Re-measure after any change to it. | | wc -l is higher than the record count | A field holds a quoted terminator | Count with a parser. Step 5 reads quoted.csv as 4 terminators and 2 data rows. | | wc -l is one lower than you expect | The last record has no terminator | Read the tail bytes as step 6 does before filing a short export. | | A record whose field count is not the header width | The file is not a rectangle | Stop counting and open that record. Step 9 names it by index. | | A record of one empty field at the end | The file ends with two terminators | Decide whether the importer drops it or stores it. Step 12 rejects it. | | The key field of record 0 is not a number | Record 0 is the header | Subtract it once. Counting it and then subtracting again is the same bug twice. | | The store holds fewer rows than the file | A key collided or a row was rejected | Read the importer's stderr and compare distinct keys, as step 12 does. |

Common mistakes

Thresholds

What to check next

• How to escape quotes in csv: what decides where a record ends.
• How to compare two csv files: the next question once counts agree.
• How to check duplicate rows on import: the two records step 12 folded into one.
• How to test a partially applied import: the rows an importer rejected and logged.
• How to test a large data export: the same counts on a file too big to print.

FAQ

How do I check the number of rows in csv?

Parse the file and subtract the header. Step 3 reports 7 records and 6 data rows for a 158 byte file where wc -l returns 8.

How to check how many rows in a csv file with a newline in a field?

Only a parser answers that. Step 5 measures quoted.csv at 4 terminators, 3 records and 2 data rows. The quoted terminator ends no record.

How to count the number of lines in a csv file?

A different number, and wc -l gives it with one caveat: it counts terminators, so a file whose last line has none reads one short.

Does the header row count as a row?

Not as a data row. Step 10 separates them by comparing record 0 against the header strings and by type checking the key column.

Verified