Duplicate keys with CSV Reconcile
Two order exports where order 5002 has two lines in the first file and order 5003 has two identical lines in the second. The Ambiguous tab lists both, with line numbers in each file, instead of pairing them up at random.
- Load the two files and select the key column.
- Press Compare and open the Ambiguous tab.
- Read the line numbers: they point at the rows in each file, counting the header as line 1.
- Fix it with a second key column such as a line number, or turn on matching duplicates by position under Comparison rules.
- Compare again and check that the Ambiguous count is zero.
What to know
If two rows share the key 5002, there is no way to know which one in the other file is the same record. Pairing them in file order is a guess, and a wrong guess shows up as phantom changes or, worse, hides a real one. So the tool refuses to guess: every key value that occurs more than once in either file is listed on its own tab and left out of the changed, added and removed results.
In the sample, order 5002 has a Wallet line and a Scarf line in the first file and a single Wallet line in the second. Order 5003 has one line in the first and two identical lines in the second. Both are ambiguous, 2 keys covering 3 rows in each file. Orders 5004 and 5005 are plainly removed and added, and 5001 is unchanged. An order table normally has several lines per order, so the order number is the wrong key on its own; order number plus item is better, and order number plus line number better still.
Two ways to resolve duplicates exist. Add a column to the key until each row is unique. Or, when both files list the duplicates in the same order and that order is meaningful, enable matching duplicate keys by position: the first row with a key in one file is compared with the first in the other, the second with the second, and surplus rows count as added or removed. That is an explicit opt-in, because it is only correct when the order is reliable. Rows with an empty key are not matched at all; a note gives their count.
Updated · Cosmovex
Questions
Why aren't duplicates just compared in order?
Because the order is only a guess about which row belongs to which. You can opt in under Comparison rules when you know the order is reliable; otherwise they stay ambiguous.
What does a line number in the result refer to?
The line of the data in the file, where the header is line 1 and the first record is line 2. In a file with quoted line breaks, this is the record number plus one.
Is a key that is duplicated in only one file also ambiguous?
Yes. If 5003 appears twice in the second file and once in the first, it is ambiguous, because it is unclear which of the two corresponds to the one.
Can I find duplicates inside a single file?
Load the same file in both boxes and look at the Ambiguous tab: it lists every key that repeats. A dedicated duplicate finder will be quicker for that, but this works.
What happens to rows with an empty key?
They are left out and the result says how many there were in each file, so they never silently match each other.
The free plan covers everything on this page. CSV Reconcile Pro ($29, paid once) is described on the CSV Reconcile page.
