How to compare two CSV files and find the differences

You have two exports of the same data – last month and this month, or the old system and the new one – and you need to know what changed. The trap is comparing the files line by line. As soon as one file is sorted differently or a row is inserted, every line after it looks "different".

The reliable way is to compare by key: pair each row with its counterpart using a column that identifies it (customer ID, SKU, invoice number, email), then sort the result into three groups:

• Added – the key exists only in the new file.
• Removed – the key exists only in the old file.
• Changed – the key exists in both, but at least one other column differs.

Here are four ways to do it, from no tools at all to code.

1. Excel or Google Sheets: XLOOKUP

Good for a few thousand rows. (Starting from .xlsx files? See the full guide on comparing two Excel sheets.) Put both files into one workbook, old data in sheet A and new data in sheet B, with the key in column A.

In sheet B, find rows that are new:

=IF(ISNA(MATCH(A2, A!A:A, 0)), "added", "")

In sheet A, find rows that were removed:

=IF(ISNA(MATCH(A2, B!A:A, 0)), "removed", "")

To spot a changed value in, say, column D, look it up by key and compare:

=IF(XLOOKUP(A2, A!A:A, A!D:D, "") <> D2, "changed", "")

Watch out for: trailing spaces and numbers stored as text (wrap values in TRIM() and VALUE()), one formula per column you want to check, and Excel slowing to a crawl past a few hundred thousand rows.

2. Python with pandas

Good for analysts who already use Python and files that fit in memory.

import pandas as pd

a = pd.read_csv("old.csv", dtype=str).fillna("")
b = pd.read_csv("new.csv", dtype=str).fillna("")
key = "customer_id"

m = a.merge(b, on=key, how="outer", suffixes=("_a", "_b"), indicator=True)
added   = m[m["_merge"] == "right_only"]
removed = m[m["_merge"] == "left_only"]

both = m[m["_merge"] == "both"]
cols = [c for c in a.columns if c != key and c in b.columns]
changed = both[(both[[c + "_a" for c in cols]].values
                != both[[c + "_b" for c in cols]].values).any(axis=1)]

print(len(added), "added,", len(removed), "removed,", len(changed), "changed")

Reading everything as text (dtype=str) avoids false differences such as 007 vs 7. For files bigger than your RAM, the same logic works in DuckDB with a FULL OUTER JOIN.

3. The command line

If you only need to know which keys appeared or disappeared, sorting the key column and using comm is very fast:

cut -d, -f1 old.csv | tail -n +2 | sort > old_keys.txt
cut -d, -f1 new.csv | tail -n +2 | sort > new_keys.txt
comm -13 old_keys.txt new_keys.txt   # added
comm -23 old_keys.txt new_keys.txt   # removed

This breaks on quoted fields that contain commas and does not tell you which values changed, so treat it as a quick check.

4. A free browser tool (no upload)

Calamo's free CSV compare does the key-based comparison for you: drop both files, pick the key column, and you get added, removed and changed rows with a cell-by-cell view of every change. Column order does not matter, delimiters are detected automatically, and you can ignore whitespace, letter case and number formatting. The files are processed inside your browser and never uploaded.

Common reasons for false differences

• Whitespace – "Acme " vs "Acme". Trim before comparing.
• Number formats – 1.50 vs 1,5 vs 1.5, especially between European and US exports.
• Leading zeros – IDs like 00123 lose their zeros when a spreadsheet treats them as numbers.
• Duplicate keys – if the key is not unique, rows get paired with the wrong partner. Combine two columns into a composite key.
• Encoding – the same name in UTF-8 and Windows-1250 looks different. Export both files as UTF-8.

Questions