The UTF8 BOM in CSV Files and When It Helps or Hurts

A byte order mark is the strangest thing in a CSV file: three bytes nobody sees, which either make Excel read the file correctly or make your loader fail on a column name that looks right on screen. The argument over whether to write one never ends, because both sides are correct for their own consumers. This article shows what the three bytes are, what five readers do with them, and how to add or remove one safely. It belongs with the other encoding issues covered in the guide to garbled text.

What the three bytes are

The Unicode character U+FEFF, called the byte order mark, was designed for UTF-16 and UTF-32, where the order of the bytes in each unit matters and the mark tells the reader which order was used. UTF-8 has no such order, so a BOM there carries no information about byte order. Some programs write it anyway as a signature meaning "this is UTF-8", and that signature is the only reason it survives. Encoded in UTF-8 it is EF BB BF.

Here are the first bytes of two otherwise identical files, and what file makes of them:

cd enc && head -c 16 utf8.csv | xxd; head -c 16 utf8-bom.csv | xxd; file utf8.csv utf8-bom.csv

00000000: 6964 2c6e 616d 652c 6369 7479 2c6e 6f74  id,name,city,not
00000000: efbb bf69 642c 6e61 6d65 2c63 6974 792c  ...id,name,city,
utf8.csv:     CSV text
utf8-bom.csv: CSV text

The second file starts with ef bb bf before id. Notice that file calls both of them plain CSV text and does not mention the BOM at all. You cannot rely on it to tell you.

What readers do with it

I opened the BOM file with five readers and looked at the name of the first column. Python is the one that surprises people:

cd enc && python - <<'PY'
import csv
for enc in ("utf-8", "utf-8-sig"):
    with open("utf8-bom.csv", newline="", encoding=enc) as f:
        header = next(csv.reader(f))
    print(f"{enc:10} header[0] = {header[0]!r}  equals 'id': {header[0] == 'id'}")
PY

utf-8      header[0] = '\ufeffid'  equals 'id': False
utf-8-sig  header[0] = 'id'  equals 'id': True

With encoding="utf-8" the first header is 'id', which prints as id in most terminals and compares as not equal to 'id'. A script that does row["id"] gets a KeyError on a file that looks right in every viewer. The codec utf-8-sig exists for this: it strips a BOM on reading if there is one and does nothing otherwise, so it is the safest way to open a CSV of unknown origin.

The other readers handle it quietly:

cd enc && python - <<'PY'
import pandas as pd
print("pandas      :", pd.read_csv("utf8-bom.csv").columns[0].encode())
PY
duckdb -list -noheader -c "SELECT 'duckdb      : ' || column_name FROM (DESCRIBE SELECT * FROM read_csv('utf8-bom.csv')) LIMIT 1"
sqlite3 :memory: ".mode csv" ".import utf8-bom.csv t" "SELECT 'sqlite      : ' || name FROM pragma_table_info('t') LIMIT 1"

pandas      : b'id'
duckdb      : id
"sqlite      : id"

pandas, DuckDB and the SQLite shell all returned id as the first column name. That is convenient and it hides the question for anyone who tests with only one of them. PostgreSQL is different, and its behavior depends on what you ask it to do with the header line. With HEADER match, which compares the file's header to the table's columns, the BOM makes the first name differ:

DROP TABLE
CREATE TABLE
ERROR:  column name mismatch in header line field 1: got "id", expected "id"
CONTEXT:  COPY bomtest, line 1: "id,name,city,note"

The error message prints got "id" with the invisible character inside the quotes, which makes it a confusing thing to read. With plain HEADER true, PostgreSQL discards the first line without looking at it, and the load succeeded with five rows:

COPY 5

When a BOM helps

The one case where a BOM is the right thing to write is a file that will be opened in Excel on Windows by a person who double clicks it. Without a marker, Excel falls back to the system's legacy code page, so a UTF-8 file with accents opens garbled. With the BOM, Excel recognizes UTF-8. I did not have desktop Excel to test on, and this behavior is widely reported by users, not described in Microsoft's import documentation, so confirm it with the version your readers use. The step by step is in opening a UTF-8 CSV in Excel without garbled text.

For every other consumer, the BOM is noise. Tools that parse headers, scripts that compare the first field, and command line programs that concatenate files (a BOM in the middle of a combined file is a bug of its own) are better off without it.

Adding and removing one

To add a BOM to a file on the command line, put the three bytes in front:

cd enc && printf '\xEF\xBB\xBF' | cat - utf8.csv > /tmp/with-bom.csv && head -c 6 /tmp/with-bom.csv | xxd
python -c "
import csv
with open('/tmp/py-bom.csv', 'w', newline='', encoding='utf-8-sig') as f:
    csv.writer(f).writerow(['id', 'name'])
print(open('/tmp/py-bom.csv', 'rb').read())"

00000000: efbb bf69 642c                           ...id,
b'\xef\xbb\xbfid,name\r\n'

In Python, write with encoding="utf-8-sig" and the codec adds the BOM at the start of the file, once. The second command above shows it: b'\xef\xbb\xbfid,name\r\n'.

To remove one, read with utf-8-sig and write with utf-8, or use sed '1s/^\xEF\xBB\xBF//' for a single file. The sed form touches only the first line and is safe on any file whose first line starts with those bytes. I prefer the Python route when I am already parsing the file, because it handles the case where the BOM is absent without a conditional.

One caution: a pipeline that concatenates files with cat puts a BOM from each file into the middle of the output, and readers treat it as part of a field. Strip the BOM from every file before combining them, or combine through a parser.

What the standards say

The Unicode Consortium's FAQ on UTF-8, UTF-16 and BOMs says plainly that a UTF-8 file may contain a BOM, that it only serves as a signature for an otherwise unmarked file, and that some recipients do not expect it. It also names the case where it breaks things: any format that expects specific ASCII characters at the very start.

JSON is the cleanest example. RFC 8259 says implementations must not add a byte order mark to the start of a JSON text sent over a network, and that a parser may ignore one. A parser that does not ignore it fails on the first character, which is a common cause of mysterious Unexpected token errors on files exported from Windows. CSV has no such rule, since no document governs it, and each reader decides, which is the reason for the table above. If you are writing for several consumers, the safest choice is no BOM and a documented encoding, and the exception is the Excel case.

When the BOM is not the reason

If the first column name is wrong but contains no invisible character, look elsewhere: a stray space, a quote, or a header that is different from what you expected. A quick way to find out is to print the first header with repr() in Python or pipe the file through xxd | head. If the bytes are not EF BB BF, there is no BOM and this article is not your problem.

UTF-16 files have their own marker, FF FE or FE FF, and need a different fix: read them with encoding="utf-16", which consumes the marker and picks the byte order. If a file starts with those bytes, the encoding itself is wrong for a UTF-8 reader, and the encoding detection article shows how to confirm it.