How to compare two csv files

Decide which of four questions you mean. sha256sum a.csv b.csv answers byte equality. Parsing answers record equality. node csvdiff.mjs keyed a.csv c.csv --key email answers data equality whatever the row order: here diff reported 8 differing lines on two files whose records matched exactly. Column order needs the fourth.

Why check this

Run this when an export is regenerated: after a change to the writer, after a migration, and on a ticket saying two extracts differ.

Four questions hide under one word. Identical bytes. Identical records. Identical data whatever the row order. Identical data whatever the column order and the spelling of a value. Each has its own command and answer.

The failure this prevents is a release held up by a difference that is not one. diff reports a re-sorted export as changed throughout, and the row that did change sits inside the noise. The opposite failure costs more: rules added to quiet the noise until a changed key goes unreported.

Prerequisites

• Node 22 for the two programs below, dependency free.
• sha256sum and wc from GNU coreutils 8.32, cmp and diff from GNU diffutils 3.10, from Git Bash on Windows 11.
• A scratch directory. Every CRLF, byte order mark and quote is built from String.fromCharCode, because a typed escape can arrive decoded.
• What quoting does to a field: How to escape quotes in csv.
• The byte order mark step 3 reports: How to check the encoding of a CSV file.

make.mjs writes a reference export, a byte copy, and four files differing in one way each.

import { writeFileSync, copyFileSync, statSync } from 'node:fs';

const CR = String.fromCharCode(13);
const LF = String.fromCharCode(10);
const DQ = String.fromCharCode(34);
const BOM = String.fromCharCode(0xfeff);

const head = ['id', 'email', 'name', 'amount', 'region'];
const data = [
  ['1', 'ada@example.com', 'Ada Lovelace', '10.00', 'EU'],
  ['2', 'grace@example.com', 'Grace Hopper', '20.50', 'US'],
  ['3', 'linus@example.com', 'Linus Torvalds', '1.50', 'EU'],
  ['4', 'barbara@example.com', 'Barbara Liskov', '40.00', ''],
  ['5', 'alan@example.com', 'Alan Turing', '55.25', 'EU'],
  ['6', 'edsger@example.com', 'Edsger Dijkstra', '60.00', 'EU'],
  ['7', 'donald@example.com', 'Donald Knuth', '70.00', 'US'],
  ['8', 'kathleen@example.com', 'Kathleen Booth', '80.75', 'EU'],
];

const join = (rows, term, finalTerm) =>
  rows.map((r) => r.join(',')).join(term) + (finalTerm ? term : '');

// a.csv: the reference export. LF, no mark, a final terminator, no quoting.
writeFileSync('a.csv', join([head, ...data], LF, true), 'utf8');
copyFileSync('a.csv', 'a_copy.csv');

// b.csv: the same eight records written by a second exporter. Five differences,
// four of them presentation: a byte order mark, CRLF, no final terminator and one
// field quoted that needed no quoting. The fifth is the amount of record 3.
const bRows = data.map((r) => r.slice());
bRows[4][2] = DQ + bRows[4][2] + DQ;
bRows[2][3] = '1.5';
writeFileSync('b.csv', BOM + join([head, ...bRows], CR + LF, false), 'utf8');

// c.csv: a.csv's records, sorted by name instead of by id.
const byName = data.slice().sort((x, y) => (x[2] < y[2] ? -1 : 1));
writeFileSync('c.csv', join([head, ...byName], LF, true), 'utf8');

// d.csv: c.csv's order with three real differences. One record dropped, one
// amount changed, one key written twice with a different region.
const dRows = byName.filter((r) => r[0] !== '4').map((r) => r.slice());
for (const r of dRows) if (r[0] === '6') r[3] = '65.00';
const graceAt = dRows.findIndex((r) => r[0] === '2');
dRows.splice(graceAt + 1, 0, ['2', 'grace@example.com', 'Grace Hopper', '20.50', 'EU']);
writeFileSync('d.csv', join([head, ...dRows], LF, true), 'utf8');

// e.csv: the same eight records from a third system. Columns reordered, one
// column added in the middle, and four values spelled differently.
const eHead = ['id', 'name', 'currency', 'email', 'amount', 'region'];
const currency = { EU: 'EUR', US: 'USD', '': 'USD' };
const eRows = data.map((r) => {
  const row = [r[0], r[2], currency[r[4]], r[1], r[3], r[4]];
  if (r[0] === '3') row[4] = '1.5';
  if (r[0] === '7') row[3] = ' Donald@example.com';
  if (r[0] === '4') row[5] = 'NULL';
  return row;
});
writeFileSync('e.csv', join([eHead, ...eRows], LF, true), 'utf8');

for (const f of ['a.csv', 'a_copy.csv', 'b.csv', 'c.csv', 'd.csv', 'e.csv']) {
  console.log(f.padEnd(11) + 'bytes ' + String(statSync(f).size).padStart(4));
}

csvdiff.mjs answers the other three: shape reports byte properties, records compares parsed records by position, keyed matches rows by key and fields by name.

import { readFileSync } from 'node:fs';

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

// RFC 4180 reader. Quoting, CRLF and the byte order mark are settled here, so
// every comparison below works on values rather than on bytes.
export function parse(text) {
  if (text.charCodeAt(0) === 0xfeff) text = text.slice(1);
  const rows = [];
  let row = [];
  let field = '';
  let quoted = false;
  let i = 0;
  while (i < text.length) {
    const c = text[i];
    if (quoted) {
      if (c === DQ && text[i + 1] === DQ) { field += DQ; i += 2; continue; }
      if (c === DQ) { quoted = false; i += 1; continue; }
      field += c; i += 1; continue;
    }
    if (c === DQ && 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;
}

const read = (f) => readFileSync(f, 'utf8');
const padR = (v, w) => String(v).padEnd(w);
const padL = (v, w) => String(v).padStart(w);
const show = (v) => (v === undefined ? '(absent)' : DQ + v + DQ);
const list = (a) => (a.length === 0 ? '(none)' : a.join(' '));

// Each rule is off unless a flag turns it on, because each one can hide a
// difference that matters. --fold-case folds every value, not only the key.
function normalizer(opt) {
  return (v) => {
    let s = v;
    if (opt.trim) s = s.trim();
    // Before --fold-case, or the spelling the flag was given no longer matches.
    if (opt.nullText !== undefined && s === opt.nullText) s = '';
    if (opt.foldCase) s = s.toLowerCase();
    if (opt.number && s !== '' && Number.isFinite(Number(s))) s = String(Number(s));
    return s;
  };
}

// Question one: are the bytes the same, and if not, which byte properties differ.
function shape(files) {
  console.log(padR('file', 11) + padL('bytes', 6) + padL('BOM', 5) + padL('CRLF', 6) +
    padL('bare LF', 9) + padL('final term', 12) + padL('quote bytes', 13));
  for (const f of files) {
    const t = read(f);
    const crlf = t.split(CR + LF).length - 1;
    const lf = t.split(LF).length - 1 - crlf;
    const last = t[t.length - 1];
    console.log(padR(f, 11) + padL(Buffer.byteLength(t, 'utf8'), 6) +
      padL(t.charCodeAt(0) === 0xfeff ? 'yes' : 'no', 5) + padL(crlf, 6) + padL(lf, 9) +
      padL(last === LF || last === CR ? 'yes' : 'no', 12) + padL(t.split(DQ).length - 1, 13));
  }
}

// Question two: same records, compared where they sit. Column names come from
// the first file, so this answer is only as good as the two files being aligned.
function records(fa, fb) {
  const A = parse(read(fa));
  const B = parse(read(fb));
  const head = A[0] ?? [];
  console.log('records        ' + padL(A.length, 3) + ' vs ' + padL(B.length, 3));
  const count = (rows) => rows.reduce((n, r) => n + r.length, 0);
  console.log('fields         ' + padL(count(A), 3) + ' vs ' + padL(count(B), 3));
  let changed = 0;
  for (let i = 0; i < Math.max(A.length, B.length); i += 1) {
    const a = A[i] ?? [];
    const b = B[i] ?? [];
    const diff = [];
    for (let j = 0; j < Math.max(a.length, b.length); j += 1) {
      if (a[j] !== b[j]) diff.push(j);
    }
    if (diff.length === 0) continue;
    changed += 1;
    console.log('record ' + padL(i, 2) + '  fields ' + padL(a.length, 2) + ' vs ' + padL(b.length, 2) +
      '  differing ' + padL(diff.length, 2));
    if (diff.length <= 2) {
      for (const j of diff) {
        console.log('    [' + (head[j] ?? 'column ' + j) + '] ' + show(a[j]) + ' vs ' + show(b[j]));
      }
    }
  }
  console.log('records with at least one differing field: ' + changed + ' of ' + Math.max(A.length, B.length));
  console.log('verdict        ' + (changed === 0 ? 'same records' : 'different records'));
}

// Questions three and four: same data, whatever order the rows and the columns
// are in. Rows are matched by key, fields by column name.
function keyed(fa, fb, opt) {
  const A = parse(read(fa));
  const B = parse(read(fb));
  const ha = A[0] ?? [];
  const hb = B[0] ?? [];
  const shared = ha.filter((c) => hb.includes(c));
  const norm = normalizer(opt);

  const index = (rows, head) => {
    const k = head.indexOf(opt.key);
    const map = new Map();
    const dup = new Map();
    for (const r of rows.slice(1)) {
      const key = norm(r[k] ?? '');
      if (map.has(key)) dup.set(key, (dup.get(key) ?? 1) + 1);
      else map.set(key, r);
    }
    return { map, dup };
  };
  const a = index(A, ha);
  const b = index(B, hb);

  const onlyA = [...a.map.keys()].filter((k) => !b.map.has(k));
  const onlyB = [...b.map.keys()].filter((k) => !a.map.has(k));
  const both = [...a.map.keys()].filter((k) => b.map.has(k));
  const changes = [];
  for (const k of both) {
    for (const col of shared) {
      const va = norm(a.map.get(k)[ha.indexOf(col)] ?? '');
      const vb = norm(b.map.get(k)[hb.indexOf(col)] ?? '');
      if (va !== vb) changes.push([k, col, va, vb]);
    }
  }
  const dups = [...a.dup].map(([k, n]) => 'A:' + k + ' x' + n)
    .concat([...b.dup].map(([k, n]) => 'B:' + k + ' x' + n));

  console.log('key column            ' + opt.key);
  console.log('rules applied         ' + list(opt.rules));
  console.log('columns in both       ' + list(shared));
  console.log('columns only in A     ' + list(ha.filter((c) => !hb.includes(c))));
  console.log('columns only in B     ' + list(hb.filter((c) => !ha.includes(c))));
  console.log('data rows A / B       ' + (A.length - 1) + ' / ' + (B.length - 1));
  console.log('keys matched          ' + both.length);
  console.log('only in A             ' + list(onlyA));
  console.log('only in B             ' + list(onlyB));
  console.log('duplicate keys        ' + list(dups));
  console.log('changed fields        ' + changes.length);
  for (const [k, col, va, vb] of changes) {
    console.log('    ' + padR(k, 22) + padR('[' + col + ']', 10) + show(va) + ' -> ' + show(vb));
  }
  const same = onlyA.length === 0 && onlyB.length === 0 && dups.length === 0 &&
    changes.length === 0 && shared.length === ha.length && shared.length === hb.length;
  console.log('verdict               ' + (same ? 'same data' : 'different data'));
}

// --key and --null take a value, the rest are switches.
const argv = process.argv.slice(2);
const mode = argv[0];
const files = [];
const opt = { rules: [] };
for (let i = 1; i < argv.length; i += 1) {
  const a = argv[i];
  if (a === '--key') { opt.key = argv[i + 1]; i += 1; continue; }
  if (a === '--null') { opt.nullText = argv[i + 1]; opt.rules.push('null=' + argv[i + 1]); i += 1; continue; }
  if (a === '--trim') { opt.trim = true; opt.rules.push('trim'); continue; }
  if (a === '--fold-case') { opt.foldCase = true; opt.rules.push('fold-case'); continue; }
  if (a === '--number') { opt.number = true; opt.rules.push('number'); continue; }
  files.push(a);
}

if (mode === 'shape') shape(files);
else if (mode === 'records') records(files[0], files[1]);
else if (mode === 'keyed') keyed(files[0], files[1], opt);
else console.log('usage: csvdiff.mjs shape|records|keyed <files> [--key col] [--trim] [--fold-case] [--number] [--null TEXT]');

Build the fixtures.

node make.mjs

a.csv      bytes  373
a_copy.csv bytes  373
b.csv      bytes  384
c.csv      bytes  373
d.csv      bytes  371
e.csv      bytes  418

Steps

1. 1

   Step 1.

   Ask the cheapest question first.

   sha256sum a.csv a_copy.csv b.csv
   

   c0a51581789a5c371ad19f40c7dbc7abc35f5cfc0eefbaf37f59ae017457e28f *a.csv
   c0a51581789a5c371ad19f40c7dbc7abc35f5cfc0eefbaf37f59ae017457e28f *a_copy.csv
   2b45818ea79cb7a6e0648395ba7b996f0cc86fb7f3b62cecc25233aac7e748a2 *b.csv

   Two hashes match, so those files are one file. The third differs, and a hash does not say how.

2. 2

   Step 2.

   Find where the bytes part.

   cmp a.csv b.csv
   

   a.csv b.csv differ: char 1, line 1

   Character 1 of line 1, before any data: the byte order mark, and cmp stops there.

3. 3

   Step 3.

   Measure the byte properties two exports differ in.

   node csvdiff.mjs shape a.csv a_copy.csv b.csv c.csv d.csv e.csv
   

   file        bytes  BOM  CRLF  bare LF  final term  quote bytes
   a.csv         373   no     0        9         yes            0
   a_copy.csv    373   no     0        9         yes            0
   b.csv         384  yes     8        0          no            2
   c.csv         373   no     0        9         yes            0
   d.csv         371   no     0        9         yes            0
   e.csv         418   no     0        9         yes            0

   b.csv carries a mark, CRLF, no final terminator and 2 quote bytes: four choices, 11 bytes.

4. 4

   Step 4.

   Parse both files and compare record by record.

   node csvdiff.mjs records a.csv b.csv
   

   records          9 vs   9
   fields          45 vs  45
   record  3  fields  5 vs  5  differing  1
     [amount] "1.50" vs "1.5"
   records with at least one differing field: 1 of 9
   verdict        different records

   45 fields each side, so nothing was lost, and one difference survives: 1.50 against 1.5.

5. 5

   Step 5.

   Run diff on two files holding the same records in another order.

   diff a.csv c.csv
   

   3,5d2
   < 2,grace@example.com,Grace Hopper,20.50,US
   < 3,linus@example.com,Linus Torvalds,1.50,EU
   < 4,barbara@example.com,Barbara Liskov,40.00,
   7c4
   < 6,edsger@example.com,Edsger Dijkstra,60.00,EU
   ---
   > 4,barbara@example.com,Barbara Liskov,40.00,
   8a6,7
   > 6,edsger@example.com,Edsger Dijkstra,60.00,EU
   > 2,grace@example.com,Grace Hopper,20.50,US
   9a9
   > 3,linus@example.com,Linus Torvalds,1.50,EU

   Three rows deleted, three added, one replaced, and no value changed. Another diff may choose a different edit script; the differing-line count is what matters.

6. 6

   Step 6.

   Compare that pair by position after parsing.

   node csvdiff.mjs records a.csv c.csv
   

   records          9 vs   9
   fields          45 vs  45
   record  2  fields  5 vs  5  differing  5
   record  3  fields  5 vs  5  differing  5
   record  4  fields  5 vs  5  differing  5
   record  5  fields  5 vs  5  differing  4
   record  6  fields  5 vs  5  differing  5
   record  7  fields  5 vs  5  differing  5
   record  8  fields  5 vs  5  differing  4
   records with at least one differing field: 7 of 9
   verdict        different records

   7 of 9 records differ, because a parser still assumes row 3 matches row 3.

7. 7

   Step 7.

   Drop that assumption and match rows by a key.

   node csvdiff.mjs keyed a.csv c.csv --key email
   

   key column            email
   rules applied         (none)
   columns in both       id email name amount region
   columns only in A     (none)
   columns only in B     (none)
   data rows A / B       8 / 8
   keys matched          8
   only in A             (none)
   only in B             (none)
   duplicate keys        (none)
   changed fields        0
   verdict               same data

   8 keys matched, no changed field, no row on one side only.

8. 8

   Step 8.

   Run diff on a pair that holds real differences.

   diff a.csv d.csv
   

   3,5d2
   < 2,grace@example.com,Grace Hopper,20.50,US
   < 3,linus@example.com,Linus Torvalds,1.50,EU
   < 4,barbara@example.com,Barbara Liskov,40.00,
   7d3
   < 6,edsger@example.com,Edsger Dijkstra,60.00,EU
   8a5,7
   > 6,edsger@example.com,Edsger Dijkstra,65.00,EU
   > 2,grace@example.com,Grace Hopper,20.50,US
   > 2,grace@example.com,Grace Hopper,20.50,EU
   9a9
   > 3,linus@example.com,Linus Torvalds,1.50,EU

   Four hunks again, and which row was dropped must be worked out by hand.

9. 9

   Step 9.

   Count lines and differing lines for both pairs.

   wc -l a.csv c.csv d.csv; diff a.csv c.csv | grep -c '^[<>]'; diff a.csv d.csv | grep -c '^[<>]'
   

      9 a.csv
    9 c.csv
    9 d.csv
   27 total
   8
   8

   9 lines each and 8 differing lines each, for a pair with no difference and one with three.

10. 10

    Step 10.

    Ask the keyed comparison what changed.

    node csvdiff.mjs keyed a.csv d.csv --key email
    

    key column            email
    rules applied         (none)
    columns in both       id email name amount region
    columns only in A     (none)
    columns only in B     (none)
    data rows A / B       8 / 8
    keys matched          7
    only in A             barbara@example.com
    only in B             (none)
    duplicate keys        B:grace@example.com x2
    changed fields        1
      edsger@example.com    [amount]  "60.00" -> "65.00"
    verdict               different data

    A row only in A, a key written twice in B, a field changed with both values: three lines, each naming an action.

11. 11

    Step 11.

    Compare a file with a column added in the middle.

    node csvdiff.mjs records a.csv e.csv
    

    records          9 vs   9
    fields          45 vs  54
    record  0  fields  5 vs  6  differing  5
    record  1  fields  5 vs  6  differing  5
    record  2  fields  5 vs  6  differing  5
    record  3  fields  5 vs  6  differing  5
    record  4  fields  5 vs  6  differing  5
    record  5  fields  5 vs  6  differing  5
    record  6  fields  5 vs  6  differing  5
    record  7  fields  5 vs  6  differing  5
    record  8  fields  5 vs  6  differing  5
    records with at least one differing field: 9 of 9
    verdict        different records

    Every record differs, header included: position cannot separate a moved column from changed data.

12. 12

    Step 12.

    Match the columns by name instead.

    node csvdiff.mjs keyed a.csv e.csv --key email
    

    key column            email
    rules applied         (none)
    columns in both       id email name amount region
    columns only in A     (none)
    columns only in B     currency
    data rows A / B       8 / 8
    keys matched          7
    only in A             donald@example.com
    only in B              Donald@example.com
    duplicate keys        (none)
    changed fields        2
      linus@example.com     [amount]  "1.50" -> "1.5"
      barbara@example.com   [region]  "" -> "NULL"
    verdict               different data

    Nine differing records become four findings: a column only in B, a key spelled two ways, one amount, one empty value.

13. 13

    Step 13.

    Turn on the four rules that decide those values.

    node csvdiff.mjs keyed a.csv e.csv --key email --trim --fold-case --number --null NULL
    

    key column            email
    rules applied         trim fold-case number null=NULL
    columns in both       id email name amount region
    columns only in A     (none)
    columns only in B     currency
    data rows A / B       8 / 8
    keys matched          8
    only in A             (none)
    only in B             (none)
    duplicate keys        (none)
    changed fields        0
    verdict               different data

    Matched keys go from 7 to 8 and changed fields from 2 to 0, and the verdict stays different data over that extra column.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | Two files with one sha256 | The bytes are identical, so every later question is answered | Stop. Step 1 was the whole comparison. | | Different hashes, no differing field after parsing | The files differ in terminator, mark, final newline or quoting | Treat them as equal records. Step 3 names which of the four it was. | | diff reports many changed lines, keyed reports none | The rows came back in a different order | Compare by key. Steps 5 and 7 read the same pair. | | only in A and only in B holding two spellings of one key | The key is not normalised on both sides | Read the two strings before filing a missing row. Step 12 has a leading space and a capital letter. | | A key under duplicate keys | That key appears twice, so one of the two rows is invisible to the comparison | Open both rows. Step 10 found a second row with a different region. | | Every record differing and the field counts unequal | A column moved or was added, so positions no longer line up | Match columns by name. Step 11 against step 12. | | changed fields 0 with a non-empty columns only in B | The values agree and the column sets do not | Decide whether the extra column is expected. Step 13 keeps it as a difference. |

Common mistakes

Thresholds

What to check next

• How to count rows in a csv file: the count that has to agree first.
• How to escape quotes in csv: where step 3 quote bytes went.
• How to check the delimiter of a csv file: a separator difference reads as a changed file.
• How to check the encoding of a CSV file: the mark step 3 reports.
• How to check duplicate rows on import: the key step 10 found twice.

FAQ

How to compare two csv files for differences?

Match rows by a key column and fields by column name. Step 10 prints key, column and both values: a row only in A, a duplicate key, a changed amount, where diff gave four hunks.

How to diff two csv files?

diff compares lines by position, so it answers only on files written in one order. Step 5 has it report 8 differing lines on two files whose records match.

How to check if two csv files are identical?

sha256sum for bytes, records for parsed records, keyed for data. Step 1 settles the first. The other two need a parser: quoting, terminators and a byte order mark change bytes without changing data.

Which column should be the key?

One the exporter cannot change between runs, and one that is unique. Step 10 shows the cost: a second row under that key never reaches the comparison, and only the duplicate keys line reports it.

Verified