You open a CSV of customers in Excel, save it, and half the zip codes have lost their leading zero, an ID has become 1.23457E+17, and a gene name is now a date. The file did not change on its own. The spreadsheet reinterpreted the data and, when you saved, wrote its interpretation back.
The root cause: CSV has no types
A CSV stores only text. There is no way to say "this column is a postcode, keep it as text". When a spreadsheet opens the file by double-clicking, it guesses each cell's type from its appearance. Anything that looks like a number becomes a number, and anything date-like becomes a date. Numbers do not have leading zeros, so 00501 becomes 501.
The common casualties
Leading zeros. US zip codes, phone numbers with a leading zero, product codes and account numbers lose their zeros once read as numbers.
Long numbers. Spreadsheets store numbers as double-precision floating point values, which keep about 15 significant digits. Excel displays long numbers in scientific notation, and anything beyond 15 digits is rounded to zeros. Credit-card-length numbers and many database identifiers are damaged this way.
Dates. A value like 3/4/2025 is interpreted using the computer's regional settings, so it is March 4 on one machine and April 3 on another. Codes such as 1-2 or MAR1 can be turned into dates. A well-documented case: researchers found that gene symbols such as SEPT2 and MARCH1 were being converted to dates in published datasets, which led the HUGO Gene Nomenclature Committee to rename the genes.
Formulas. A cell that starts with =, +, - or @ may be treated as a formula. That is not just an annoyance: it is the basis of CSV injection attacks, where a malicious value executes when opened. Treat exported user-supplied data with care.
Scientific notation lookalikes. A value such as 12E3 is read as 12,000.
How to import safely
Use the import wizard, not double-click. In Excel, use Data, then From Text/CSV, and set the problematic columns to Text before loading. In LibreOffice, the Text Import dialog lets you choose "Text" as the column type. In Google Sheets, uncheck the option to convert text to numbers, dates and formulas when importing.
Pre-format the destination column as Text before pasting.
Quote and prefix in the file if you control how it is produced. A leading apostrophe or an Excel-specific ="00501" pattern works in Excel but pollutes the data for every other consumer, so prefer fixing the import.
Don't save over the original
Opening a CSV in a spreadsheet and clicking Save writes the interpreted values back. If the original is the only copy, the data is permanently altered. Save the spreadsheet as a workbook, or export a copy, and keep the original.
How to check the real contents
Open the file in a plain-text view to see what is truly stored. Docento's Text & Markdown Editor opens CSV files without interpreting the values, so 00501 stays 00501, and its syntax bar confirms each row has the same number of fields. You can fix a value by hand in the text and download the file, with no spreadsheet reinterpretation in between.
When data must remain exact
For identifiers, use a format that carries types: JSON stores numbers and strings distinctly (see what is a JSON file), and database or spreadsheet native formats can carry column types. For CSV, document which columns are text and agree on how dates are written. ISO 8601 dates (2025-04-03) are unambiguous and avoid the day-month problem.
Takeaway
Spreadsheets guess types from CSV text and then save their guess. Import with the wizard, set identifier columns to Text, use ISO dates, never overwrite your only copy, and inspect the true contents in a plain-text view.
