How to compare two large CSV files with different columns
To compare two large CSV files with different columns, do not diff them line by line. Map each source column to its target column, choose a key (often a composite key) to match rows, normalise values (trim, case, dates, decimals), set tolerances for numbers, then run a keyed comparison that streams or indexes the data rather than loading it into a spreadsheet. The output should be a break report: rows only in A, rows only in B, duplicate keys, and changed values.
Step 1: Map the columns
Write the mapping as a table: trade_id → id, amount → total_amount, Qty → Units. Columns that exist on one side only should be listed as out of scope, deliberately. Column order then does not matter.
Step 2: Pick the key
Use the business key, not the row number. If no single column is unique, combine columns, for example account + ISIN + trade_date. Count duplicates per key on each file before comparing.
Step 3: Normalise before you compare
Trim spaces, standardise case, parse dates into one format, and make number formats consistent (decimal separators, thousands separators, negatives in brackets). Most false breaks come from here. See normalise before you compare.
Step 4: Set tolerances
Decide where small differences are acceptable, for example 0.01 on amounts, and exact elsewhere. Record them with the mapping.
Step 5: Run a keyed comparison that scales
| Approach | Good for | Watch out for |
|---|---|---|
| Excel / Power Query | Small files, quick checks | 1,048,576-row limit; VLOOKUP returns first match only |
| DuckDB or Python (pandas, polars) | Engineers comfortable writing SQL or code | You own the mapping, tests and report format |
| csv-stream-diff and similar open-source tools | Streaming CSV comparisons | Check key, mapping and tolerance support for your case |
| Reconlify | CSV/TSV with keys, column mapping and tolerance via CLI | CSV and TSV only today, per its FAQ |
| ShipDataFast | Large files in CSV and other formats under one set of rules | Scoped reconciliation, not a desktop diff app |
Step 6: Read the break report
Group results by type and show both values for every changed field. Start with missing and duplicate keys; they usually explain many value breaks. Re-run with the same rules after fixes.
When one side is not CSV
If one file is XLSX, PDF, XML or JSON, convert carefully or use a tool that reads both. ShipDataFast compares PDF, XLSX, CSV, XML and JSON with the same mapping, key and tolerances, on multi-GB files.
Frequently asked questions
How do I compare two CSV files with different column names?
Create a column mapping from source to target names, then compare on a key rather than by line. Tools such as Reconlify, DuckDB and ShipDataFast support this.
Can Excel compare two large CSV files?
Only up to 1,048,576 rows per sheet, and lookups are slow and return the first match only. For larger files, use a keyed comparison tool or a database such as DuckDB.
What if the columns are in a different order?
With a mapping and a keyed comparison, column order does not matter.
What should the output look like?
A break report: rows only in file A, rows only in file B, duplicate keys and changed values, each with the key and both values.
Want to check two real files?
ShipDataFast's Recon Check gives you a first break report at no charge. Anonymised or sample files are fine; agree a safe transfer method before sharing sensitive data.