The CSV that opens as gibberish
A CSV exported from one system, opened in Excel on another machine, and every accented or non-Latin character has turned into nonsense: JosΓ© becomes JosΓΒ©, Thai text becomes a run of question marks, Japanese becomes boxes. Sometimes the whole file lands in column A with commas still visible. Nothing is corrupted β CSV is a text file with no header declaring how it was written, so every program that opens one is guessing at two things: the character encoding and the separator. This guide shows how to make the guess unnecessary.
Why CSV cannot describe itself
A CSV file is bytes. There is no metadata block, no version field, no declared charset. When Excel opens one it must decide what those bytes mean, and historically it assumed the system's legacy code page β Windows-1252 in Western Europe, Windows-874 in Thailand, Shift-JIS in Japan β rather than UTF-8. Modern Excel is better, but the defaults still differ by version, platform and locale.
| What you see | What went wrong | Fix |
|---|---|---|
| JosΓΒ©, Γ’β¬ΕquotedΓ’β¬, ΓΒ€ | UTF-8 bytes read as Windows-1252 | Re-open declaring UTF-8, or add a BOM |
| Question marks or boxes for Thai, Chinese, Japanese, Cyrillic | Read with a code page that has no such characters | UTF-8, and a font that covers the script |
| Everything in column A, commas visible | Excel expected the locale's list separator (often a semicolon) | Use semicolons, or import rather than double-click |
| Leading zeros gone from 007823 | Excel parsed the column as a number | Import and set the column type to Text |
| Dates flipped: 03/04 became April 3rd | Day/month order taken from the machine's locale | Write dates as YYYY-MM-DD in the source |
| Long numbers as 1.23457E+14 | Scientific notation applied to a numeric column | Set the column to Text on import |
The BOM: three bytes that solve most of this
A Byte Order Mark is the sequence EF BB BF at the very start of a file. In UTF-8 it carries no textual meaning, but Excel treats it as a declaration: these bytes are UTF-8. Add it and double-clicking a CSV shows accents and non-Latin scripts correctly on Windows, without any import dialog.
The catch is that the BOM is a Microsoft convention, and other software is less pleased to see it. A Python script reading with plain utf-8 will find an invisible character glued to the first column name, so id silently becomes something that does not compare equal to id. The rule of thumb:
- Add the BOM when a human will open the file in Excel on Windows.
- Leave it out when a program will parse the file β a data pipeline, a database import, another service's API.
- In Python, read with
encoding="utf-8-sig"and the BOM is stripped if present and ignored if not. It is the safe default for files of unknown origin.
Separators, quoting and the rules people forget
The C in CSV means comma, but in locales where the comma is the decimal mark β most of continental Europe, and Excel follows Windows regional settings β the list separator is a semicolon, and Excel both writes and expects that. A file that opens perfectly in Berlin lands in one column in London.
- Quoting. A field containing the separator, a double quote or a line break must be wrapped in double quotes. A literal quote inside is doubled:
"He said ""yes""". - Line endings. The specification says CRLF. Unix tools write LF. Every modern reader copes; very old ones do not.
- TSV. Tabs sidestep the comma-versus-semicolon argument entirely, because tabs almost never appear inside a field. If you control the export, it is an underrated choice.
- The sep line. Excel honours a first line reading
sep=;and uses that separator. Almost nothing else does, and it will appear as a stray row in every other tool.
The reliable ways to open a CSV in Excel
Double-clicking is the operation that makes assumptions. Two alternatives do not:
- Data β From Text/CSV (Power Query). Choose the file origin as UTF-8, pick the delimiter, and set each column's type before loading. This is the one that preserves leading zeros and long identifiers.
- Convert to .xlsx first. An Excel workbook stores its text as UTF-8 inside a ZIP container and records which cells are text. There is nothing left to guess, so the file opens the same way on every machine. CSV to Excel does this in your browser, and Excel to CSV goes the other way when a system demands CSV.
Converting to XLSX is the most durable fix when you are sending data to colleagues. It removes the encoding question, the separator question and the type-coercion question in one step.
If you control the export
Most of this is preventable upstream. Write UTF-8. Add the BOM only for Excel audiences. Use ISO dates in YYYY-MM-DD form. Keep identifiers that have leading zeros, such as postcodes and account numbers, in a column the receiving side will treat as text, and say so in the documentation. Quote every field that could contain a separator rather than hoping none does. None of this costs anything at export time and it removes an entire category of support request afterwards.