List ReconcileCompare CSV files without losing leading zeros in IDs
List Reconcile ·
To compare CSV files without losing leading zeros, read the ID column as text from the original export. An identifier such as 000042 is a string with six characters. If a spreadsheet has already converted it to the number 42 and saved that change, a comparison tool cannot discover how many zeros used to be there.
An Excel user working with employee IDs reported this exact concern: opening CSV files removed leading zeros, with possible consequences for duplicate employee records in their database. The discussion concerned import behavior and Excel settings. It does not establish that every CSV application converts identifiers. Start by finding the first step that turns the text into a number.
CSV contains characters, not column types
A CSV file can contain a field written as 000042. It cannot attach an Excel cell type saying “always treat this as text.” The application opening the file chooses how to interpret those characters. Even a quoted CSV field can be interpreted as a number by a spreadsheet; CSV quotes describe field boundaries and escaping, rather than a universal text-type instruction.
Microsoft’s guidance on leading zeros and large numbers explains that Excel can remove leading zeros and convert long numerical text. It describes importing columns as Text and, in supported versions, controlling automatic conversions. The exact controls depend on the Excel version. Inspect the imported values instead of assuming a previously chosen setting still applies.
Locate the first changed copy
Keep the original downloaded CSV untouched. Open it in a plain text editor and search for one affected ID. If the file contains 000042 but Excel shows 42, the conversion happened when the spreadsheet interpreted the file. If the original download already contains 42, investigate the exporting system or its configuration.
A custom cell format can display 42 as 000042. That display is useful for a known fixed-width convention, but it does not prove the original identifier had that width. If the source allows both 042 and 000042, adding zeros to every number can merge distinct IDs. Ask for the source’s identifier specification before attempting a repair.
The same caution applies to scientific notation. A displayed 1.23E+17 might be a presentation choice, or it might follow a conversion that already discarded digits. Check the actual value against the original text file. Changing the display back is not a reliable way to recover missing information.
Try a deliberately awkward set of identifiers
Create a small CSV for A:
employee_id,team
000042,North
42,South
12345678901234567890,West
000042,NorthFor B, use:
employee_id,team
000042,North
12345678901234567890,WestOpen these files in Compare two lists, choose CSV, keep Header row checked, and select employee_id on both sides. Leave Ignore outer whitespace and Ignore case unchecked. The result should contain three distinct IDs. 000042 has counts two and one; 42 has counts one and zero; the twenty-digit ID has counts one and one.
That is a useful acceptance sample for any tool handling identifiers. It tests preservation, a repeated ID, and a long string at once. A result with only two distinct IDs would suggest that 42 and 000042 were merged somewhere. A changed final digit in the long value would require a separate investigation.
You can load this fictional employee-ID pair with the columns selected, or download the original A CSV and B CSV to follow the same steps yourself.
Choose an output that carries text types
List Reconcile’s Excel download writes comparison keys and original field values as text cells. The Summary sheet contains counts; Source A and Source B contain the associated input records. For a spreadsheet handoff, this avoids relying on the recipient’s CSV import defaults. The tool generates this workbook locally when you choose Excel and Download.
The JSON download also keeps identifiers as strings and includes source line references. It is useful for inspecting exact characters or handing the result to another program. Keep string values as strings when processing that JSON; converting every digit-only field to a number would reintroduce the same problem downstream.
A CSV report is convenient when another system specifically requires CSV, but opening that report by double-clicking can trigger spreadsheet conversions again. List Reconcile cannot control the receiving application’s import choices. Its CSV report also prefixes potentially executable formula-like strings for safer spreadsheet handling, so choose JSON or the text-cell workbook when exact source values matter.
A careful Excel import workflow
If you prefer Excel, use its text/CSV import path and explicitly assign Text to the identifier column before loading or accepting a numeric conversion step. Check a zero-prefixed ID and a long ID in the resulting table. Save the working file as an Excel workbook when you need the column types to travel with it.
Do not rely on formatting the column as Text after values have already changed. At that point you may only be marking the altered value as text. Start again with the untouched CSV if it still contains the original characters. Our Excel formula guide assumes this preparation has already happened.
Check the receiving workflow too
Before sending a large report, send the small sample above through the same export and import steps. Ask the recipient to confirm all three identifiers character for character, including the long value’s final digits. Confirm that two occurrences of 000042 are still two records rather than one deduplicated entry.
Keep this sample with the workflow instructions so a changed spreadsheet version or import configuration can be tested again. If the remaining differences concern counts rather than damaged IDs, move to comparing lists with duplicates. Preserving characters gets the inputs ready; deciding whether those repeated records belong there is the next task.