Match CSV rows by an ID column

When row order is the problem

Suppose one people export lists Alice, Ben, and Chloë, and the next export lists them in a different order. Comparing row 2 with row 2 reports several changes even though the records themselves may be unchanged.

Key matching uses a stable identifier to find the corresponding row. Use something like a customer ID or product code, not a row number that is regenerated each time.

Set up key matching

1. Choose the two CSV or TSV files.
2. Open Comparison options → CSV and TSV options.
3. Check First row contains column names if the first row is a header.
4. Set Match rows by to Key column.
5. Enter the header, such as Record ID, in Key column. You can also enter a one-based column number, such as 1.
6. Click Compare.

Matched records are checked for changed values; records present on only one side are still reported. Row order by itself is no longer a difference.

What makes a good key

• Each row needs a nonempty key, and keys must be unique within each file.
• Key values are exact and case-sensitive. AB12 and ab12 are different keys.
• Header names are looked up without case differences. Duplicate header names need a column number.
• The key column must occupy the same position in both files. This setting aligns rows, not columns.
• With headers off, use a column number.

A missing or duplicate key produces an error instead of the app guessing which record you meant. Correct the source export or choose a genuinely unique key.

Try the reordered people example

Download and extract the CSV practice files. Compare the two people exports with Row position selected: twelve field differences. Now select Key column and enter Record ID. Compare again: Same, with zero differences.

The records match; their order changed. If you edit an amount in one of the practice files and rerun, that changed value should still be reported against the matching ID.