Python Doc & Data Automation

Fix UnicodeEncodeError Writing CSV

The export runs on a laptop and fails in the scheduled job:

UnicodeEncodeError: 'charmap' codec can't encode character '–' in position 18: character maps to <undefined>
UnicodeEncodeError: 'ascii' codec can't encode character 'ü' in position 7: ordinal not in range(128)
UnicodeEncodeError: 'latin-1' codec can't encode character '€' in position 12: ordinal not in range(256)

The three messages name three different codecs, and none of them is one the code asked for — the export line is a plain frame.to_csv(path, index=False).

Root Cause

When to_csv is given no encoding, pandas writes with the platform's preferred encoding. On Linux and macOS that is normally UTF-8, which can represent any character; on Windows it is the legacy ANSI code page, cp1252 in Western Europe, whose codec is called charmap. In a container or a service started without a locale, it can be ascii. Those encodings cover a few hundred characters, so the first en dash, euro sign, Polish ł, Turkish İ or Chinese character raises UnicodeEncodeError — and only when a row containing one is exported, which is why the job worked for months and then failed. Explicitly choosing latin-1 has the same problem for characters outside its 256: the euro sign, curly quotes and en dashes that word processors and web forms produce routinely. Writing the file is not the only place this surfaces: printing to a redirected console, writing JSON or sending an email attachment name can raise the same error for the same reason.

Minimal Diagnostic

Find which characters cannot be encoded, in which columns and rows, before deciding what to do about them.

# pip install "pandas>=2.2"
import sys
import unicodedata
from collections import Counter
from pathlib import Path
import pandas as pd

TARGET_ENCODING = "cp1252"          # the encoding the recipient requires

def encoding_report(frame: pd.DataFrame, encoding: str = TARGET_ENCODING, examples: int = 5) -> None:
    print(f"python default encoding: {sys.getdefaultencoding()}, "
          f"locale preferred: {__import__('locale').getpreferredencoding(False)}, "
          f"filesystem: {sys.getfilesystemencoding()}")
    bad_chars: Counter = Counter()
    rows_affected: dict[str, list[int]] = {}
    for col in frame.select_dtypes(include=["object", "string"]).columns:
        for idx, value in frame[col].dropna().astype(str).items():
            try:
                value.encode(encoding)
            except UnicodeEncodeError as exc:
                for ch in value[exc.start:exc.end] or value:
                    try:
                        ch.encode(encoding)
                    except UnicodeEncodeError:
                        bad_chars[ch] += 1
                rows_affected.setdefault(col, []).append(idx)
    print(f"characters not representable in {encoding}:")
    for ch, count in bad_chars.most_common(examples):
        print(f"  {ch!r} U+{ord(ch):04X} {unicodedata.name(ch, '?'):<30} x{count}")
    for col, rows in rows_affected.items():
        print(f"  column {col!r}: {len(rows)} row(s), first at index {rows[0]}")

if __name__ == "__main__":
    frame = pd.read_csv(Path("in/customers.csv"), dtype="string", encoding="utf-8")
    encoding_report(frame)

python default encoding: utf-8, locale preferred: cp1252, filesystem: utf-8
characters not representable in cp1252:
  '–' U+2013 EN DASH                       x412
  '€' U+20AC EURO SIGN                     x88
  'ł' U+0142 LATIN SMALL LETTER L WITH STROKE   x31
  'İ' U+0130 LATIN CAPITAL LETTER I WITH DOT ABOVE  x9
  '中' U+4E2D CJK UNIFIED IDEOGRAPH-4E2D        x4

The locale's preferred encoding is cp1252, which is what the failing job used. Four kinds of characters cannot be represented, and each needs a decision: en dashes and euro signs can be mapped to ASCII equivalents, Polish and Chinese characters cannot without losing meaning.

Choosing what to do about an unencodable character The root asks whether the recipient can accept UTF-8. If yes, the fix is to write UTF-8 with a BOM for Excel users and nothing else changes. If the recipient's system requires a legacy encoding, typographic punctuation such as en dashes and curly quotes is mapped to ASCII equivalents, accented names are transliterated only where the recipient agrees, and characters that carry meaning, such as CJK text, cause the export to fail with a clear message rather than be silently replaced. Can the recipient accept UTF-8? ask before transliterating anything yes encoding='utf-8-sig' nothing is lost no, punctuation Map to ASCII en dash to hyphen, euro to EUR no, names Transliterate only if agreed no, CJK or unclear Fail the export escalate

Fix: Choose the Encoding Explicitly and Map What It Cannot Represent

Never rely on the platform default. Write UTF-8 where the recipient allows it, and where a legacy encoding is mandatory, transform the text deliberately before writing. Changed lines carry comments.

# pip install "pandas>=2.2"
import unicodedata
from pathlib import Path
import pandas as pd

PUNCTUATION_MAP = {                                    # changed: typographic to ASCII, meaning preserved
    chr(0x2013): "-", chr(0x2014): "-", chr(0x2010): "-", chr(0x2011): "-",   # dashes
    chr(0x2018): "'", chr(0x2019): "'", chr(0x201A): "'",                     # single quotes
    chr(0x201C): '"', chr(0x201D): '"', chr(0x201E): '"',                     # double quotes
    chr(0x2026): "...", chr(0x00A0): " ", chr(0x202F): " ",                   # ellipsis, NBSPs
    chr(0x20AC): "EUR", chr(0x00A3): "GBP", chr(0x2122): "(TM)", chr(0x00AE): "(R)",
}

def make_encodable(text: str, encoding: str, transliterate: bool) -> tuple[str, list[str]]:
    """Return text that encodes cleanly, plus the characters that were changed."""
    changed: list[str] = []
    out = []
    for ch in text:
        try:
            ch.encode(encoding)
            out.append(ch)
            continue
        except UnicodeEncodeError:
            pass
        if ch in PUNCTUATION_MAP:                                   # changed: safe, meaning-preserving
            out.append(PUNCTUATION_MAP[ch])
            changed.append(ch)
            continue
        if transliterate:                                            # changed: opt-in, lossy
            folded = unicodedata.normalize("NFKD", ch)
            ascii_only = "".join(c for c in folded if not unicodedata.combining(c))
            try:
                ascii_only.encode(encoding)
                out.append(ascii_only)
                changed.append(ch)
                continue
            except UnicodeEncodeError:
                pass
        raise UnicodeEncodeError(encoding, text, text.index(ch), text.index(ch) + 1,
                                 f"character U+{ord(ch):04X} cannot be represented")   # changed: fail loudly
    return "".join(out), changed

def export_csv(frame: pd.DataFrame, dest: Path, encoding: str = "utf-8-sig",
               transliterate: bool = False) -> dict[str, int]:
    out = frame.copy()
    stats: dict[str, int] = {}
    if encoding.lower() not in {"utf-8", "utf8", "utf-8-sig", "utf-16", "utf-16-le"}:
        for col in out.select_dtypes(include=["object", "string"]).columns:
            cleaned = []
            for value in out[col].astype("string"):
                if pd.isna(value):
                    cleaned.append(value)
                    continue
                text, changed = make_encodable(str(value), encoding, transliterate)
                for ch in changed:
                    stats[ch] = stats.get(ch, 0) + 1
                cleaned.append(text)
            out[col] = pd.array(cleaned, dtype="string")
    dest.parent.mkdir(parents=True, exist_ok=True)
    out.to_csv(dest, index=False, encoding=encoding, errors="strict")   # changed: explicit, never silent
    return stats

if __name__ == "__main__":
    customers = pd.read_csv("in/customers.csv", dtype="string")
    print(export_csv(customers, Path("out/customers-utf8.csv")))                 # nothing changed
    print(export_csv(customers, Path("out/customers-cp1252.csv"), "cp1252"))     # raises on 'ł' and CJK

errors="strict" is the default and should stay: errors="replace" writes ? where characters were lost, and errors="ignore" deletes them — both corrupt names silently, which is exactly the failure the exception was protecting you from. Returning the map of changed characters turns the transformation into something reviewable: a spike in changed characters after a supplier's system upgrade is worth investigating.

Encodings and what they can represent ASCII represents none of the special cases. cp1252 represents Western accented characters, typographic punctuation and the euro sign, but not Polish or CJK. latin-1 represents Western accents but neither typographic punctuation nor the euro sign. UTF-8 represents everything but is misread by Excel on double-click unless a byte order mark is present. UTF-8 with BOM represents everything and opens correctly in Excel. Encoding Accents Dashes, quotes, EUR Polish, CJK Excel opens right ascii no no no yes cp1252 yes yes no yes latin-1 yes no no yes utf-8 yes yes yes mojibake utf-8-sig yes yes yes yes

Variant Fix 1: Make the Environment Deterministic

A job that behaves differently on a laptop and a server is a configuration problem as much as a code one. Force UTF-8 for the whole process so every file, log line and subprocess agrees:

# systemd unit, Dockerfile or CI environment
PYTHONUTF8=1          # Python 3.7+: UTF-8 mode, ignores the locale for I/O defaults
PYTHONIOENCODING=utf-8
LANG=C.UTF-8
LC_ALL=C.UTF-8

# stdlib only — a start-up assertion that fails fast on a misconfigured host
import locale
import sys

def assert_utf8_environment() -> None:
    problems = []
    if sys.getfilesystemencoding().lower() not in ("utf-8", "utf8"):
        problems.append(f"filesystem encoding is {sys.getfilesystemencoding()}")
    if locale.getpreferredencoding(False).lower() not in ("utf-8", "utf8"):
        problems.append(f"locale preferred encoding is {locale.getpreferredencoding(False)}")
    if problems:
        raise SystemExit("set PYTHONUTF8=1 and a UTF-8 locale: " + "; ".join(problems))

Running this check at start-up turns "the nightly export failed on row 4,812 after two hours" into "the service refused to start with a clear message", which is far cheaper. The same environment variables fix accented file names too, as described in fix encoded attachment filenames in Python.

Variant Fix 2: Reporting What Would Be Lost Before Exporting

For recurring exports to a legacy system, check the data before the run and report rows that cannot be represented, so someone can correct them at the source:

# pip install "pandas>=2.2"
import pandas as pd

def unencodable_rows(frame: pd.DataFrame, encoding: str, key_col: str) -> pd.DataFrame:
    rows = []
    for col in frame.select_dtypes(include=["object", "string"]).columns:
        for idx, value in frame[col].dropna().astype(str).items():
            try:
                value.encode(encoding)
            except UnicodeEncodeError as exc:
                rows.append({key_col: frame.at[idx, key_col], "column": col, "value": value,
                             "character": value[exc.start:exc.end],
                             "code_point": f"U+{ord(value[exc.start]):04X}"})
    return pd.DataFrame(rows, columns=[key_col, "column", "value", "character", "code_point"])

Send that list to whoever maintains the data. A customer name with a Polish ł is legitimate data that a cp1252 system cannot store; the answer is a conversation about the target system, not a transliteration decided by an export script. Where the target genuinely cannot change, agree the transliteration rules in writing and encode them in PUNCTUATION_MAP, so the same name always maps the same way.

Export with an explicit encoding contract Before export the data is checked against the target encoding and rows that cannot be represented are reported. Agreed mappings replace typographic punctuation and, where agreed, transliterate accents. The file is written with an explicit encoding in strict mode so nothing is silently replaced. The file is read back with the same encoding and compared with the source frame, and the count of changed characters is logged for review. Check encodability per column and row Report blockers to the data owner Apply agreed map punctuation, accents Write strict explicit encoding Read back compare and log

Verification

Read the written file back with the declared encoding, compare against the source, and assert that only the agreed characters changed.

# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd

def verify_encoding(source: pd.DataFrame, dest: Path, encoding: str, allowed_changes: dict[str, str]) -> None:
    back = pd.read_csv(dest, dtype="string", encoding=encoding, keep_default_na=False)
    assert list(back.columns) == list(source.columns), "columns differ"
    assert len(back) == len(source), f"{len(back)} rows read, {len(source)} written"
    for col in source.select_dtypes(include=["object", "string"]).columns:
        expected = source[col].astype("string").fillna("")
        for ch, replacement in allowed_changes.items():
            expected = expected.str.replace(ch, replacement, regex=False)
        actual = back[col].astype("string").fillna("")
        diff = expected.compare(actual)
        assert diff.empty, f"{col}: {len(diff)} unexpected change(s), e.g. {diff.head(1).to_dict()}"
    raw = dest.read_bytes()
    assert b"?" not in raw or b"?" in source.to_csv(index=False).encode(encoding, "ignore"), \
        "question marks in the output suggest characters were replaced"
    print(f"{dest.name}: round-trips in {encoding} with only the agreed substitutions")

The question-mark heuristic catches a errors="replace" slipping back into the code during a refactor — the classic way this bug returns after it has been fixed once.

FAQ

Why does the same code work on my machine? Your locale is UTF-8 and the server's is not. Set the encoding explicitly so the platform never decides.

Is latin-1 a safe fallback because it never fails on bytes? On decoding, latin-1 accepts any byte; on encoding it rejects everything above U+00FF, including the euro sign. It is not a safe default in either direction.

Should I use UTF-16 for Excel? UTF-16LE with a BOM and tab separation is an old Excel-friendly combination, but utf-8-sig is simpler and handled well by current Excel versions.

What about the file name itself? Non-ASCII file names need a UTF-8 file system encoding; see the environment variables above and fix encoded attachment filenames in Python.

Part of Exporting Data to CSV Formats.