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.