IDs missing zeros with CSV Reconcile
One export has employee numbers 00417 to 00420, the other was saved through a spreadsheet and shows 417 to 421. With leading zeros ignored in the key, 417, 418 and 419 match, 420 is removed, 421 is added and employee 418 moved from Plant 2 to Plant 3.
- Load both files. The page shows the row counts, so check them against what you expect.
- Select the ID column as the key.
- Under Comparison rules turn on Ignore leading zeros in the key.
- Press Compare and read the Changed, Added and Removed tabs.
- If many rows are added and removed in pairs, look at the key values on both tabs for another formatting difference.
What to know
Opening a CSV in a spreadsheet and saving it again is the usual way leading zeros disappear. The application sees 00417 as a number, stores 417, and writes 417 back. The data looks the same to a person, but the key no longer matches, so a naive comparison reports every ID as removed and a second set as added. With 400 employees that is 800 spurious differences.
With the rule on, a key made only of digits is compared without its leading zeros, so 00417 and 417 are the same record, while 00420 and 421 are not. The zeros are removed for matching only; changed rows show the key as written in the first file. In the sample, Jane Roe (418) moved from Plant 2 to Plant 3, 00420 exists only in the first file and 421 only in the second. The rule affects keys only. Other columns follow the usual rules, so a zip code 02134 in one file and 2134 in the other is compared as numbers if numbers are on, and is equal.
Mind what it can do wrong. If your system has genuinely different IDs 0123 and 123, the rule merges them and they become ambiguous, which the tool reports rather than hides. The better fix is upstream: import ID columns as text, or avoid re-saving through a spreadsheet at all. Until then, this rule gets you a usable comparison. The employees in the sample are invented.
Updated · Cosmovex
Questions
Does this change my files?
No. The zeros are only ignored while matching rows. Your files and the downloaded results show the original values.
Does the rule apply to other columns?
It applies to the key only. For other columns, numeric values such as 02134 and 2134 are equal when numbers are compared as numbers; with that rule off they are different text.
What about keys that contain letters?
Only keys made entirely of digits are affected. A key such as A-0042 is compared as written, apart from the usual trimming and optional case folding.
What if 0123 and 123 are different records?
With the rule on they match the same key and become ambiguous if both files contain them. Switch the rule off in that case.
Why did the zeros disappear in the first place?
Spreadsheets treat a column of digits as numbers and store 00417 as 417. Importing the column as text, or quoting the values, avoids it.
The free plan covers everything on this page. CSV Reconcile Pro ($29, paid once) is described on the CSV Reconcile page.
