skip to content

In Excel, why do IDs like 00123 or SEP-1 change when you open a CSV?

level: middleimportance: should knowfreq 45%

answer

  1. the file format carries no types
  2. Excel has to guess something
  3. the zeros were only characters
  4. saving writes back what is displayed
  5. big integers stop being exact somewhere

basics

~20 s

A CSV carries no types, so Excel guesses per value: 00123 parses as the number 123, SEP-1 as a date, and long digit strings keep only 15 significant digits. Import through Get Data with the column typed as Text instead.

solid answer

~50 s

CSV is plain text with **no type information**, so on open Excel infers a type for every value. Three inferences destroy data. Leading zeros vanish because `00123` is parsed as the number 123 and the zeros were only characters. Anything shaped like a date is converted — `SEP-1` becomes 1 September of the current year, and it will be **written back in the new display format** if you save. And long digit strings are stored as floating-point numbers, which keep only about 15 significant digits, so an 18-digit account number comes back with zeros on the end. None of this raises an error. The fix is to stop double-clicking CSVs: use `Data > Get Data > From Text/CSV` and set the affected columns to **Text** before loading. Formatting the column as Text *after* the fact does not restore anything — the original characters are already gone.

code

text · 7 lines
text
-- customers.csv on disk
id,code,account
00123,SEP-1,1234567890123456789

-- after double-clicking in Excel and saving back to CSV
id,code,account
123,1-Sep,1234567890123450000

go deeper

for a junior

Know that CSV has no types and Excel guesses, and that leading zeros, date-shaped codes and very long numbers are the three victims. Be able to say that you import through Get Data and set the column to Text.

for a middle

Explain that the loss happens at parse time, so nothing done after opening recovers it, and that saving writes the display format back into the file. Name the roughly 15-significant-digit limit on stored numbers.

for a senior

Show you design the boundary: typed import queries, validation on ingest, and no round-tripping of files through a spreadsheet. Be ready to describe a real incident where a silent coercion broke a downstream join.

for a principal

Treat recurring coercion damage as evidence the interchange format is wrong. Decide whether to move partner exchanges onto typed formats or a managed pipeline, and weigh that cost against the ubiquity that makes CSV and Excel the default in the first place.

## Why this happens at all A CSV is characters and separators. It has no schema, no declared types, no metadata. When Excel opens one it must decide, value by value, whether each token is a number, a date, or text — and it decides in favour of number or date whenever the token plausibly looks like one. That heuristic is convenient for a sales export and destructive for anything with identifiers in it. The damage is *silent*. There is no warning row, no error cell, no log. The file opens, looks fine, and the values are already different. ## Leading zeros `00123` is a string of five characters. Parsed as a number it is 123; the leading zeros were never part of the numeric value, so they are not "hidden", they are gone. Formatting the column as Text afterwards converts the number 123 to the string "123" — the zeros do not come back. Only a custom number format like `00000` can *display* them again, and that is a display mask, not the original data: exporting still writes 123. This matters for zip codes, cost centres, GL accounts, SKUs, phone numbers and any zero-padded key. The failure mode downstream is a join that misses. ## Date coercion Excel is aggressive about date-shaped tokens. `SEP-1`, `1-2`, `3/4`, `2-10` and similar all convert. The converted value is a serial number displayed with a date format, so `SEP-1` becomes 1 September of the current year — note that a year has been invented that was never in the source. The round-trip is the ugly part: if you save the file back as CSV, Excel writes the **display format**, so the file on disk now says `1-Sep` where it once said `SEP-1`. The corruption is now permanent and has been handed back to whoever produced the file. This class of coercion is well known enough that gene-symbol naming conventions in genetics were revised specifically because symbols were being turned into dates by spreadsheet software. ## Precision and scientific notation Excel stores numbers as IEEE 754 double-precision floats and retains **15 significant digits**; digits beyond the fifteenth become zeros. A 16-, 18- or 20-digit account number, credit-card-style identifier or event ID therefore loses its tail: ``` 1234567890123456789 -> 1234567890123450000 ``` Long numbers also display in scientific notation, which people try to fix by widening the column or reformatting — neither of which recovers the lost digits, because the loss happened at parse time, not at display time. ## Doing it safely 1. **Never double-click the CSV.** Open Excel first, then `Data > Get Data > From Text/CSV`, and in the preview set every identifier column to **Text** before loading. This step is saved with the query, so the next refresh types it the same way. 2. **Type at the boundary, not after.** The decision must happen before the value is parsed. Nothing you do afterwards recovers information. 3. **Do not round-trip data through Excel.** If a file is going back to a system, Excel is the wrong intermediary — it rewrites display formats into the file. 4. **Prefer a typed format.** Parquet or a database extract carries types with the data, removing the guess entirely. 5. Recent Microsoft 365 builds add options to suppress some automatic conversions; treat that as a helpful backstop rather than the control, and verify what your build offers. The per-cell apostrophe prefix (`'00123`) works but is a manual, invisible fix that is lost on the next export and cannot be applied to a 90,000-row file. ## How to talk about it A strong answer names the three coercions, points out that all three are silent, and states the rule: **types must be declared at the import boundary**. Then the honest structural conclusion — if identifiers keep passing through a spreadsheet, the exchange format is wrong, and the fix is a typed handoff rather than a rule asking analysts to be careful.

  • You formatted the column as Text after opening the file — why are the leading zeros still missing?
    Because the loss happened at parse time. `00123` was converted to the number 123 the moment the file opened, and the zeros were characters that the numeric value never contained. Formatting afterwards converts 123 into the string "123". A custom format like `00000` can display padding again, but exports still write 123 — the original characters are unrecoverable.
  • Why is prefixing values with an apostrophe not a real solution?
    It is a manual, per-cell fix invisible to the file's producer and lost on the next export or refresh. It cannot be applied across 90,000 rows, and it depends on someone remembering every time. Typing the column at import is one setting saved with the query and applied to every future refresh, so it does not rely on anyone's discipline.
  • Excel must stay in a data exchange with a partner — how do you make it safe?
    Treat Excel as a display surface, not a transport. Import through a typed query, never save back over the source file, and validate on ingest: check row counts, check that key columns still match their expected pattern or length, and reject files whose keys have changed shape. Better still, move the exchange to a format that carries types, such as Parquet or a database extract.

saying these in an interview costs you the question

  • Says formatting the column as Text afterwards restores leading zeros
  • Claims Excel stores arbitrarily large integers exactly
  • Blames column width for scientific notation and lost digits
  • Treats it as analyst carelessness rather than a typing problem
  • Assumes a CSV file carries data types with it

context

resolving links…