ü Instead of ü: Fixing Broken Special Characters in CSV Files

You export a CSV, open it in Excel, and every umlaut, accent, and euro sign is wrecked: München becomes München, José becomes José, 5€ becomes 5€. Nothing is random about this. The bytes in the file are fine; Excel is simply reading them with the wrong decoder. This guide shows exactly how the corruption happens byte by byte, what the UTF-8 byte order mark is, why Excel cares about it when almost nothing else does, and how to save, import, and repair CSV files so the characters survive.

What an encoding actually is

A file on disk is only bytes. A character encoding is the mapping that turns those bytes back into text: the same byte sequence yields different characters depending on which mapping the reader applies. UTF-8 covers every language and is what nearly every modern system writes. Windows-1252, which Excel calls ANSI, is an old single-byte encoding where every byte is exactly one character. Plain ASCII text (letters, digits, commas) is identical in both, which is why the bug hides until the first ü or € appears.

München, decoded byte by byte

UTF-8 stores common accented characters as two bytes. Windows-1252 has no concept of multi-byte characters, so it reads those two bytes as two separate one-byte characters. That is the whole mechanism behind mojibake.

The word "München" saved as UTF-8:

bytes:         4D  C3 BC  6E  63  68  65  6E
read as UTF-8:  M   ü     n   c   h   e   n     (C3 BC is ONE character)
read as ANSI:   M   Ã  ¼  n   c   h   e   n     (C3 BC is TWO characters)

Same trick for other characters:
  é  = C3 A9  ->  é        ñ  = C3 B1  ->  ñ
  €  = E2 82 AC  ->  €    (three bytes, so three junk characters)

Notice the fingerprint: two-byte UTF-8 characters become two visible junk characters, usually starting with à or Å, and three-byte characters like € become three. If you see that pattern, you know with certainty the file is valid UTF-8 being misread as Windows-1252 or Latin-1. The reverse mistake exists too: a genuine ANSI file read as UTF-8 shows the replacement character � instead, because bytes like a lone 0xFC (ü in Windows-1252) are not valid UTF-8 sequences.

The byte order mark, and why Excel wants it

How is Excel supposed to know which decoder to use? A .csv file carries no metadata; it is bare bytes. One convention fills the gap: the byte order mark (BOM). In UTF-8 it is the three bytes EF BB BF at the very start of the file, the UTF-8 encoding of the character U+FEFF. UTF-8 has only one byte order, so the BOM carries no ordering information here. The Unicode FAQ is explicit that a BOM is used in UTF-8 purely as a signature, an indication that an otherwise unmarked file is UTF-8, and the standard neither requires nor recommends it. Excel, however, leans on exactly that signature: double-click a CSV that starts with EF BB BF and Excel decodes it as UTF-8; double-click the same file without the BOM and Excel on Windows falls back to the system ANSI code page, and your ü becomes ü.

Saving it right from Excel

Since Excel 2016, Excel has a dedicated format for this. Go to File, Save As, choose the file type "CSV UTF-8 (Comma delimited)". This writes UTF-8 with the BOM, so the file round-trips through Excel correctly and stays readable everywhere else. The older plain "CSV (Comma delimited)" option saves in the legacy ANSI code page and will destroy any character that code page cannot represent, replacing it with a question mark. If your Excel is older than 2016, that lossy option is all you get; save as "Unicode Text" instead or use another tool for the final export.

Opening it right: the import route

Double-clicking a CSV lets Excel guess, and guessing is the whole problem. The reliable route is to state the encoding explicitly: in Excel go to Data, then Get & Transform Data, then From Text/CSV, pick the file, and set File Origin to "65001: Unicode (UTF-8)" in the preview dialog. This works with or without a BOM, and it also lets you fix the delimiter and stop Excel from mangling values, the same wizard that rescues you from leading zeros and date auto-conversion. If your file uses semicolons because of a European locale, the same dialog handles that too; see comma versus semicolon CSVs for why that split exists.

Writing CSVs from code

If you generate CSVs that end users will double-click into Excel, write UTF-8 and prepend the BOM yourself. It is one string constant.

// JavaScript: prepend the BOM so Excel detects UTF-8
const BOM = "\uFEFF";
const blob = new Blob([BOM + csvString], { type: "text/csv;charset=utf-8" });

# Python: the "utf-8-sig" codec writes EF BB BF for you
with open("export.csv", "w", encoding="utf-8-sig", newline="") as f:
    writer = csv.writer(f)

(utf-8-sig also strips the BOM when READING, so use it on both ends.)

For APIs and machine-to-machine pipelines, do the opposite: plain UTF-8, no BOM, and declare charset=utf-8 in the Content-Type header. Reserve the BOM for files whose destiny is a double-click.

Symptom, cause, fix

Repairing an already-mangled file

First, check whether the file is actually damaged. If someone merely opened it wrong and closed without saving, the bytes are intact; open it again with the correct encoding and you are done. Real damage happens when the misread text gets saved: the ü is now literally stored in the file. Even then it is usually reversible, because the corruption is a deterministic round trip: encode the mojibake text back to Windows-1252 bytes, then decode those bytes as UTF-8, and München becomes München again. In Python that is s.encode("cp1252").decode("utf-8"). It fails only if the save step lost information, for example if a byte had no Windows-1252 mapping and became a question mark. Once the text is clean, load it into the CSV Editor to verify columns and values survived, and double-check the quoting is still intact, since encoding accidents and delimiter accidents often travel together.

If you just need the file to open cleanly in Excel without touching wizards or code, convert it directly: the converter below reads your CSV, lets you confirm the characters look right, and produces a real .xlsx, which stores text as UTF-8 internally and has no encoding guesswork at all. Everything runs in your browser; the file never leaves your machine.

Sources

• Unicode FAQ: UTF-8, UTF-16, UTF-32 and BOM
• Microsoft Support: What's new in Excel 2019 (CSV UTF-8 save format)
• Microsoft Support: Import or export text (.txt or .csv) files
• MDN: Character encoding
• W3C: Character encodings for beginners