CSV encoding and the mysterious BOM
Excel opens a UTF-8 file without a BOM using the system's legacy code page, and “Café” turns into “Café”. That is neither an Excel bug nor a file bug: a CSV simply has nowhere to say what encoding it is in.
A CSV has no place for an encoding
XML declares its encoding in the prolog, JSON is by standard always UTF-8, DBF keeps a code in its header. A CSV has nothing: it is just text with commas. The program that reads it must guess — or ask.
Excel does not ask. On a double-click it uses the system's ANSI code page — windows-1252 on a Western Windows install. Anything not in that encoding comes out as mush.
What the BOM is and why it helps
The BOM (byte order mark) is three service bytes, EF BB BF, at the start of a file. Formally they mean “this is UTF-8”, and Excel understands them: a file with a BOM opens correctly even on a double-click.
The flip side: everyone else sees those bytes too. A program that does not know about the BOM reads the first column name as id with an invisible character in front — which breaks field matching. That is the classic cause of the mysterious “column id not found”.
So the rule is simple: use a BOM if the file is meant for a person with Excel; leave it out if the file is meant for a program.
Detecting the encoding from content
UTF-8 is recognizable structurally: its high bytes follow strictly defined sequences. If the file obeys that rule and also contains non-ASCII bytes, it is almost certainly UTF-8.
If the structure does not fit, single-byte candidates remain, and the content decides: the text is decoded with each candidate and the one that reads like real text wins — no box-drawing characters, no control or replacement characters. When that is not conclusive, you can switch the encoding manually without saving the file again. The same mechanism is used for DBF; see garbled characters in DBF.
The delimiter: the other half of the same problem
The standard says “comma”, but in regions where the comma is the decimal separator (Germany, France, Spain, Brazil and many others), Excel expects a semicolon by default. Hence files that open as a single column — see CSV opens in one column in Excel.
The delimiter can be detected reliably: try the candidates and pick the one that gives every row the same number of fields, counting quotes properly. If there is no confident answer, it is more honest to say so and let the user switch manually than to silently show a wrong table.
How to save so that nobody has problems
- For exchange between programs: UTF-8 without BOM, comma as the delimiter,
\nline endings. - For a person using Excel: UTF-8 with BOM, and a semicolon if their region uses the decimal comma.
- For older systems: windows-1252 (or the code page they expect), semicolon. Check that every character fits — curly quotes, dashes and the euro sign are missing from some code pages.
In the Tabulens export dialog these are ready-made combinations rather than an abstract list of encodings.
FAQ
Why did my file become windows-1252 after saving from Excel?
Excel saves plain “CSV” in the system code page. For UTF-8, choose “CSV UTF-8 (Comma delimited)” as a separate option.
Can I remove the BOM?
Yes, it is just the first three bytes of the file. Any editor that lets you choose an encoding can save without it.
What if the file mixes encodings?
Split it by source and process the parts separately: a single table cannot be recoded when the encodings are mixed inside it.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.