Excel Is Changing Your CSV Data: Zeros, Long Numbers, and Dates

The short answer

A CSV file stores text. It has no way to say “this column is text, not a number”, so a spreadsheet guesses — and a value that looks like a number or a date is read as one. 00123 becomes 123 because numbers have no leading zeros, and SEPT2 becomes a date because it looks like one.

In most cases the file is untouched and only the reading of it changed. That distinction matters: if the file is fine, re-saving it from the spreadsheet is what actually destroys the data.

The file says one thing, the screen says another

Pasting this into the CSV Converter returns every value exactly as stored — measured, not assumed:

zip,id,code
00123,1234567890123456789,SEPT2

[
  {
    "zip": "00123",
    "id": "1234567890123456789",
    "code": "SEPT2"
  }
]

Nothing has been lost. The zeros are in the file, the nineteen-digit ID is intact, and SEPT2 is still a product code. A spreadsheet opening the same bytes commonly shows something else:

To be precise about what was measured: the middle column comes from running each value through our converter. The third column is the widely documented spreadsheet behaviour and was not measured here — we have no spreadsheet under test, and the exact result varies by application, version, and regional settings.

Working out which one changed

1. 1. Open the file as text, not as a spreadsheetA text editor, or pasting it into a browser-side converter, shows the characters that are actually stored. That is the only way to see the file rather than one program’s reading of it.
2. 2. If the value is intact in the text, the file is fineNothing needs repairing. What needs changing is how the file is opened, and re-saving it from the spreadsheet at this point would write the damaged values back.
3. 3. Import rather than double-clickExcel’s Data → Get Data → From Text/CSV and Google Sheets’ File → Import both let you set a column to Text before anything is converted. Double-clicking skips that step entirely.
4. 4. If the value is already wrong in the text, fix the exportSomething upstream wrote it that way — often a spreadsheet that had already reinterpreted it before saving. Re-exporting from the original source is the durable fix.
5. 5. Treat spreadsheet formatting as display-onlyFormatting a column as Text after the value has been converted does not bring back digits that were already dropped, and the setting does not travel with a re-saved CSV.

The trap worth naming

Open a CSV by double-clicking, let the spreadsheet convert the values, then save it back as CSV — and the converted values are now what the file contains. The damage was reversible right up until that save.

This is also why formatting a column as Text after the fact does not help: it changes how the value is displayed, not the digits that were already dropped, and the setting is not part of a CSV file so it does not survive a round trip.

Check what the file actually contains

Paste the raw file into the CSV Converter and every field comes back as the text it is stored as — no value is converted to a number or a date. If the zeros are there, the file is intact and the problem is in the opening.

It cannot fix your spreadsheet. It does not know your regional settings, cannot change how an application imports a file, and cannot recover digits that were already dropped and saved over. It tells you what is in the text. Conversion runs in your browser, so a file with real data is not uploaded anywhere.

A different symptom

If the values are correct but every row lands in a single column, that is a delimiter problem rather than a type problem — CSV opens in one column covers semicolons, commas, and regional list separators.

Measured with

• Papa Parse — 5.5.4
• DataToolsHQ CSV Converter — Papa Parse 5.5.4

Parsed values on this page come from the tool. Spreadsheet display behaviour is described from documentation and common practice, not measured here.