File Encoding Explained: UTF-8, ANSI, and BOM for CSV

Encoding Is How Bytes Become Characters

A CSV file is a sequence of bytes. Encoding is the map from those bytes to letters, digits, and symbols. Pick the wrong map and "é" becomes "é"—or the importer rejects the file entirely.

The Encodings You Actually Meet

You do not need every code page—just the common ones in US/UK/EU business files.

What Is a BOM?

A Byte Order Mark is a few invisible bytes at the start of a file (for UTF-8: EF BB BF). Excel often uses a UTF-8 BOM so it opens characters correctly. Some parsers then treat the first header as "\uFEFForder_id" unless BOM handling is enabled.

• Excel-friendly UTF-8 CSV → BOM can help on Windows.
• Developer pipelines → prefer UTF-8 without BOM, or strip it on read.
• Never mix "I thought it was ASCII" with a BOM-prefixed header.

Step-by-Step: Diagnose Encoding Problems

Symptoms first, then fix.

• Look for mojibake (é, ’) or replacement characters (�).
• Check whether only non-English text breaks.
• Open the file in a tool that shows encoding (editor, Online CSV Editor after re-save).
• Re-export from the source as UTF-8 when you control the export.
• If not, convert Windows-1252 → UTF-8 with a known decoder, then proceed.

# Decode legacy bytes, rewrite UTF-8
raw = open("legacy.csv", "rb").read()
text = raw.decode("cp1252")  # Windows-1252
open("utf8.csv", "w", encoding="utf-8", newline="").write(text)

Excel and Encoding

"CSV (Comma delimited)" on some Excel builds is not UTF-8. Prefer "CSV UTF-8 (Comma delimited)" when available, or convert via Excel to CSV tools that emit UTF-8 explicitly.

Encoding in Databases and APIs

Postgres COPY should set ENCODING 'UTF8'. MySQL connections need matching character_set_client / database collation. HTTP APIs should send Content-Type with charset=utf-8 for CSV downloads.

Real-World Examples

Encoding bugs in the wild.

Common Mistakes

Easy to get wrong once—and forever in a pipeline.

• Calling every non-UTF-8 file "ANSI" without checking the real code page.
• Transcoding twice (double UTF-8).
• Stripping BOM by deleting the first three characters of a non-BOM file.
• Assuming Notepad’s default save is UTF-8 on older Windows.
• Fixing display in Excel only and leaving the pipeline encoding wrong.

Best Practices

Standardize and document.

• UTF-8 for all new CSV contracts.
• Document BOM policy for Excel consumers.
• Validate a non-ASCII sample row in QA.
• Set encoding explicitly in loaders (never rely on locale guess).
• Convert legacy files once, then keep UTF-8 as source of truth.

Why Use Convert CSV Online?

Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Preview text in the Online CSV Editor, re-export Excel as UTF-8 CSV, and convert only after characters look right. Client-side workflows work on Windows, macOS, and Linux browsers.

Conclusion

File encoding is the difference between correct text and permanent corruption. Prefer UTF-8, understand BOM tradeoffs, and re-encode legacy Windows CSV files before they enter modern pipelines.