CSV Opens as Garbage: BOM, Delimiters, and Encoding

Exporting a report, downloading a feed, integrating two systems — if a CSV is involved, "it opens as garbage" is a matter of when.

The short version:

Symptom	Cause	Fix
Accented or non-Latin text becomes boxes or é	File is UTF-8, Excel decoded it as a legacy ANSI codepage	Save as UTF-8 with BOM, or import via Data → From Text/CSV
Everything lands in one column	The delimiter is not a comma	Specify the delimiter during import
1.23E+15, ###, leading zeros gone	Excel type-inferred a text field	Import with the column typed as Text
A cell contains an unexpected line break	Field contains a delimiter or newline without quoting	Quote per RFC 4180

Mojibake is not corruption

A text file is just bytes; the encoding is the agreement about how to interpret them. The byte E9 is é in Latin-1, an incomplete sequence in UTF-8, and part of a multibyte character in Shift-JIS.

Read UTF-8 bytes as Latin-1 and every multi-byte character splits into two plausible-looking Latin-1 characters — that is é where you expected é. No bytes were lost. The interpretation was wrong.

So the only correct repair is re-reading with a different encoding, never editing the text by hand.

Why Excel gets it wrong on double-click

Excel's double-click path looks at exactly two things:

1. Is there a BOM at the start of the file?
2. If not, use the system's legacy ANSI codepage.

A BOM-less UTF-8 file therefore renders as mojibake on any machine configured with a non-UTF-8 default. The Data → From Text/CSV route asks you to pick encoding explicitly, which is why it works.

The same file may look fine on macOS, where the default is UTF-8. "It works on my colleague's machine" is almost always this.

BOM: when to add it

The BOM is three bytes (EF BB BF, U+FEFF) at the start. It is not content; it is a hint.

Scenario	Recommendation
CSV downloaded by spreadsheet users	Add it. It is the only way double-click works
CSV read by code (pandas, backend import)	Prefer no BOM; if present, read with encoding='utf-8-sig'
Upload to a third-party system	Check their docs. Some systems include the BOM in the first header name

That last row is a nasty one: the column is named name, but the program receives \ufeffname. The strings are not equal, validation fails, and the file looks fine to every human who opens it.

To check for a BOM:

head -c 3 data.csv | xxd
# BOM present: 00000000: efbb bf

[System.IO.File]::ReadAllBytes('data.csv')[0..2] -join ','
# BOM present: 239,187,191

Delimiters: the C is aspirational

Comma-separated is the name, not the reality. Semicolons, tabs and pipes are everywhere — in locales where the comma is the decimal separator, Excel uses semicolons as the list separator.

The quick test: open the file as plain text and look for the character that appears at regular intervals.

Rather than renaming extensions, use CSV to JSON, which auto-detects comma, semicolon, tab and pipe, and shows you exactly how many columns resulted. If you just need a normalised table, Table Converter converts between CSV, TSV and Markdown.

Quoting and escaping

RFC 4180: a field containing a delimiter, a double quote, or a newline must be wrapped in double quotes, and internal quotes are doubled.

name,note
"Smith, John","He said ""tomorrow"", don't wait"
"Doe, Jane","line one
line two"

The second record is one row spread across two lines of text. Splitting on \n fails on this, which is the first bug in every hand-written CSV parser. You need a state machine that tracks whether you are inside quotes.

Conversely, "the export is broken" after saving from Excel is usually just a different quoting policy — Excel only quotes when necessary.

A diagnostic order that works

1. Check the first three bytes for EF BB BF. BOM present but still garbled means the file is not actually UTF-8.
2. Try encodings in order: UTF-8, then Windows-1252 / Latin-1, then Shift-JIS or GB18030 depending on origin. GB18030 is a superset that also covers GBK.
3. Count columns. Fixed encoding but still one column means a delimiter problem, not an encoding problem.
4. Watch the types. Leading zeros, long numeric IDs and #N/A all mean Excel guessed. Use the import wizard, or process the file without opening it.
5. Convert to JSON to inspect. JSON forces explicit strings, so a wrongly split column becomes immediately visible.

The one-line rule

Garbage characters mean the encoding was guessed wrong. One column means the delimiter was guessed wrong. Deformed numbers mean the type was guessed wrong. Double-clicking makes Excel guess all three. Use the import wizard and it stops guessing.

And when exporting: add a BOM for spreadsheets, declare UTF-8 explicitly for code.