UTF-8 vs UTF-8 BOM for CSV: A Tested Python Read/Write Matrix

Use the receiving system’s encoding contract first. In our Python CSV test, utf-8-sig read both plain UTF-8 and BOM-prefixed UTF-8 with a clean first header. A plain utf-8 reader kept the BOM in that header. The difference was a three-byte prefix, not different Korean text or a changed identifier.

All four combinations preserve cell values. The failure is an exact header lookup, not lost row data.

This reference compares four read/write combinations using one controlled fixture. It answers an export-design question: which UTF-8 variant should a producer write, and what should the consumer read? Tests ran on Python 3.12.13 on macOS on September 6, 2026. Excel behavior below is attributed to Microsoft, not presented as a local Excel test.

The fixture and what was checked

id,name,note
00123,서울,"comma, quote ""yes"""

The actual test string uses CRLF line endings. We check the first header, a leading-zero identifier, Korean text, and a quoted field containing a comma and doubled quotes. Each input is decoded and then parsed with csv.DictReader.

Observed results: four combinations

Python 3.12.13 CSV read/write results

Write encoding	Read encoding	First header (repr)	row["id"]
utf-8	utf-8	'id'	'00123'
utf-8	utf-8-sig	'id'	'00123'
utf-8-sig	utf-8	'\ufeffid'	KeyError
utf-8-sig	utf-8-sig	'id'	'00123'

All four combinations preserved the cell values, including 서울 and comma, quote "yes". Only the third combination failed the exact id lookup. In that row the value still existed under the prefixed key. Plain UTF-8 began with 69 64 2c (id,); BOM-prefixed UTF-8 began with ef bb bf.

Run the same comparison

import csv
import io

text = 'id,name,note\r\n00123,서울,"comma, quote ""yes"""\r\n'
for write_encoding in ("utf-8", "utf-8-sig"):
    raw = text.encode(write_encoding)
    for read_encoding in ("utf-8", "utf-8-sig"):
        with io.TextIOWrapper(io.BytesIO(raw), encoding=read_encoding,
                              newline="") as f:
            reader = csv.DictReader(f)
            row = next(reader)
            first = reader.fieldnames[0]
        assert row[first] == "00123"
        assert row["name"] == "서울"
        assert row["note"] == 'comma, quote "yes"'
        print(write_encoding, read_encoding, repr(first), "id" in row)

Choose the producer and consumer settings separately

• A documented data pipeline: follow its required encoding. If you control both sides and need no signature, plain UTF-8 on both sides passed this test.
• A Python importer accepting either UTF-8 variant: utf-8-sig passed both inputs. This is a narrow compatibility choice, not an encoding detector.
• A CSV intended for normal opening in Excel: Microsoft says UTF-8 CSV files can open normally when saved with a BOM. It also describes an import route for files without one. Test the intended Excel version and import workflow before promising compatibility.

Sources: Microsoft: opening UTF-8 CSV files in Excel; Python: UTF-8 signature codec.

Writing a new CSV for an Excel recipient

import csv

with open("export-for-excel.csv", "w", encoding="utf-8-sig", newline="") as f:
    writer = csv.writer(f)
    writer.writerow(["id", "name", "note"])
    writer.writerow(["00123", "서울", 'comma, quote "yes"'])

Run this in a folder where that output name is unused: write mode replaces an existing file. For a consumer requiring no BOM, change only the encoding to utf-8. The standard-library CSV writer handles the comma and embedded quotes; avoid building rows with a plain string join.

Limits: encoding is only one part of CSV compatibility

The Python result 00123 does not establish what a spreadsheet will infer when opening the file. This experiment tests decoding and CSV parsing, not spreadsheet cell types. We also checked that both readers reject an invalid UTF-8 byte, and that a U+FEFF inside a cell survives BOM-aware decoding. Delimiters, schema validation, and spreadsheet import choices remain separate decisions.

Already debugging a failed header lookup? Use the focused Python CSV KeyError diagnosis and fix to distinguish an initial BOM from whitespace or a wrong delimiter.

Python CSV KeyError: 'id' — Diagnose and Fix a UTF-8 BOM Header

If Python raises KeyError: 'id' even though the CSV appears to start with id, inspect the header before changing your data. A UTF-8 byte order mark (BOM) can turn that first key into '\ufeffid'. For that specific case, read the file with encoding="utf-8-sig".

The same bytes produce different first-header keys. Diagram based on the verified Python example.

This walkthrough reproduces the failure and the fix using Python 3.12.13 on macOS, tested on September 6, 2026. The example uses synthetic data and only the standard library.

Quick diagnosis

Check the first header with repr(). If it is \ufeffid, use utf-8-sig. If it is id;name or id, follow the delimiter or whitespace checks below.

1. See the actual key, including invisible characters

Run this against your CSV. The repr() output makes the invisible prefix visible. Reading the first three bytes is a separate check: ef bb bf identifies the UTF-8 signature at the start of this file.

from pathlib import Path
import csv

path = Path("input.csv")
with path.open("rb") as f:
    print("First bytes:", f.read(3).hex(" "))
with path.open(encoding="utf-8", newline="") as f:
    reader = csv.DictReader(f)
    print("Headers:", [repr(x) for x in (reader.fieldnames or [])])

2. Reproduce the error without downloading a file

import csv
import io

raw = "id,name\r\n00123,서울\r\n".encode("utf-8-sig")
with io.TextIOWrapper(io.BytesIO(raw), encoding="utf-8", newline="") as f:
    row = next(csv.DictReader(f))
    print(repr(next(iter(row))))
    try:
        print(row["id"])
    except KeyError as error:
        print(type(error).__name__, str(error))

Observed output:

'\ufeffid'
KeyError 'id'

The row exists. The lookup fails because id and \ufeffid are different strings. In this reproduction, neither a missing row nor damaged Korean text causes the error.

3. Read the BOM-aware way and validate the header

import csv

with open("input.csv", encoding="utf-8-sig", newline="") as f:
    reader = csv.DictReader(f)
    headers = reader.fieldnames or []
    if "id" not in headers:
        raise ValueError(f"Expected an id column; got {headers!r}")
    for row in reader:
        print(row["id"])

For the sample above, the value is 00123. Our test also confirmed that the same reader handles the UTF-8 sample without a BOM. It does not turn the identifier into a number.

Python documents how utf-8-sig skips an initial UTF-8 signature. Its CSV documentation explains header-based dictionaries and the recommended newline="" file setting.

4. If it still fails, follow the header evidence

• ['id;name']: your comma reader sees one column. If the file uses semicolons, pass delimiter=";".
• [' id', 'name']: the first header has a space. Confirm the intended schema before trimming or renaming columns.
• ['ID', 'name']: the spelling differs by case. Use the actual key or an explicit, checked mapping.
• An empty list: there is no header to map. Check whether the file is empty or whether the export failed.

The wrong-delimiter and leading-space cases were included in our tests: switching to utf-8-sig did not fix either one. Avoid silently falling back to row.get("id", "") before diagnosing the schema; that would hide the failed lookup.

What this fix does not do

It does not detect every encoding, repair invalid UTF-8 bytes, or remove a U+FEFF character embedded in a cell. We tested invalid UTF-8 and an interior U+FEFF separately: decoding failed for the former and preserved the latter. Keep the original export while diagnosing the problem.

For choosing the encoding of a new export, see UTF-8 vs UTF-8 BOM for CSV: a tested Python read/write matrix.