Why Your CSV Has Question Marks Where Names Should Be

Names like ?? ?? in a customer export are not a display problem, and nothing you do to the CSV will bring the characters back. Somewhere between the database and the file, a program was asked to write characters into an encoding that had no place for them, and it wrote ? instead. This article shows where that substitution happens, measures the damage on a test table, and separates it from the decoding failures that look similar but can be repaired. The wider picture is in the guide to garbled text.

Where the question marks come from

Every encoding covers a limited set of characters. ASCII covers 128, Latin 1 covers 256, and Windows 1252 covers 256 with a different set in the upper part. A program that writes text to a file in one of those encodings has to decide what to do with a character outside the set, and the most common decision is to write a question mark. Python calls it errors="replace", one of several error handlers that include ignore, which drops the character entirely, and xmlcharrefreplace, which writes ㊣ style references. Database client libraries, export wizards and legacy reporting tools do the same without mentioning it.

The substitution is a one way trip. The byte written is 3F, the same for every character that could not be encoded. After that there is nothing in the file to tell 田 from Ł from €. I ran three encodings over one line of the test table:

python - <<'PY'
text = "1,José Müller,田中 太郎,€4.50"
for enc in ("latin-1", "cp1252", "ascii"):
    data = text.encode(enc, errors="replace")
    print(f"{enc:8} -> {data.decode(enc)!r:40} {data.hex(' ')[:60]}")
PY

latin-1  -> '1,José Müller,?? ??,?4.50'              31 2c 4a 6f 73 e9 20 4d fc 6c 6c 65 72 2c 3f 3f 20 3f 3f 2c 
cp1252   -> '1,José Müller,?? ??,€4.50'              31 2c 4a 6f 73 e9 20 4d fc 6c 6c 65 72 2c 3f 3f 20 3f 3f 2c 
ascii    -> '1,Jos? M?ller,?? ??,?4.50'              31 2c 4a 6f 73 3f 20 4d 3f 6c 6c 65 72 2c 3f 3f 20 3f 3f 2c 

The same line came out three ways. Latin 1 kept José Müller and replaced the Japanese name and the euro sign. Windows 1252 kept the euro sign, since it has a place for it at byte 80, and replaced the Japanese. ASCII lost the accents as well. The hex shows the same byte 3F standing in for different characters.

Count the damage

A quick way to know how bad it is: count question marks in the source and in the export. A column that never had a ? and now has many is a loss:

cd enc && python - <<'PY'
import re
raw = open("utf8.csv", "rb").read().decode("utf-8")
lossy = raw.encode("latin-1", errors="replace").decode("latin-1")
print("question marks in the original  :", raw.count("?"))
print("question marks after the export :", lossy.count("?"))
print("rows with a ? between letters   :", [l for l in lossy.splitlines() if re.search(r"\w\?\w|\?\?", l)][:3])
PY

question marks in the original  : 0
question marks after the export : 16
rows with a ? between letters   : ['4,?? ??,??,???', '5,?ukasz Nowak,Kraków,za?ó??']

The original had none and the exported version had 16. The lines with ?? or a ? between letters are the signature, such as 5,?ukasz Nowak,Kraków,za?ó??, where the Polish Ł, ż, ł and ć disappeared and ó survived because Latin 1 has it. That pattern, a mix of intact accents and holes, is what a narrower encoding leaves. If the damage is all question marks and the rest is clean ASCII, it points at an ASCII export.

Do not use this count as a detector on data that really contains question marks, such as free text, URLs or survey answers. Compare the same field before and after, and look at which rows changed.

A question mark is not a replacement diamond

People report both as "weird characters", and they come from opposite directions. A question mark is written by an encoder that could not represent a character. The replacement character U+FFFD, shown as a black diamond with a question mark, is produced by a decoder that met bytes that were invalid in the encoding it was told to use. The first is permanent. The second is only a display of bytes that may still be in the file, so decoding with the right encoding can recover them, as the UTF-8 validity check and the mojibake article show.

The practical test: open the file in a hex viewer and look at the suspicious byte. 3F is a literal question mark. EF BF BD is the replacement character already saved to disk, which is also permanent, because someone wrote the diamond into the file. Any other byte is still the original, and the file needs only the correct decoder.

So which one is broken

Three places can introduce the narrowing, and the decision is which to fix.

The export step, if it writes to Latin 1 or ASCII by configuration or by default. This is the easiest to fix: set it to UTF-8, or to Windows 1252 if the consumer needs a legacy encoding and the data fits. Many database tools and reporting programs have a code page setting in a hidden dialog.

The connection, if a client sees the data in a narrower encoding than the database stores. A client library configured for Latin 1 asks the server to convert, and the server substitutes characters on the way out. The source data is fine, and re-exporting with a UTF-8 connection fixes it. This is often the real cause when the database holds correct text.

The source itself, if the data was already stored with question marks, for example because a previous import went through a narrow code page. Then the characters are gone and the remedy is re-entry or restoring from the original system.

To find out which, compare one affected record in the source database with its exported form. If the database shows 田中 太郎 and the file shows ?? ??, the export or connection is at fault. If the database shows question marks, so was an earlier step.

The decision: UTF-8 or a legacy encoding

If you control both ends, export UTF-8 and move on; nothing else carries all of Unicode without loss. If a consumer demands a legacy encoding, choose the closest one to its data. Windows 1252 covers more punctuation than Latin 1, which matters for curly quotes and the euro sign, as the conversion article shows. Then count: a legacy export should come with a check that the number of question marks did not rise. I would also keep the UTF-8 original, since the legacy file cannot be turned back into it.

If the downstream system truly cannot take non-Latin text, transliteration is an option, converting 田中 to Tanaka with a library, which changes the data deliberately and visibly instead of by accident. It is a different decision from encoding and should be documented as one.

Preventing it in code

If you write the export yourself, make the failure loud. Python's default for encode is errors="strict", which raises UnicodeEncodeError on the first character the target cannot hold, and that exception is the right behavior for a data pipeline: it stops the job, names the character and the position, and nobody ships a file with holes. The damage in the earlier example only happened because I asked for replace. If you inherit code that uses replace or ignore, search for it, since each use is a place where data can disappear without anyone being told.

For exports that really must be Latin 1 or Windows 1252, check each text value for encodability before writing, and route rejects to a log: value.encode("cp1252") inside a try is enough. A file with five rejected rows and a log line is a much better deliverable than a file with five rows that silently lost their names. The PostgreSQL manual on character set support lists which conversions between server and client encodings exist, and it is worth reading before configuring a client encoding narrower than the database.

When the question marks are real

Free text fields, survey answers and URLs contain real question marks, so a global replace of ? is wrong. A file that has a question mark only in places where a letter would be impossible, such as inside a word, is damaged; a file whose question marks end sentences is not. And if the question marks appear only in your terminal or only in one tool, the file may be fine: print the bytes with xxd first. The strange characters article lists the other symptoms and what each means.