CSV Files and Excel: Encoding, BOM and Delimiters

Symptom 1: garbled accents (mojibake)

You export a UTF-8 CSV, double-click it, and Excel shows Müller instead of Müller, or € instead of €. This happens because Excel on Windows opens a double-clicked CSV in the system’s legacy “ANSI” code page — Windows-1252 on most Western European and US systems — unless something tells it otherwise. UTF-8 encodes ü as the two bytes C3 BC, and Windows-1252 displays those bytes as ü.

The reverse mistake looks different. A file saved by older software in Windows-1252 stores é as the single byte E9. A UTF-8 reader sees an invalid byte and shows the replacement character �, or rejects the file.

Two quick diagnostics:

• Ã followed by another odd character usually means UTF-8 read as Windows-1252.
• � usually means Windows-1252 (or another single-byte encoding) read as UTF-8.

The byte-order mark (BOM)

The reliable way to make Excel read UTF-8 on double-click is to start the file with a UTF-8 byte-order mark: the three bytes EF BB BF. Excel for Windows and for Mac treats them as a declaration that the file is UTF-8. Excel’s own “CSV UTF-8 (Comma delimited)” save option writes this BOM; the plain “CSV (Comma delimited)” option writes the legacy code page instead and replaces characters it cannot encode with ?.

Adding the BOM when you generate files:

# Python: 'utf-8-sig' writes the BOM
df.to_csv("export.csv", index=False, encoding="utf-8-sig")
with open("export.csv", "w", encoding="utf-8-sig", newline="") as f:
    ...

// Node.js / browser: prefix the text with U+FEFF
const blob = new Blob(["\uFEFF" + csvText], { type: "text/csv;charset=utf-8" });

The downside is that other consumers may not expect it. Strict parsers and some Unix tools treat the BOM as part of the first header name, so a column called id becomes \uFEFFid and lookups by name fail. Use the BOM for files meant to be opened in Excel by people, and plain UTF-8 for machine-to-machine pipelines.

Symptom 2: everything in column A

CSV stands for comma-separated values, but Excel does not actually look for commas. It uses the list separator from Windows regional settings. In locales that write decimals with a comma — Germany, France, Spain, Italy, the Netherlands, Brazil and many others — the list separator is a semicolon. Excel there saves “CSV” files with semicolons and, when opening a comma-separated file, finds no semicolons and puts each whole line into column A. The opposite happens when a semicolon file from a European colleague is opened on a US machine.

Options, from most to least portable:

1. Import instead of opening. In Excel use Data → From Text/CSV. The import dialog lets you choose the delimiter and the file origin (pick 65001: Unicode (UTF-8)), and it shows a preview before loading.
2. Add a sep= line. If the first line of the file is sep=, or sep=;, Excel uses that character as the delimiter regardless of locale. It is an Excel-only convention: pandas, databases and most other tools read sep=, as an ordinary first row, which breaks the header. Some users also report that Excel ignores a UTF-8 BOM when a sep= line is present, so test with your Excel version before relying on both together.
3. Use tabs. Tab-separated files are less affected by locale, though Excel still needs to be told the encoding.
4. Skip CSV. Generate a real .xlsx file. Spreadsheets store text as Unicode and numbers as numbers, so encoding and delimiter problems disappear entirely.

Symptom 3: Excel changes your data

Even with the right encoding and delimiter, opening a CSV directly lets Excel convert values it thinks it understands:

• Leading zeros vanish: postcodes and product codes like 00123 become the number 123.
• Long numbers are truncated: Excel keeps 15 significant digits, so a 16-digit card number or an 18-digit ID ends in zeros and is displayed as 1.23457E+17.
• Text becomes dates: 1-2 turns into 1 February or 2 January depending on locale. Gene symbols such as SEPT2 and MARCH1 were converted to dates so often that the HUGO Gene Nomenclature Committee renamed those genes in 2020.
• Formulas run: a cell beginning with =, +, - or @ may be interpreted as a formula. When exporting user-supplied data, guard against this “CSV injection” by prefixing such values with a single quote or a space.

The import dialog avoids most of these: set the affected columns to Text before loading.

A checklist for CSV exports meant for Excel

• UTF-8 with BOM, or .xlsx instead of CSV
• Comma delimiter for international audiences, documented in the file name or README if you use semicolons
• Quote every field that contains the delimiter, a quote or a line break, and double embedded quotes ("Chloé ""CJ"" Martin"), as RFC 4180 describes
• CRLF line endings are the RFC 4180 default, but Excel reads LF too
• Identifiers and codes exported as text, never as numbers

PasteKit can help with each step without uploading the file. When you open or drop a file, it detects UTF-8 with or without a BOM and UTF-16 with a BOM, and falls back to Windows-1252 when the bytes are not valid UTF-8. The CSV formatter auto-detects comma, semicolon, tab or pipe delimiters, aligns columns and flags rows with the wrong number of fields, and its Table view exports a real .xlsx file. CSV to JSON and JSON to CSV handle conversions, with a semicolon option for European Excel.

Related

• csv formatter
• csv to json
• json to csv
• tsv formatter
• csv garbled characters excel
• json byte order mark
• power query formatter