CSV reconciliation checklist for small teams.

1. Define the matching key

Pick the field that identifies the same record in both files: customer ID, order ID, SKU, invoice number, or transaction ID. If there is no stable key, create one from a small set of fields and document the tradeoff.

2. Normalize before comparing

Trim whitespace, standardize casing, parse dates into one format, and compare numbers as numbers. Many false mismatches come from formatting differences rather than actual data differences.

3. Report four failure types separately

• Rows missing from the left export.
• Rows missing from the right export.
• Duplicate keys that make comparison ambiguous.
• Field mismatches for records that exist in both files.

4. Keep the output simple

A useful first version usually needs only summary.json, mismatches.csv, missing_left.csv, missing_right.csv, and duplicates.csv. Add Excel output only when someone needs to review the report manually.

Copy-paste brief

Goal:
Compare two CSV exports and generate a reconciliation report.

Files:
- source_a.csv
- source_b.csv

Matching key:
- customer_id

Fields to compare:
- email
- amount
- status

Output:
- summary.json
- mismatches.csv
- missing_left.csv
- missing_right.csv
- duplicates.csv

Rules:
- Trim whitespace.
- Compare email case-insensitively.
- Compare amount as a decimal number.
- Treat blank status as missing.