CSV encoding: why Excel mangles your accents and how to fix it

You export a CSV containing José, open it in Excel, and see José. The file is correct: you wrote valid UTF-8, and any text editor or script reads it back perfectly. The problem is that a CSV file carries no declaration of its own encoding, so Excel guesses, and on Windows it has historically guessed the legacy system code page rather than UTF-8. Everything about this problem, and its fix, follows from that missing declaration.

You can convert between JSON and CSV, and see exactly what the output contains, with the JSON to CSV converter.

Why the characters get mangled

é in UTF-8 is two bytes, C3 A9. Read those same two bytes as Windows-1252, where every byte is one character, and you get Ã followed by ©. That is the whole mechanism: no data is lost, the bytes are simply interpreted under the wrong table. The result has a name, mojibake, and its signature is recognisable, since specific pairs recur:

Seeing Ã before an accented character means UTF-8 read as Latin-1 or Windows-1252. Seeing a literal  at the start of the first cell means the opposite: a byte order mark read as text.

The BOM fix

A byte order mark is the three byte sequence EF BB BF at the start of a file. In UTF-8 it serves no technical purpose, since UTF-8 has no byte order to signal, but it functions as a marker: Excel sees it and switches to UTF-8 without asking.

Prepending it is the standard fix for the Excel problem:

const csv = 'name,city\nJosé,Köln\n'
const blob = new Blob(['' + csv], {
  type: 'text/csv;charset=utf-8;',
})

In Python:

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

utf-8-sig is UTF-8 with the BOM. The newline='' argument matters too: without it, Python's csv module and the platform both add line endings and you get blank rows between every record on Windows.

The cost of the BOM is that other consumers may not expect it. Naive parsers hand back a first column named name instead of name, which then fails every lookup by field name. Command line tools show a stray character at the start of line one. The standard advice reflects this split:

• Exports intended for humans to open in Excel: include the BOM.
• Files consumed by other programs: omit it, and document that the file is UTF-8.
• Parsers you write: strip a leading BOM before doing anything else, unconditionally.

Offering both is not unreasonable. A single "Excel compatible" checkbox on an export screen solves more support tickets than any amount of documentation.

The delimiter problem

The second half of the Excel story is the separator. Excel does not use a comma; it uses the list separator from the user's locale. In much of Europe that is a semicolon, because the comma is the decimal separator. A correct comma delimited file opened on a German or French Windows system lands entirely in column A.

There is a non standard header that resolves it, understood by Excel and LibreOffice:

sep=;
name;city
José;Köln

The sep= line must be the very first line, before the header. It is not part of any CSV specification, and other parsers will read it as a data row, so it belongs only in files explicitly built for spreadsheet users.

The general solution is the same as with the BOM: produce a strict, standard file for machine consumption, and a separate spreadsheet friendly file for people. Do not try to make one file satisfy both.

Quoting rules that still apply

Whatever the encoding, the structural rules are unchanged and are still where malformed files come from:

• A field containing the delimiter, a double quote, or a newline must be wrapped in double quotes.
• A double quote inside a quoted field is escaped by doubling it: "He said ""hi""".
• Newlines inside quoted fields are legal, and a parser that splits the file by line before parsing will corrupt them. This is the single most common way hand written CSV parsing breaks.

id,name,note
1,"Smith, John","Said ""yes"" on
the second call"

Use a real CSV writer rather than string concatenation. The rules look trivial until a customer's name contains a comma.

Excel's other habits

Even with correct encoding and delimiters, Excel transforms values on open:

• Leading zeros disappear. Postal codes and account numbers become numbers.
• Long numbers become scientific notation. Anything past 15 digits also loses precision permanently.
• Values that look like dates become dates, which is how gene names became a documented problem in genomics and why product codes turn into 2026 dates.
• Values beginning with =, +, -, or @ are treated as formulas. This is not only a display issue: a field containing =HYPERLINK(...) or a command invocation is a genuine injection vector when the file is exported from user supplied data. Prefixing such fields with a single quote, or refusing to emit them unquoted, is a standard mitigation.

None of these are fixable from the file side alone, because CSV has no type information. If types must survive, the file format is the problem, and JSON or a real spreadsheet format is the answer. Converting a JSON API response into a flat CSV, and back, is exactly what the JSON to CSV tool does, and JSON to CSV: API to spreadsheet covers the shape mismatch between nested and tabular data.

A checklist for exports

1. Write UTF-8.
2. Add the BOM if Excel is the intended consumer; strip it when parsing.
3. Use \r\n line endings, which is what the CSV specification asks for and what Windows tools expect.
4. Quote any field containing a delimiter, quote, or newline; double internal quotes.
5. Consider sep= only for spreadsheet targeted files.
6. Neutralise fields beginning with =, +, -, or @.
7. Test by opening the file in Excel on Windows, not only in a text editor, since a text editor will never reproduce the bug you are trying to prevent.