Why Excel ruins codes in CSV files
CSV is a text format: it has no types. Whatever program opens the file invents them. Excel invents them aggressively, and that is exactly why codes break in it.
Three ways to lose data with one double-click
Leading zeros
The value 00123 looks like a number, so Excel turns it into the number 123. The zeros are not hidden — they are gone. Save it back to CSV and 123 is what goes into the file. ZIP codes such as 02134, SKUs, barcodes, and account and employee numbers are all affected.
Scientific notation
The identifier 7812345678901234 becomes 7.81235E+15. It looks like a display problem, but it is not.
Lost precision
Excel stores numbers in double precision, which is 15–16 significant digits. A sixteen-digit identifier is on the edge, a seventeen-digit one is past it: the trailing digits are replaced with zeros for good. Widening the column will not help — the data is already lost.
Text that merely resembles a date suffers too: Excel will happily turn a product code such as 1-2 or MAR1 into a date.
Why “change the cell format” does not save you
A cell format changes how a value is displayed, not what it contains. By the time you change the format, the conversion has already happened while the file was being parsed. The only way out is not to let Excel decide for you at the moment of opening.
Workable approaches
- The text import wizard. Do not double-click the file; import it: Data → From Text/CSV, and for every column of codes pick the Text type. Tedious, but it works.
- Quotes do not help. A common misconception: Excel still recognizes
"00123"as a number. Quotes in a CSV only mark the boundaries of a field. - An apostrophe prefix. If you generate the CSV yourself, Excel will show
'00123as text — but the apostrophe becomes part of the data for every other program. - Do not open it in Excel at all. If the task is to look, filter, reconcile or export a subset, an intermediate Excel is simply not needed.
How it is done here
The type of a column is decided from a sample of values, with three rules that Excel ignores:
- a value with a leading zero makes the whole column text;
- a column where every value has the same length of eleven or more digits is treated as a code, not a number — that is how account numbers and national IDs behave;
- values longer than 15 digits are always text — beyond that boundary double-precision arithmetic lies.
When exported to XLSX, such a column stays text, so there is nothing left for Excel to “fix”.
FAQ
Can the lost zeros be brought back?
If you know the length of the code, yes: pad it with zeros on the left. If precision was lost on a long number, no — those digits cannot be restored.
Does Google Sheets behave the same way?
Similarly: leading zeros are dropped and long numbers are rounded. The details differ, the substance is the same.
Then why does everyone use CSV?
Because anything can read it. The problem is not the format but the fact that types are not written down, so every reader guesses them on its own.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.