A CSV file is plain text. It has no types, no formatting and no metadata, just characters separated by commas. When Excel opens one, it must guess what each value is meant to be, and its guesses are aggressive and irreversible.
The four classic corruptions
Leading zeros disappear. 00745 becomes 745. Excel decides it is a number, and numbers do not have leading zeros. This destroys postcodes, product SKUs, employee IDs, bank sort codes and any zero-padded identifier.
Long numbers become scientific notation. 4532015112830366 displays as 4.53202E+15. Worse, Excel stores numbers with only 15 significant digits, so a 16-digit credit card or IMEI number is not merely displayed oddly, the final digits are permanently replaced with zeros. Save the file and the original value is gone.
Text becomes dates. 3-4 becomes 3 April. 1/2 becomes 1 February or 2 January depending on locale. This is not a hypothetical edge case: in 2020 the HUGO Gene Nomenclature Committee formally renamed several human genes because Excel kept converting symbols like SEPT1 and MARCH1 into dates, and the errors had contaminated a significant fraction of published genomics supplementary data.
Accented characters turn to gibberish. José becomes José. Excel does not reliably detect UTF-8 in a .csv file and falls back to the system's legacy code page.
Why the encoding one is so persistent
Excel will detect UTF-8 if the file begins with a byte order mark, the three bytes EF BB BF. Without it, it assumes the local ANSI code page.
So the fix for Excel is to write the BOM. The complication is that a BOM is unwelcome almost everywhere else: many parsers include it in the first column's header name, so id silently becomes \ufeffid and every lookup by that name fails.
This is a real incompatibility, not a bug you can fix once. If a file is for Excel, write the BOM. If it is for a data pipeline, do not.
The delimiter is not always a comma
Excel does not use a comma when opening CSVs. It uses the list separator from your operating system's regional settings. In locales where the decimal separator is a comma, much of Europe and Latin America, that list separator is a semicolon.
The consequence is that a comma-separated file opens correctly on one colleague's machine and lands entirely in column A on another's. Nothing is wrong with the file; the two machines disagree about what CSV means.
Getting data in safely
Never double-click a CSV you care about. Double-clicking gives Excel free rein to guess. Instead, use Data → From Text/CSV, which opens the import dialog where you can set the encoding, the delimiter, and, crucially, the type of each column. Set identifier columns to Text and Excel leaves them alone.
Or defend in the file itself. Prefixing a value with an apostrophe ('00745) forces text, though the apostrophe is an Excel convention that other tools will treat as part of the data.
Or avoid the round trip. If the data is going to a database or a script, do not open it in Excel at all. Every trip through a spreadsheet is a chance for a silent type conversion.
Rules for producing CSVs others will open
- Quote every field that could contain a comma, a quote, or a line break. Escape embedded quotes by doubling them (
""), per RFC 4180. - Use ISO dates,
2026-06-19. Unambiguous everywhere, sorts correctly as text, and resistant to locale reinterpretation. - Write UTF-8, with a BOM if and only if Excel is the intended consumer.
- Keep identifiers as text and say so in the documentation, because CSV cannot say it in the file.
- State the delimiter in whatever accompanies the file.
- Do not put commas in numbers.
1,234.56is two fields, not one.
Checking before you trust it
The fastest way to find out what a file actually contains is to look at it in something that does not reinterpret it. A CSV viewer shows the parsed rows as they really are, and a CSV cleaner normalises delimiters, quoting and encoding before the file reaches a spreadsheet. Both run locally, which matters when the file is a customer export.