CSV UTF-8 BOM and Excel

A CSV file is plain text plus delimiter conventions. It does not contain a standard header that says “this file is UTF-8.” The bytes must be interpreted by the program that opens it. If the program guesses wrong, the rows and columns may still split correctly while characters are damaged.

UTF-8 is the safest modern encoding for interchange, but Excel behavior depends on version, operating system, locale, and open path. Importing with an explicit encoding is more dependable than double-clicking.

A UTF-8 BOM is the byte sequence at the beginning of a file that signals UTF-8 to some programs. It is not required for UTF-8 text, and many Unix tools do not need it. Excel on Windows has historically used the BOM as a clue that a CSV should be opened as UTF-8.

The BOM can be visible to tools that do not expect it, sometimes as an extra character at the beginning of the first header name. That is why data pipelines often prefer UTF-8 without BOM, while spreadsheet handoff to Excel often uses UTF-8 with BOM.

Broken text such as é where é should appear is mojibake: bytes decoded with the wrong character set. First identify what the file actually contains. file -I data.csv may report a charset, but it is a guess for many text files.

If you know the source encoding, convert deliberately. For example, iconv -f WINDOWS-1252 -t UTF-8 legacy.csv > utf8.csv converts Windows-1252 bytes to UTF-8. Do not run random conversions repeatedly; each wrong pass can make recovery harder.

For Excel recipients, write UTF-8 with BOM and use the delimiter expected by their locale or tell them to use import. Some libraries call this utf-8-sig. In Python, df.to_csv("out.csv", index=False, encoding="utf-8-sig") writes the BOM.

If IDs, ZIP codes, and long numbers matter, encoding is only one issue. Excel may still coerce values after opening. Use XLSX for safer spreadsheet handoff when cell types and display formats matter.

A common pattern is to store canonical data as UTF-8 without BOM for systems and generate a separate Excel CSV with BOM for people. Name the files clearly so the Excel convenience copy does not become the machine input accidentally.

When converting CSV to JSON or Parquet, strip a BOM from the first header if your parser does not handle it. A hidden BOM in customer_id can create a field named customer_id, which is hard to see in logs.

When receiving files, preserve the original bytes before repairing encoding. A damaged display in Excel is not proof that the source file is wrong. If you save over it from Excel, you may replace a recoverable encoding issue with permanent character loss.

For exports, name the intended consumer in the job or filename, such as customers-excel-utf8bom.csv and customers-pipeline-utf8.csv. The contents may be almost identical, but the leading BOM changes how some tools treat the first header.

When a CSV starts with a BOM, downstream code should strip it during header normalization. Otherwise the first column name may include an invisible prefix, and joins or mappings against customer_id will fail even though the header looks right.

A final encoding policy should be written next to the export code. State whether the file is UTF-8 with BOM, UTF-8 without BOM, or a legacy code page, and test characters outside plain ASCII.