Compare two CSV files by key, even when rows move
A reordered file should not turn every row into a difference. Start with record identity.
A line-by-line diff answers “what moved in this text file?” A record comparison answers “what changed for this order, customer or item?” Those are different questions. If two systems sort their exports differently, comparing line 20 with line 20 tells you very little about the underlying records.
The useful starting point is a key: a field, or combination of fields, that identifies the same record in both exports. Keep the original files intact while you work. Every example below is synthetic.
Choose identity before choosing columns
For an order export, order_id may be a good key. For order lines, it probably is not: one order can contain several items. A combination such as order_id and line_id can identify each line. Names, postal codes and amounts are usually poor first choices because unrelated records can share them and legitimate edits can change them.
| File | Order ID | Delivery city |
|---|---|---|
| Monday.csv | ORD-104 | Bristol |
| Monday.csv | ORD-105 | Leeds |
| Tuesday.csv | ORD-105 | York |
| Tuesday.csv | ORD-104 | Bristol |
With Order ID as the key, ORD-104 is unchanged and ORD-105 has a changed city. Their positions no longer matter. Without the key, both positions appear different.
Map equivalent fields explicitly
A key can have different headers in each system. Map order_id to Order Number, then map the values you actually need to compare. The same word does not always mean the same thing: a field named “status” could describe payment in one file and shipment in the other.
In Spreadsheet Comparison, load both files, review the suggested column mappings and select the mapped key fields. Suggestions use header patterns and data overlap. They are a starting point for your review, not a guarantee that two business concepts match.
Read each result in context
A changed result means records matched by the selected key but compared values differ. A record found only in one file has no matching counterpart under those choices. Before calling that a deletion, check the export dates, filters and scope. A Tuesday export restricted to active orders can legitimately omit a Monday order.
Filter the result to changed or one-file-only records, search a known key and open the full row preview. Inspect one expected change and one expected match before reviewing the whole export. Export the report when the key and mappings answer the intended question.
Check the three common traps
Duplicate keys: repeated IDs mean identity is incomplete or the source has duplicates. Read the duplicate-key guide before interpreting row pairings. Blank keys: a missing identifier is not a shared identity. Changed formatting: “00104” and “104” may represent different identifiers, even if a spreadsheet displays them similarly.
Do not repair a CSV by splitting every line at commas. Quoted fields can contain commas and line breaks; embedded double quotes use escaping. RFC 4180 describes these CSV conventions. Use a CSV-aware reader and check the imported values.
A practical review checklist
- Confirm both exports cover the intended dates and record types.
- Choose a stable key and inspect duplicate or blank-key warnings.
- Review field mappings, including fields intentionally excluded.
- Spot-check expected unchanged, changed and missing records.
- Save the report alongside the two original exports.
Spreadsheet Comparison runs the comparison on your Mac. The free trial allows up to 5,000 data rows per file and ten successful comparisons. It supports composite keys when one field is not enough.