CSV import fails on the first column: the UTF-8 BOM from Excel

Our B2B scheduling product lets office managers bulk-invite staff by uploading a CSV with email, first_name, last_name and team. One Tuesday support escalated eleven tickets from the same morning: "Your importer says my file has no email column. It does." The template they downloaded from our help centre worked. The file they edited and saved from Excel did not.

The worker log was short:

Traceback (most recent call last):
  File "/app/importer/staff.py", line 48, in parse_rows
    email = row["email"].strip().lower()
KeyError: 'email'

A second, smaller group of uploads failed earlier, before any row was parsed:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe9 in position 2117: invalid continuation byte

Reproducing it

We opened our own template in Excel for Mac, typed one row, and used File → Save As → CSV UTF-8 (Comma delimited). Uploading that file failed with the KeyError every time. Printing the parsed header instead of trusting our eyes showed the problem immediately:

>>> import csv
>>> with open("staff.csv", newline="", encoding="utf-8") as f:
...     print(csv.DictReader(f).fieldnames)
...
['email', 'first_name', 'last_name', 'team']

The first column name was not email. It was email, which prints exactly like email in logs and in most terminals.

The UnicodeDecodeError reproduced on a Windows machine with the plain CSV (Comma delimited) format and a staff member called Hélène: byte 0xe9 is é in Windows-1252, and it is not valid UTF-8 in that position.

What we ruled out

• Delimiter detection. Some locales save CSV with semicolons, but csv.Sniffer and a manual look at the bytes both showed commas.
• Whitespace in headers. We already called .strip() on header names. str.strip() does not remove U+FEFF because it is not classified as whitespace.
• A recent change in our code. The importer had not changed in months. What changed was that a large customer rolled out a new Excel version that defaulted to the UTF-8 CSV format.

Root cause

Excel's "CSV UTF-8" format writes a UTF-8 byte order mark, the three bytes EF BB BF, at the start of the file. We opened files with encoding="utf-8", which in Python keeps the BOM as the character U+FEFF, so it became part of the first header name and every lookup by "email" failed. Files saved in the legacy CSV format contained Windows-1252 bytes, which strict UTF-8 decoding rejected. Our importer assumed one encoding for a format that does not define one.

What is happening under the hood

CSV has no encoding header. RFC 4180 describes the structure and leaves the character set to the media type parameter, which a file on disk does not carry. Each producer picks:

A byte order mark is meaningless for UTF-8 (there is no byte order to signal) but Windows tools use it as a signature that says "this is UTF-8, not the local code page". Python offers two codecs: utf-8 decodes the BOM into U+FEFF and keeps it, and utf-8-sig removes it if present and otherwise behaves exactly like utf-8. On write, utf-8-sig adds the BOM, which is useful when you want Excel to open your exports with accents intact.

Environment

Minimal reproduction

import csv
import io

data = b"\xef\xbb\xbfemail,first_name\r\nana@example.com,Ana\r\n"

reader = csv.DictReader(io.StringIO(data.decode("utf-8")))
row = next(reader)
print(list(row))   # ['email', 'first_name']
print(row["email"])  # KeyError: 'email'

Who is affected

Any importer in any language that reads text as UTF-8 without stripping a BOM: Python with encoding="utf-8", Node.js reading a Buffer with toString("utf8"), Go's encoding/csv, PHP's fgetcsv. Java's UTF-8 decoder also keeps the BOM. Pandas users hit the same thing as a column named 'email'. The legacy CP1252 problem only appears with non-ASCII characters, so it passes every test with English sample names.

The fix

We replaced "assume UTF-8" with an explicit decoding step that runs once on the raw bytes, then normalised header names so invisible characters cannot hide in them.

import codecs
import csv
import io
import unicodedata

FALLBACK = "cp1252"


def decode_upload(raw: bytes) -> tuple[str, str]:
    """Return (text, encoding_used). Never guesses silently."""
    if raw.startswith(codecs.BOM_UTF8):
        return raw.decode("utf-8-sig"), "utf-8-sig"
    if raw.startswith((codecs.BOM_UTF16_LE, codecs.BOM_UTF16_BE)):
        return raw.decode("utf-16"), "utf-16"
    try:
        return raw.decode("utf-8"), "utf-8"
    except UnicodeDecodeError:
        # Legacy Excel "CSV (Comma delimited)" on Western Windows locales.
        return raw.decode(FALLBACK), FALLBACK


def normalise_header(name: str) -> str:
    name = unicodedata.normalize("NFC", name)
    name = name.replace("", "").strip().lower()
    return name.replace(" ", "_")


def parse_rows(raw: bytes):
    text, encoding = decode_upload(raw)
    reader = csv.DictReader(io.StringIO(text, newline=""))
    reader.fieldnames = [normalise_header(h) for h in reader.fieldnames or []]
    missing = {"email", "first_name"} - set(reader.fieldnames)
    if missing:
        raise ValueError(f"Missing columns: {', '.join(sorted(missing))} (decoded as {encoding})")
    yield from reader

The diff at the old call site was small:

-with io.TextIOWrapper(blob, encoding="utf-8", newline="") as f:
-    for row in csv.DictReader(f):
+for row in parse_rows(blob.read()):

Two details matter. First, the UTF-8 attempt comes before the CP1252 fallback: valid UTF-8 with accents would decode "successfully" as CP1252 into mojibake (Hélène), so the order prevents silent corruption. Second, the error message now names the columns we expected and the encoding we used, which turned several support tickets into self-service fixes.

Alternatives and trade-offs

1. encoding="utf-8-sig" everywhere. The one-line fix for the BOM. It is safe for files without a BOM, but it does nothing for CP1252 uploads.
2. A detection library such as charset-normalizer. Useful when you receive many encodings (Shift_JIS, Windows-1250), but detection on short files is a statistical guess. We preferred an explicit, small set of supported encodings and a clear error.
3. Ask users to pick the encoding in the upload form. Accurate but confusing for office managers; we show it only as an "advanced" override.
4. Pandas: pd.read_csv(path, encoding="utf-8-sig") handles the BOM; the same CP1252 caveat applies.

How to test the fix

We added fixtures that are generated as bytes, so an editor cannot "helpfully" strip the BOM:

import codecs

import pytest

from importer.staff import parse_rows

ROWS = "email,first_name\r\nhelene@example.com,Hélène\r\n"


@pytest.mark.parametrize("raw", [
    codecs.BOM_UTF8 + ROWS.encode("utf-8"),
    ROWS.encode("utf-8"),
    ROWS.encode("cp1252"),
    ROWS.encode("utf-16"),  # Python adds the BOM for "utf-16"
])
def test_parses_every_supported_encoding(raw):
    row = next(parse_rows(raw))
    assert row["email"] == "helene@example.com"
    assert row["first_name"] == "Hélène"

pytest tests/importer/test_staff_encodings.py -q

Confirming in production

We re-queued the 31 failed imports from that week. All 31 completed; 24 were decoded as utf-8-sig, 6 as cp1252 and 1 as plain utf-8 (it had failed for an unrelated missing column, which the new error message explained). We log the encoding used per upload as a structured field, so a dashboard now shows the mix: over the following month roughly 58 percent of uploads carried a BOM, which told us this was never an edge case.

We spot-checked accented names in the staff table for the re-run imports to confirm there was no mojibake such as é.

Regression guard

• The parametrised test above runs in CI with real byte fixtures for each producer.
• A lint check fails the build if encoding="utf-8" appears in the importer package outside decode_upload, because that is the exact line that caused the bug.
• An alert fires if the share of imports failing with "Missing columns" rises above 5 percent in a day, which is how we would notice a new producer format.

Security implications

Decoding does not change the attack surface by itself, but importers are a common entry point:

• Cap the upload size before reading it into memory; raw.decode() holds the whole file.
• Treat cell values as untrusted. If you ever export them again to CSV for Excel users, escape values starting with =, +, - or @ to avoid CSV formula injection.
• Do not echo raw file content into error messages shown to other users; we only report column names.

Prevention checklist

• Decide which encodings you support and decode bytes explicitly, once, at the boundary.
• Strip U+FEFF and normalise header names before matching them.
• Try strict UTF-8 before any single-byte fallback to avoid silent mojibake.
• Test with BOM, CP1252 and non-ASCII fixtures, not just English sample data.
• Log the detected encoding per import so the real-world mix is visible.

References

• Python documentation, codecs module and the utf-8-sig codec — https://docs.python.org/3/library/codecs.html
• Python documentation, csv module — https://docs.python.org/3/library/csv.html
• RFC 4180, Common Format and MIME Type for CSV Files — https://www.rfc-editor.org/rfc/rfc4180
• Unicode FAQ, UTF-8, UTF-16, UTF-32 and BOM — https://www.unicode.org/faq/utf_bom.html
• OWASP, CSV Injection — https://owasp.org/www-community/attacks/CSV_Injection