How to Compare Two CSV Files and Find the Differences

You have last month's export and this month's, and you need to know what changed. Most tools treat this as a text problem. It is a record-matching problem, and almost every frustrating result below comes from that mismatch.

diff, and why it usually disappoints

The obvious first attempt:

diff old.csv new.csv

This compares line 1 to line 1, line 2 to line 2. It works only if both files list rows in exactly the same order — and real exports reorder constantly, because they come out of a database with no stable sort. Insert one row near the top and diff reports every line after it as changed.

Sorting first helps:

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

Now row order stops mattering. But you have traded one problem for another: a row where a single field was edited shows up as one deletion plus one addition, and it is on you to spot that they are the same record. With a few hundred rows that is unreadable. The header row also sorts into the middle of the file.

diff is the right tool when you need to know whether two files differ. It is the wrong tool for knowing what changed about which customer.

Excel with XLOOKUP

If both files are open as tables, you can check one column at a time:

=IF(XLOOKUP([@email], old[email], old[plan], "NOT IN OLD") = [@plan],
    "same", "changed")

This works, and for a quick look at one or two fields it is genuinely the fastest option — you are already in Excel and the answer is in front of you.

The limit is arithmetic. You need one formula column per field you care about. That is fine for three columns and miserable for thirty, and it tells you nothing about rows that were deleted — those simply do not appear in the new file to be checked.

Power Query, which is the real Excel answer

Underused, and the strongest free desktop option:

1. Data → Get Data → From File for both CSVs.
2. Merge Queries, match on your key column, join kind Full Outer.
3. Expand the second table's columns.

You now get every row from both files side by side, with nulls marking rows that exist on only one side. Add a column comparing old and new values and you have added, removed, and changed in one table — refreshable next month by dropping in new files and hitting Refresh.

It is fiddly to set up the first time. It is the correct answer if this is a recurring job.

Python with pandas

import pandas as pd

old = pd.read_csv("old.csv").set_index("email")
new = pd.read_csv("new.csv").set_index("email")

added = new.index.difference(old.index)
removed = old.index.difference(new.index)
common = old.index.intersection(new.index)

cols = old.columns.intersection(new.columns)
changed = old.loc[common, cols].compare(new.loc[common, cols])

.compare() is the part worth knowing: it returns only the cells that differ, labelled self and other, instead of a wall of matching values.

Two things to watch. set_index on a column with duplicate values gives you a non-unique index and the alignment quietly does the wrong thing — check old.index.is_unique first. And read_csv will infer types, so 01234 in one file and 1234 in the other will compare as different when they are the same account number. Pass dtype=str if the values are identifiers rather than quantities.

In the browser

The decision that actually determines your results

Every method above depends on one question: what makes two rows the same row?

Pick the wrong column and the output is nonsense. Row position is not identity. Name is not identity — people change theirs, and two customers share one. An email or an account ID usually is.

There is a second-order problem that catches people out. If your key is an email, then Jane@Example.com in one file and jane@example.com in the other are the same customer, and any method that compares them as raw text will report a deletion and an addition rather than a match. The comparison tool here normalises keys before matching — trimming whitespace and lowercasing — so those land as one record.

That is also why key columns are excluded from change detection. If a key matched case-insensitively but was then compared strictly, every row whose email changed capitalisation would be flagged as edited when nothing about the record actually changed.

Limitations worth knowing before you trust the output

Honest about the tool above, and mostly true of the other methods too:

• Duplicate keys collapse. If your key column contains the same value twice, only the last of those rows is compared. Deduplicate first, or use a combination of columns as the key.
• With no key column selected, matching falls back to comparing entire rows. You will get accurate added and removed lists, but nothing will ever be reported as changed — an edited row is simply one removal and one addition, the same limitation diff has.
• Added or removed columns are reported separately from row changes. A renamed column reads as one column removed and another added, because nothing in a CSV records that they are related.

Which to use

Reach for diff to answer "are these identical." Use Excel formulas for a one-off look at a couple of fields. Set up Power Query if you will run this every month. Use pandas if the files are large or the comparison needs custom logic. Use the browser tool when you want the answer now and the file contains data you would rather not upload anywhere.