Remove BOM from CSV file

What is a BOM and why does it break things?

A byte-order mark (BOM) is a hidden sequence of three bytes — 0xEF 0xBB 0xBF — that Microsoft Excel prepends to UTF-8 CSV exports as a signal to other Microsoft tools that the file is UTF-8. The problem: this sequence is invisible in text editors and spreadsheet apps, but it appears as literal characters  or  at the very start of the file when processed by tools that don't understand it.

Concretely, a CSV that starts with id,name,email becomes id,name,email after Excel exports it. Python's built-in csv.reader reads the first column header as id rather than id, so row["id"] returns nothing and row["id"] returns the value. PostgreSQL's COPY FROM command errors with "invalid input syntax". Ruby's CSV library raises a parse error. Pandas silently creates a column named id that doesn't match any downstream key.

When does Excel add the BOM?

Excel adds the BOM when you use File → Save As → CSV UTF-8 (with BOM) — the option that explicitly says "UTF-8" in its name on Windows. The plain "CSV" option saves in the system codepage (usually Windows-1252) without a BOM, which causes a different set of encoding problems. There is no Excel option that saves UTF-8 without a BOM; you need an external tool to strip it.

Google Sheets, LibreOffice Calc, and Numbers do not add a BOM. The issue is specific to Microsoft Excel on Windows.

Quick fix in Python, if you prefer code

Use encoding="utf-8-sig" when opening the file — Python strips the BOM automatically:

import csv

with open("data.csv", encoding="utf-8-sig") as f:
    reader = csv.DictReader(f)
    for row in reader:
        print(row["id"])  # works correctly

For a no-code solution, or if you're working with XLSX or need additional cleanup at the same time, use the tool above.

Frequently asked questions