Blog/Data

How to compare two CSV files and find missing or changed rows

Two files that should match rarely do: the orders export has 1,204 rows, the payments export has 1,197, and someone has to find the seven. Sorting both and scrolling side by side breaks as soon as one row is out of place. Comparing by a key column is faster and far more reliable.

Data Reconciliation Studio screenshot

Pick the column that identifies a row

Every comparison needs a key: a value that means the same record in both files, such as an order number, invoice number, email address or SKU. Without one, the only option is to compare row positions, and a single extra row throws everything off.

If no single column is unique, combine two, for example customer email plus date. Check the key for duplicates first: if the same order number appears twice in one file, decide whether that is a real duplicate or two separate lines.

Clean the key before you compare

Most false mismatches come from formatting, not from real differences. INV-0042 and inv-0042 are the same invoice, and so are 00123 and 123 when a spreadsheet drops leading zeros.

Trim extra spaces, use one letter case, and make sure numbers stored as text and numbers stored as numbers are treated the same.

  • Trim spaces at the start and end
  • Use one letter case for text keys
  • Keep leading zeros consistent
  • Use one date format

The four results you are looking for

A good comparison sorts every row into one of four groups, so you can act on each group separately.

  • Missing: in file A but not in file B
  • Unmatched: in file B but not in file A
  • Duplicated: the key appears more than once in a file
  • Changed: the key matches, but another value differs, such as the amount or status

Doing it in Excel or Google Sheets

For a quick check, add a column in each file with a lookup (XLOOKUP, VLOOKUP or MATCH) against the other file's key. Rows that return an error are missing from the other side. A COUNTIF on the key column shows duplicates.

This works for a few hundred rows and one comparison. It gets slow and fragile with large files, several columns to compare, or a check you repeat every month.

Compare amounts with a tolerance

When the files come from different systems, amounts can differ by a cent because of rounding. Decide on a tolerance, such as one cent, so rounding does not flood your list of changes and the real differences stand out.

Do it in your browser

Data Reconciliation Studio compares two CSV or Excel files by the key you choose and lists missing, unmatched, duplicated and changed records, with the changed values side by side. Files are processed in your browser, so nothing is uploaded.

Your first 3 comparisons are free, then it costs $29 once.

On this page
Pick the column that identifies a rowClean the key before you compareThe four results you are looking forDoing it in Excel or Google SheetsCompare amounts with a toleranceDo it in your browser
Free to try

Do this faster with Data Reconciliation Studio.

No signup, nothing to connect. Start with the free results and decide later.