CSV Garbled in Excel? Why It Happens and How a UTF-8 BOM Fixes It

You export a CSV of customer names, open it in Excel, and every Chinese or Japanese name has turned into strings like 日本語. The data is intact — Excel is decoding correct UTF-8 bytes with the wrong table. A CSV file carries no encoding declaration, so Excel guesses. Adding a three-byte UTF-8 BOM removes the guess. Here is the full mechanism, the fixes, and the cases where the BOM makes things worse.

#TL;DR

• CSV files store no encoding metadata, so every reader has to guess.
• Excel guesses your Windows locale code page, usually Windows-1252.
• A UTF-8 BOM — bytes EF BB BF — tells Excel the encoding outright.
• Per the WHATWG Encoding Standard, a BOM outranks every other hint.
• The BOM breaks naive parsers: your first column becomes id.

#Why does my CSV show 日本語 as 日本語 in Excel?

Mojibake is not corruption. It is a decoding mismatch, and it is fully reversible once you know which two encodings were involved.

#A CSV file carries no encoding declaration

This is the root cause, and it is baked into the format. RFC 4180 registers text/csv with charset as an optional MIME parameter and notes that common usage of CSV is US-ASCII. That parameter lives in an HTTP header or an email part — not in the file. The moment the file lands on disk, the charset information is gone.

Compare that to formats that do declare themselves. HTML has <meta charset>. XML has an encoding declaration in its prolog. JSON is defined as UTF-8 on the wire. CSV has nothing. A .csv is an undifferentiated pile of bytes, and every program that opens one has to make an assumption.

#What Excel does when you double-click a .csv

Excel on Windows resolves the ambiguity with the system ANSI code page — the legacy single-byte encoding tied to your locale. In Western Europe and North America that is Windows-1252. In Japan it is code page 932 (a Shift_JIS variant); in Simplified Chinese locales, code page 936 (GBK).

Every byte from 80 to FF gets a character from that table. UTF-8, meanwhile, encodes CJK characters as three bytes each, all in that high range. So three bytes that should render as one character render as three:

"日本語"  UTF-8 bytes:  E6 97 A5 E6 9C AC E8 AA 9E
  read as Windows-1252:  æ  —  ¥  æ  œ  ¬  è  ª  ž
  displayed:             日本語

Nine bytes in, nine characters out. Nothing was lost — the bytes on disk are still perfect UTF-8. Only the interpretation is wrong, which is why saving the mojibake back out of Excel is the step that actually destroys the data.

#Reading the mojibake backwards to find the real encoding

The shape of the garbage tells you which encoding pair you are dealing with. This is worth memorising because it turns a ten-minute mystery into a five-second diagnosis:

The first four share a fingerprint: a leading Ã, æ, ä, or í followed by punctuation-looking characters. That is always UTF-8 misread as a Latin single-byte page, and it is losslessly recoverable.

The last row is different. Replacement characters (U+FFFD) mean a reader tried to decode Shift_JIS or GBK bytes as UTF-8, found invalid sequences, and substituted. 日本語 in Shift_JIS is 93 FA 96 7B 8C EA — none of which form legal UTF-8 sequences. If you see ���, your source file is not UTF-8 at all and a BOM will not help; you need to transcode it first.

#What a UTF-8 BOM actually is

The name is misleading enough that half the confusion around this fix comes from the terminology.

#The three bytes: EF BB BF

The BOM is the Unicode character U+FEFF (ZERO WIDTH NO-BREAK SPACE) encoded in whatever UTF you are using. In UTF-8 that encoding is three bytes: EF BB BF. Prepended to a file, it looks like this:

without BOM:  E6 97 A5 E6 9C AC E8 AA 9E
with BOM:     EF BB BF E6 97 A5 E6 9C AC E8 AA 9E
              ^^^^^^^^ signature

Twelve bytes instead of nine. That is the entire fix — three bytes at offset zero, nothing else in the file changes.

#Why the BOM is a signature, not a byte order mark

UTF-16 and UTF-32 have real endianness: a 16-bit code unit can be stored big-endian or little-endian, and the BOM disambiguates. UTF-8's code unit is a single byte, so byte order is meaningless. The Unicode FAQ on UTF-8, UTF-16, UTF-32 & BOM is explicit that when a BOM appears in UTF-8 it serves only as an encoding signature for text whose character set is otherwise unmarked — exactly the CSV situation.

The convention is Microsoft's. Python's documentation records the history plainly: Microsoft invented the UTF-8-with-signature variant for Notepad, and Python exposes it as the utf-8-sig codec. Thirty years later it is still the interchange contract that makes Excel behave.

#Why the BOM wins over every other encoding hint

If a reader implements the WHATWG Encoding Standard, the BOM is not one hint among several — it is decisive. The spec's decode algorithm sniffs the first three bytes first and overrides the caller's fallback encoding when it finds a match. The standard states the priority rule directly:

"For compatibility with deployed content, the byte order mark is more authoritative than anything else." — WHATWG Encoding Standard

That is why a BOM beats a locale setting, a Content-Type charset parameter, and any heuristic sniffing. Excel's behaviour matches: Microsoft's own guidance says a UTF-8 CSV opens normally on double-click if it was saved with a BOM, and only prescribes the import-wizard workaround otherwise.

#How to add a BOM to a CSV file

Three routes, depending on whether you control the exporter, the file, or neither.

#Python, Node, PHP, and Ruby one-liners

If you own the export code, this is a one-word change. In Python, swap the codec:

# pandas
df.to_csv("out.csv", index=False,
          encoding="utf-8-sig")

# stdlib csv
with open("out.csv", "w", newline="",
          encoding="utf-8-sig") as f:
    csv.writer(f).writerows(rows)

The utf-8-sig codec writes EF BB BF on encode and strips it on decode, so round-tripping through Python is clean. In other runtimes you prepend the bytes yourself:

// Node.js
const BOM = "";
fs.writeFileSync("out.csv", BOM + csvText, "utf8");

// PHP
$fh = fopen('out.csv', 'w');
fwrite($fh, "\xEF\xBB\xBF");
fputcsv($fh, $header);

# Ruby
File.write("out.csv", "" + csv_text,
           encoding: "UTF-8")

The rule is the same everywhere: the BOM must be the first thing written. A BOM after a header row is not a signature — it is a stray zero-width character sitting in the middle of your data.

#Excel's own "CSV UTF-8 (Comma delimited)" save option

Excel 2016 and later ship a distinct save format called CSV UTF-8 (Comma delimited) (*.csv), separate from plain CSV (Comma delimited). It writes UTF-8 with a BOM. Plain CSV (Comma delimited) writes the locale code page and will silently replace any character outside it with ?.

If your workflow is "someone edits in Excel and mails the CSV back," tell them to pick the UTF-8 variant. It is the single most effective piece of process advice for multilingual data teams, and it costs one dropdown selection.

#Doing it in the browser with no upload

When you receive a BOM-less CSV from a vendor and have no build step to hook into, you need a converter that (a) reads it as UTF-8 explicitly and (b) writes it back with a signature. The iKit CSV ↔ JSON converter runs the parse and re-encode entirely in the page — the file never leaves the machine, which matters when the CSV is a customer list. Round-trip CSV → JSON → CSV and you get a normalised, BOM-prefixed file back.

If the CSV came out of a database dump rather than a spreadsheet, the SQL converter turns CREATE/INSERT statements straight into an .xlsx, which sidesteps the encoding question entirely — XLSX is a ZIP of XML with declared encoding, so there is nothing to guess.

#How to open a UTF-8 CSV in Excel without a BOM

Sometimes you cannot modify the file — it is evidence, or it is signed, or the vendor regenerates it nightly. Excel can still read it correctly if you stop double-clicking.

#Data → From Text/CSV and file origin 65001

Open a blank workbook, then Data → Get Data → From File → From Text/CSV. Excel shows a preview pane with a File Origin dropdown. Set it to 65001: Unicode (UTF-8) and watch the preview redraw with correct characters before you click Load. 65001 is the Windows code page identifier for UTF-8.

Power Query defaults to 65001, but it only infers UTF-8 when the file opens with a BOM — otherwise it can fall back to the system page. The dropdown is where you override it. Microsoft's support note Opening CSV UTF-8 files correctly in Excel documents this path and the legacy alternative below.

#The legacy Text Import Wizard

The older Get Data From Text command opens the Text Import Wizard instead of Power Query. Step 1 has the same File Origin dropdown. The practical difference: the wizard imports values without creating an external query connection in the workbook, which some teams prefer because the resulting file has no refresh dependency on a path that will not exist on a colleague's machine.

#Google Sheets, Numbers, and LibreOffice

Not every spreadsheet guesses badly. Google Sheets assumes UTF-8 on import and handles BOM-less files correctly in the common case. LibreOffice Calc always shows an import dialog with an explicit Character set selector — no silent guess, which is why "open it in LibreOffice first" is standard triage advice. Apple Numbers infers UTF-8 and generally gets it right.

This asymmetry is worth stating to stakeholders: the file is not broken, and it opens fine in three other applications. The problem is specific to Excel's double-click path on Windows.

#When the BOM causes problems instead

The BOM is not free. It solves a display problem for one consumer and creates a parsing problem for several others.

#The id header column bug

This is the classic. A parser that opens the file as plain utf-8 rather than utf-8-sig reads U+FEFF as an ordinary character and glues it onto the first header name. Your column is no longer id — it is id. Every downstream row["id"] lookup returns None, usually with no error, because the key genuinely does not exist.

# Symptom
>>> list(reader)[0].keys()
dict_keys(['id', 'name', 'email'])

# Fix at read time
open("in.csv", encoding="utf-8-sig")

Most modern libraries handle this — Python's utf-8-sig, PapaParse, .NET's StreamReader with detectEncodingFromByteOrderMarks. Hand-rolled split(",") parsers do not. If a nightly job started failing the day after someone "fixed the encoding," this is why.

#Shell scripts, JSON, and diff noise

The Unicode FAQ warns that a BOM interferes with any format that expects specific ASCII bytes at position zero. The canonical casualty is the shebang: a BOM before #!/bin/sh makes the kernel refuse to execute the script. jq rejects BOM-prefixed JSON. And in version control, adding a BOM changes the first line of the file, so a diff of an otherwise-unchanged export shows a spurious modification — worth checking with a diff tool before you blame the exporter.

If you need to inspect what a JSON or CSV payload actually contains before deciding, a JSON decoder that shows you the parsed structure is faster than reasoning about the bytes.

#A decision table: BOM or no BOM

The short version: BOM at the boundary where humans open files, no BOM inside pipelines where machines read them. If one export serves both, produce two files rather than arguing about which consumer matters more.

#References

• RFC 4180 — Common Format and MIME Type for Comma-Separated Values (CSV) Files — confirmed that charset is an optional MIME parameter and that common usage is US-ASCII, i.e. the file itself declares nothing.
• FAQ - UTF-8, UTF-16, UTF-32 & BOM — Unicode's own position on the UTF-8 BOM as a signature rather than a byte order mark, and its warnings about ASCII-sensitive formats.
• Encoding Standard — the decode and BOM-sniff algorithms; source of the rule that the BOM overrides all other encoding hints.
• Opening CSV UTF-8 files correctly in Excel — Microsoft's documented Power Query and Text Import Wizard routes for BOM-less UTF-8 files.
• codecs — Codec registry and base classes — the utf-8-sig codec, its Notepad origin, and its encode/decode behaviour around the signature bytes.

#Related on iKit

• RFC 4180 explained, including the CSV edge cases every parser gets wrong — the spec-level companion to this article; covers quoting, CRLF, and delimiters alongside encoding.
• The complete guide to converting CSV to JSON — the conversion workflow that lets you normalise encoding as a side effect of a round trip.
• How to flatten nested objects when converting JSON to CSV — the other direction, where you choose the output encoding at write time.
• Convert Excel files to JSON in the browser with no server — skips the CSV middle step entirely, which removes the encoding guess from the pipeline.
• Turn SQL dumps into Excel workbooks in under 30 seconds — for database exports, going straight to .xlsx avoids the BOM question because XLSX declares its own encoding.