Why Your CSV Opens Wrong: Encodings, BOMs and Delimiter Guessing

"Müller" renders as "Müller". The first column is mysteriously named "id". And the entire file opens as a single column. Three different gremlins, one file — and none of them mean your data is lost. CSV has no header declaring its encoding or delimiter, so every tool guesses, and guesses fail in systematic, fixable ways.

Gremlin 1: Mojibake (Wrong Encoding)

Those ü sequences are UTF-8 bytes read as Windows-1252 (or vice versa): the classic encoding mismatch. The fix chain: detect first — a UTF-8 validity check catches most cases, since random legacy bytes are rarely valid UTF-8; decode with the right codec (Windows-1252 for Western-European legacy exports, Shift_JIS for Japanese ones); and re-encode everything to UTF-8 at ingest so the problem is solved once, upstream, instead of per-tool forever.

Prevention beats repair: when you control the export, always emit UTF-8 and say so in documentation. When you receive files, make encoding detection part of the standard import pipeline — a file whose encoding you guessed wrong will pass every downstream check while corrupting every non-ASCII value.

Gremlin 2: The Phantom BOM

"id" is a UTF-8 byte-order mark (BOM) displayed as data: three invisible bytes (EF BB BF) that Excel prepends to UTF-8 files, now glued to your first column name. Joins on that column fail silently ("id" ? "id"), and the cause is invisible in most editors. The rule: strip a leading BOM on read, always. It carries no information in UTF-8 (byte order is fixed); it exists only because Excel writes it. Any CSV reader that does not strip it by default is a reader you should configure or replace.

Gremlin 3: The Wrong Delimiter

Everything lands in one column because the file uses semicolons (common where the comma is a decimal separator) or tabs while the tool assumed commas — or the reverse: commas inside unquoted fields shatter rows into confetti. Robust delimiter sniffing reads a sample of lines and picks the candidate (comma, semicolon, tab, pipe) that yields the most consistent column count. Consistency across lines beats frequency within one line, because text fields are full of commas that are not delimiters.

Related traps: quoted fields containing newlines (a naive line-splitter breaks the row — parse with a real CSV grammar, not split('\n')); inconsistent quoting between rows; and trailing delimiters creating phantom empty columns. After import, verify mechanically: consistent column counts, expected headers, and row counts matching the source — the same round-trip checks as any export handoff, because an import is just someone else's export arriving at your door.