Compare two CSV files: what changed between exports
Two exports from consecutive periods, a file you sent and the answer to it, a product catalog before and after an update. The task sounds simple, but the usual ways of doing it produce an answer you cannot trust.
Why a line-by-line diff does not work
Text comparison tools (WinMerge, the diff in editors) match lines by position. For a table that is wrong: it is enough for one record to be inserted in the middle, and every following line is marked as changed.
Besides, the sort order of the export may have changed — then everything shows as different, even though the data is identical.
Tables are compared not by position but by key: a field or set of fields that uniquely identifies a record.
Why VLOOKUP is not enough
The classic approach is to bring both exports onto one sheet and compare them with a formula. It works, with reservations:
- VLOOKUP tells you whether a record exists but not which field changed — that takes a formula per column;
- on a couple of hundred thousand rows recalculation takes minutes;
- Excel has already damaged leading zeros on the way, and keys stopped matching — the most annoying and most frequent cause of false differences (see leading zeros).
How to do it properly
The procedure is the same for any two tables:
- Choose the key. One field or several: an order ID, a pair such as SKU + warehouse, a customer ID + date. The key must be unique — if it is not, the tool should say how many repeats it met.
- Match columns by name, not by position: a field may have been added in the middle of the new export.
- Get four numbers: added, removed, changed, unchanged. They should add up to the number of rows — the first sanity check of the result.
- See what exactly changed, down to the field: was → now.
In Tabulens both exports open in tabs, the key is chosen with a click, and the discrepancy report can be exported to CSV — ready to hand to whoever corrects the data in the source system.
Comparison traps
- Whitespace.
"Smith "and"Smith"are different strings. Trimming the edges should be on, or half the records will show as “changed”. - Case. For codes it usually matters, for surnames usually not. It is a setting, not a dogma.
- Number formats.
10.5and10,50are the same value written differently. Compare the converted values, not the text. - A non-unique key. If several records share one key, the comparison loses its meaning: add another field to the key.
FAQ
Can I compare a CSV with a DBF?
Yes, provided both tables have columns with the same names: the format of the source does not matter for the comparison.
What if the files are sorted differently?
For a comparison by key the order of rows does not matter.
How fast is it?
Comparing two tables of about fifteen hundred rows takes tens of milliseconds; on hundreds of thousands of rows, seconds.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.