Problem
Leading Zeros Disappeared From Your CSV — Why, and How to Stop It
The postcodes used to start with a zero. The account numbers were 18 digits. Now one is short a
character and the other says 1.23457E+17.
The short answer
The column is being read as a number when it is really text. Fix it at read time, not afterwards. In DataTray, open Data Converter, and set the type for those columns to text before the run — postcodes, account numbers, product codes and phone numbers all come out exactly as written. Never convert a file by opening it in Excel and saving it again; that is where this happens.
Why it happens
A CSV has no types. Every value in it is text, and any program reading one has to decide what each column means.
Excel decides by looking. A column of digits is a number, and numerically 02134 and 2134 are
identical — the leading zero carries no value, so it is dropped. That is correct arithmetic and
completely wrong for a postcode, because a postcode is not a quantity. Nobody adds two postcodes
together.
The same logic turns 1-2 into a date and +44 20 into something unrecognisable.
The one that cannot be undone
Leading zeros are recoverable if you know the expected width — pad back to five characters and the postcodes are fine.
Long identifiers are not. Excel holds numbers as 64-bit floating point with about 15 significant digits. An 18-digit order number does not fit, so it is rounded, and the final digits are destroyed rather than hidden. Formatting the cell as text afterwards shows you the rounded value. The information is gone from the file the moment it was saved.
This is why the advice is always to fix it at read time. There is no reliable repair after the fact.
Doing it properly
- Open the CSV in Data Converter. It shows the detected delimiter, encoding and a per-column type.
- Set the identifier-shaped columns to text. DataTray flags likely candidates — a column of fixed-length numeric strings is exactly the leading-zero-loss pattern it looks for.
- Convert to
.xlsx. The workbook stores the type, so nothing re-guesses when it is opened.
That last point is the real win of writing a workbook rather than another CSV: an .xlsx records
that the column is text, so the person you send it to cannot lose the zeros by opening it.
If you have already lost them
Clean & Anonymize can pad a column back to a fixed width, which restores postcodes and any other identifier whose length you know. Run it, check a sample against the source system, and then fix the export that caused it — otherwise you will be doing this again next month.
For truncated long numbers, go back to the source. There is no honest repair.
Do not fix this with a formula
The common advice is to prefix values with an apostrophe, or wrap them in ="02134". Both work
inside that one workbook and both contaminate the data: the apostrophe is invisible in Excel and
very visible to everything else, and the formula is not a value at all. Anything downstream then
has to strip them.
Setting the type is the fix. The apostrophe is a workaround for not having set the type.
Every other free way to do this asks you to upload the file first. An export full of account numbers is a poor candidate for that.
Do this in one pass with — Data Converter
CSV, TSV, Excel, Parquet, JSON, JSONL and XML, in whichever direction you need — including the ones Windows has no answer for. Twelve files into one, or one file split into twelve, because converting, combining and splitting are the same job at different cardinalities. Drop a folder in and it works out which you meant. Learn more about the Data Converter.
Windows 10/11 (x64) · Free · No account