CSV encoding: UTF-8, BOM, windows-1251 and why Excel shows garbage

Legacy code pages such as windows-1251 (Cyrillic) and windows-1252 (Western European) map each of the 256 possible byte values to exactly one character. That is efficient and completely unambiguous — as long as everyone agrees which page is in use. Nothing in the file says so, which is the whole problem: the byte C0 is «А» in windows-1251 and «À» in windows-1252.

UTF-8 solves that by spending more bytes: ASCII characters stay one byte, most European and Cyrillic letters take two, and CJK takes three. The encoding is self-describing — the high bits of each leading byte announce how many bytes follow — which means a decoder can verify a file really is UTF-8 by checking that every sequence is well-formed. CSV Polish does exactly that first, and only falls back to a code page when the check fails.

When the UTF-8 check fails, the remaining question is which single-byte page it is. Cyrillic text shows up as long runs of high bytes (whole words above 0x7F); Western text sprinkles single accented letters between ASCII ones, as in «Genève». Counting those two patterns, plus the byte values that are unassigned in each page, identifies the encoding correctly for ordinary business data — and the tool tells you when it is only guessing.

The byte order mark is the character U+FEFF. In UTF-16 it genuinely marks byte order; in UTF-8 there is no order to mark, so it serves only as a signature: the three bytes EF BB BF at the start of a file mean “this is UTF-8”. The Unicode standard calls it permitted but not recommended — and then there is Excel.

Excel on Windows, opening a .csv by double-click, does not sniff UTF-8. Without a BOM it decodes the bytes with the system ANSI code page, and every Cyrillic or accented name turns to garbage. With the BOM it switches to UTF-8 and the same file opens perfectly. That is why “UTF-8 with BOM” is the default export in CSV Polish: it is the setting that makes a double-click work for the person you send the file to.

The cost is that strict consumers see the BOM as data. A naive parser reports the first column as “\ufeffid”, and some database loaders reject the row outright. If the file is going into a script, a data warehouse or a Unix pipeline, export plain UTF-8; if it is going to a colleague who will double-click it, export with the BOM.

In UTF-8, «привет» is twelve bytes (D0 BF D1 80 D0 B8 D0 B2 D0 B5 D1 82). Read those bytes through windows-1252, where each byte is its own character, and you get «Ð¿Ñ€Ð¸Ð²ÐµÑ‚» — twelve visible characters instead of six. The text is not lost; it has been decoded with the wrong alphabet. The same accident through windows-1251 produces the Russian-looking «РїСЂРёРІРµС‚».

That means the damage is usually reversible. Encode the mangled string back to the bytes of the code page that produced it, then decode those bytes as UTF-8. If the round trip yields valid UTF-8, the original text is recovered exactly; if it does not, the file lost bytes on the way — often because somebody saved it through a program that replaced unmappable characters with ? — and no tool can bring them back.

CSV Polish runs that repair per cell and reports it as a separate issue, because it must happen before anything else touches the text: trimming or case-folding mojibake first would corrupt the byte sequence and make the repair impossible. In the browser the whole operation is TextDecoder and TextEncoder — no library, no upload.

Google Sheets is the forgiving one: File → Import lets you pick the separator, detects UTF-8 reliably and does not need a BOM. Numbers on macOS also assumes UTF-8 and generally gets it right, but it offers no encoding chooser at all — if a file opens wrong there, you must fix the file, not the app.

LibreOffice Calc shows a full import dialog every time, with an encoding list and a delimiter choice, which makes it the best free tool for inspecting a suspicious file. Excel hides the same power behind Data → From Text/CSV (Power Query), where you can set File Origin to 65001: Unicode (UTF-8) and mark ID columns as Text before the data lands in the sheet.

One more Excel-only lever: a first line containing sep=; forces the separator regardless of regional settings. It helps a colleague in another country open your semicolon file, and it breaks every non-Excel reader. Use it only for files that are going to Excel and nowhere else.

Receiving: open the file and look at one row with non-English text. If you see Ð, Ñ, Ã or characters, it is mojibake and needs repairing, not re-typing. If you see rows of question marks or empty boxes, the bytes are already gone — ask for a fresh export, in UTF-8 this time.

Sending: UTF-8 with BOM for people, plain UTF-8 for machines, and say which one you used in the message. Add the delimiter and the date format to that sentence and you will save the recipient the whole of this guide.