mygems

How to compare two CSV files and find what changed

diff compares lines, so a re-sorted export looks entirely rewritten. Here is how to compare by key instead: what is new, what is gone, what changed.

By uos ·

diff old.csv new.csv answers the wrong question. It compares lines, so a file that was sorted differently comes back as "everything changed", and a row whose one field was corrected comes back as a deletion plus an addition rather than a change.

What you want is a comparison by key: which rows are new, which are gone, and, for the rows in both, which fields differ. Three questions, and they need three different answers.

Start with the shape

Before comparing content, check the two files are comparable at all:

head -n 1 old.csv new.csv
wc -l old.csv new.csv

A changed header is the most common reason a comparison produces nonsense. If a column was added, renamed, or reordered between the two exports, every field-level comparison downstream is meaningless until you account for it. Row counts are the second signal: a difference of three is a normal week, a difference of 40,000 usually means one export was truncated or filtered.

The three-question comparison in SQL

Pick your key first: the column that identifies the same record in both files. id, order_id, email. If there isn't one, see the last section.

Rows only in the new file:

duckdb -c "SELECT * FROM read_csv('new.csv') n WHERE n.id NOT IN (SELECT id FROM read_csv('old.csv'))"

Rows only in the old file:

duckdb -c "SELECT * FROM read_csv('old.csv') o WHERE o.id NOT IN (SELECT id FROM read_csv('new.csv'))"

Rows in both, with a changed field:

duckdb -c "SELECT o.id, o.status old_status, n.status new_status FROM read_csv('old.csv') o JOIN read_csv('new.csv') n USING (id) WHERE o.status IS DISTINCT FROM n.status"

IS DISTINCT FROM rather than <> is the detail that matters: <> returns NULL when either side is NULL, so a field that went from empty to filled, or filled to empty, silently drops out of your results. That is the change you were most likely looking for.

To catch changes across every column at once without naming them:

duckdb -c "SELECT * FROM read_csv('old.csv') EXCEPT SELECT * FROM read_csv('new.csv')"

EXCEPT compares whole rows and ignores order, which makes it a much better diff than diff. It tells you that a row changed, not which field, so pair it with the join above once you know which rows to look at.

When the files are just sorted differently

If you only need to know whether the content is the same, sort and hash:

tail -n +2 old.csv | sort | md5sum
tail -n +2 new.csv | sort | md5sum

Same hash, same content, regardless of row order. Different hash tells you nothing about what changed, but it takes a second and it settles the "are these the same export?" question that often turns out to be the whole enquiry.

For a line-level diff that survives reordering:

diff <(sort old.csv) <(sort new.csv)

Still line-based, so a one-field change still shows as a pair, but at least sorting no longer dominates the output.

Dedicated tools

csvdiff and daff both compare by key and produce output that says "column status changed from A to B for row 41" rather than showing you two lines. If you do this regularly, install one of them. daff diff old.csv new.csv --output diff.html produces a coloured table you can read at a glance, which none of the above are.

Git, if the files are versioned. Setting a CSV diff driver in .gitattributes turns git diff on a data file from useless to useful, and it's a two-line change.

Excel, honestly, is poor at this. The usual approach, a VLOOKUP per column into the other sheet, works for small files, is quadratic on large ones, and produces a grid of #N/A that is hard to read. Power Query's merge is much better and almost nobody uses it.

Reading the differences on a phone

The common real-world version of this: two exports a week apart, in your email, and the question is "what changed" rather than "produce a reconciliation report".

The practical route is not a diff at all. Add a column marking the source, stack the two files, sort by the key, and read the pairs. The changed fields are visible side by side because the two versions of each record are now adjacent. For a few dozen rows this is faster than any tool.

CSV File Viewer - Smart CSViPhone & iPad · Android

SQL against the file directly is the whole comparison: a join on the key with IS DISTINCT FROM finds the changed fields, and the AI assistant handles the version of the question you would rather ask in words than write out.

What it does

Numbers, dates and the false differences

A comparison usually turns up a large number of "changes" that aren't. Rule them out before investigating:

  • Trailing whitespace. "active" and "active " differ. Compare trim()ed values unless whitespace is meaningful, which it almost never is.
  • Number formatting. 1000, 1,000 and 1000.00 are one number and three strings. Cast to a numeric type on both sides before comparing.
  • Date formats. 01/02/2026 versus 2026-02-01, and worse, 01/02/2026 in one file meaning February and in another meaning January. Parse both to dates rather than comparing text.
  • Float noise. 0.1 + 0.2 is not 0.3 in any language you are using. Round to the precision that matters before comparing decimals.
  • Encoding. A name that renders as José in one file and José in the other is an encoding difference, not a data change.

Each of these produces differences in the thousands on a large file, which is how a comparison ends up "showing everything as changed".

CSV Editor - Smart CSViPhone & iPad

Where the comparison turns into a fix, such as normalising a column, rounding a decimal or correcting the rows that did change, a formula column applies it down every row, and the result saves inside the CSV.

What it does

When there is no key

Sometimes the files have no identifier: two exports of a log, two lists of transactions. You have two options and they are both compromises.

Build a composite key from the columns that together identify a row: date plus amount plus description, say. This works when the combination really is unique, and it fails quietly when it isn't: two identical transactions on the same day become one, and the comparison reports a phantom deletion.

Compare as multisets. Count occurrences of each distinct row in each file and diff the counts. This is what you want for logs and transactions, where the same row legitimately appears more than once and the meaningful change is how many times:

duckdb -c "SELECT *, count(*) n FROM read_csv('old.csv') GROUP BY ALL"

Run it against both files and compare the n columns. It is the only approach that gets duplicate-heavy data right.

Questions

How do I compare two CSV files and find the differences?
Compare by key rather than by line. Ask three separate questions: which keys are only in the new file, which are only in the old one, and for keys in both, which fields differ. In SQL that is two anti-joins and one join with `IS DISTINCT FROM` per column. A plain `diff` reports a one-field change as a deletion plus an addition, and reports a re-sorted file as entirely changed.
Why does diff say every row changed when the data looks the same?
Almost always because the two exports are sorted differently. `diff` compares lines in order, so a reordered file looks completely rewritten. `diff <(sort old.csv) <(sort new.csv)` removes that effect, and `SELECT * FROM old EXCEPT SELECT * FROM new` ignores row order entirely.
How do I compare two CSV files in Excel?
Use Power Query's merge rather than VLOOKUP. A VLOOKUP per column into the other sheet works for a few hundred rows, is quadratic on large files, and produces a grid of #N/A that is hard to read. Power Query joins on a key, keeps both versions of each column side by side, and refreshes when the files are replaced.
Why is my CSV comparison showing thousands of false changes?
Formatting differences that are not data differences: trailing whitespace, thousands separators, `1000` versus `1000.00`, two date formats, floating-point noise, and mismatched text encodings. Normalise both sides (trim, cast numbers to numeric, parse dates as dates, round decimals) before comparing, or every one of those shows up as a change on every affected row.
How do I compare CSV files that have no ID column?
Either build a composite key from the columns that together identify a row, or compare the files as multisets by counting occurrences of each distinct row on both sides and diffing the counts. The second is the right answer for logs and transactions, where the same row can legitimately repeat and the change you care about is how many times it appears.
What is the fastest way to tell if two CSV files are identical?
Strip the header, sort, and hash: `tail -n +2 file.csv | sort | md5sum` on each. Matching hashes mean identical content regardless of row order. It tells you nothing about what differs, but it takes a second and it often settles the question you had, which is whether the two exports are the same one.

uos — Builds CSV Editor and Smart CSV Viewer, and has reconciled enough pairs of exports to distrust every line-based diff.

The apps in this guide

All guides