Why Excel Mangles Your CSV Numbers (and How to Stop It)
A zip code of 00501 opens as 501. A 16-digit account number turns into 1.23457E+15. A product code like 2-3 silently becomes the date March 2nd. None of these are problems with the CSV file itself — every one of them is Excel guessing at what a plain-text value "really" means the moment you double-click to open it, and getting the guess wrong for data that was never meant to be interpreted that way in the first place.
Why this happens: CSV has no type information
A CSV file is just text — every value is a string, with nothing in the format itself to mark a field as "this one's actually a number" or "this one's actually text that happens to look numeric." Excel has to guess based on what a value looks like, and it guesses aggressively: anything that resembles a number gets treated as one, anything that resembles a date gets treated as one, and both transformations are lossy the moment the original value wasn't actually meant that way.
Leading zeros disappear
ZIP codes, product codes, and any zero-padded ID get their leading zeros silently stripped, since Excel reads 00501 as the number 501, and a number has no concept of a "leading" zero to preserve. The data isn't recoverable from the display — once Excel has reinterpreted the cell as numeric, the original text form is gone unless you re-import from the source file.
Long numbers turn into scientific notation
Account numbers, tracking numbers, and other long digit strings (typically 12+ digits) get converted to scientific notation like 1.23457E+15 once they exceed Excel's default numeric display precision — and depending on how the cell is later formatted or saved, the original exact digits can be permanently lost, not just displayed differently.
Text that looks like a date gets rewritten
A value like 2-3, Mar-2, or a version number like 1-2 can get silently reinterpreted as a calendar date, since Excel's date-detection heuristics are notoriously eager. This is a well-known enough problem that it's caused real, documented issues in fields like genetics, where gene symbols such as SEPT2 or MARCH1 get auto-converted to dates in spreadsheet software — significant enough that some naming conventions were eventually revised partly to work around it.
The actual fix: don't let Excel guess
Opening a CSV by double-clicking it hands every column over to Excel's automatic type detection with no way to intervene per-column. The fix is importing through Data → From Text/CSV instead (technically named "From Text/CSV" or "Get Data" depending on Excel's version), which opens an import wizard that lets you set each column's data type explicitly — critically, setting the type to Text for any column that should be preserved exactly as-is, before Excel ever gets the chance to reinterpret it as a number or date.
Why this matters more for generated or re-shared files
If you're generating a CSV programmatically and it's likely to be opened in Excel by someone else, this is worth being aware of on both ends: as the generator, there's no way to force Excel's import behavior from inside the file itself, and as the recipient, defaulting to the plain double-click open is exactly what triggers the mangling described above. The safest habit is treating the "From Text/CSV" import path as the default way to open any CSV with IDs, codes, or zero-padded values in it, rather than trusting a double-click.
Convert without the risk
FreeToolDev's CSV ↔ TSV converter and CSV to JSON converter both treat every value as plain text by default rather than guessing at numeric types — the mangling described here is specifically an Excel behavior on open, not something that happens during conversion itself.