TopolearnMojibake column names and broken accents are the most common CSV support tickets.
CSV files have no way to declare their encoding, so you must know or guess it.
Reading:
- Modern files are usually UTF-8. Files saved by Excel as "CSV UTF-8" start with a byte order mark (BOM). Opened as
utf-8, the first header becomes'\ufeffname'androw["name"]raisesKeyError. Useencoding="utf-8-sig", which strips a BOM if present and works fine without one. - Older Windows exports may be
cp1252; some systems producelatin-1. AUnicodeDecodeErroris a strong hint. errors="replace"keeps a script running on bad bytes, at the cost of\ufffdcharacters; log when it happens.
Writing for Excel:
- Write
encoding="utf-8-sig"so Excel detects UTF-8; without the BOM, Excel on Windows often assumes the local code page and shows accents as garbage. - Excel in many European locales expects
;as the delimiter and,as the decimal separator. - Excel interprets values: long numbers lose digits, leading zeros vanish, and
2-3may become a date. That's display-side; the file is fine.
Security: a field starting with =, +, - or @ may be executed as a formula when opened in a spreadsheet (CSV injection). If users supply values for exports, prefix such values with '.
Example
import csv
with open("excel.csv", "w", newline="", encoding="utf-8-sig") as f: # writes BOM
csv.writer(f).writerows([["name", "city"], ["José", "Zürich"]])
print(open("excel.csv", "rb").read()[:4]) # b'\xef\xbb\xbfn' (BOM + 'n')
with open("excel.csv", newline="", encoding="utf-8") as f:
print(next(csv.DictReader(f)).keys()) # dict_keys(['\ufeffname', 'city'])
with open("excel.csv", newline="", encoding="utf-8-sig") as f:
print(next(csv.DictReader(f))) # {'name': 'José', 'city': 'Zürich'}
def safe_cell(value: str) -> str:
return "'" + value if value[:1] in ("=", "+", "-", "@") else value
print(safe_cell("=HYPERLINK(\"http://evil\")")) # '=HYPERLINK("http://evil")Remember
- Read with utf-8-sig to strip an Excel BOM automatically
- Write utf-8-sig when the file is meant for Excel
- Neutralise leading = + - @ in user data to prevent CSV injection
Watch out for
- Reading an Excel UTF-8 export with encoding='utf-8' and getting KeyError on the first column.