Why does my CSV turn into question marks?

Because a step in the chain stored your text in a charset that has no byte for ầ, and substituted 0x3F — the ASCII question mark — instead. Measured on MySQL 8.4.11: Trần Thị Hương written into a latin1 column comes back as Tr?n Th? H??ng, stored as 54 72 3F 6E 20 54 68 3F 20 48 3F 3F 6E 67. Fourteen bytes, where the same name in UTF-8 is twenty. The six missing bytes are not hidden or misread. They were never written.

This is the difference that decides whether your afternoon is ten minutes or two days. Trần Thị is misread text — every original byte is still there and a one-line round trip gives it back. Tr?n Th? is destroyed text, and no amount of code will bring ầ back from a ?.

Everything below was run on an Apple M3, 16 GB, macOS 26.4.1: Python 3.14.6, Node v23.5.0 (ICU 76.1), MySQL 8.4.11 in Docker. Built-ins only, offline.

The short answer

• ? means the bytes are gone. A charset conversion that cannot represent a character writes 0x3F. Vietnamese in a MySQL latin1 column loses 6 of 20 bytes and is unrecoverable.
• Ã means the bytes are fine and the label is wrong. UTF-8 bytes read as Windows-1252 give Trần Thị Hương; text.encode('windows-1252') .decode('utf-8') reverses it exactly — but only for 7 of our 16 test strings, because Windows-1252 has five undefined bytes. Repairing through latin-1 instead worked 16 of 16.
• The  at the start of your file is a UTF-8 BOM, bytes EF BB BF. It makes the first header cell 'name', five characters long, which is not equal to 'name' — so every lookup on your first column silently returns nothing.
• Encoding detection is mostly a UTF-8 validity check, and that check is strong. Of 50,000 random 32-byte strings, 0 decoded as valid UTF-8. A 14-rule detector got 10 of 11 samples right; the one it missed was short UTF-16 with no BOM.
• MySQL's utf8 is not UTF-8. utf8mb3 holds three bytes per character — enough for every Vietnamese letter, not enough for an emoji, which becomes ?? with warning 1366.

What do the same letters look like in each encoding?

One name, five encodings. Windows-1252 and ISO-8859-1 are in the table because people believe they are options; they are not.

One letter makes it clearer. ầ is U+1EA7. In UTF-8 it is E1 BA A7; in UTF-16LE it is A7 1E; in Windows-1252 and ISO-8859-1 it does not exist at all. Those two are 256-slot tables built for Western Europe, and Vietnamese needs 134 letters they never had room for. No setting makes ầ fit into one byte.

The ASCII part of the name is identical in every row — Anh is 41 6E 68 in all five. That is why the bug survives testing: ASCII test data passes everywhere, and the first real customer file breaks.

Why does it come out as Trần and not as ?

Two different failures, and the symptom tells you which one you have. ? comes from a conversion: something decoded the bytes correctly, then re-encoded them into a charset that could not hold them and substituted 0x3F. Ã comes from a mislabel: nothing converted anything, a reader just applied the wrong table to bytes that are still intact.

Trần Thị Hương   read as Windows-1252  ->  Trần Thị HÆ°Æ¡ng
Trần Thị Hương   read as ISO-8859-1    ->  Trần Thá» HÆ°Æ¡ng
Trần Thị Hương   read as ASCII         ->  UnicodeDecodeError: byte 0xE1 pos 2
Trần Thị Hương   ASCII, errors=replace ->  Tr���n Th��� H����ng
Trần Thị Hương   ASCII, errors=ignore  ->  Trn Th Hng

errors='ignore' is the quiet catastrophe on that list. It produces text that looks like a plausible unaccented Vietnamese name, passes every validation, and has thrown away 6 of 20 bytes.

Four of our eight test names could not even be read as Windows-1252: Nguyễn Văn Đức raises UnicodeDecodeError: byte 0x90 at 15, because Windows-1252 leaves five byte values undefined — 0x81 0x8D 0x8F 0x90 0x9D. That detail becomes the whole story in the repair section.

What is the  at the start of my file?

It is a UTF-8 byte order mark: EF BB BF, three bytes prepended to the file. UTF-8 has no byte order to mark — the BOM is there purely as a flag saying "this is UTF-8", and Windows tooling writes it by default.

It costs nothing until you parse a header row:

first 8 bytes    : EF BB BF 6E 61 6D 65 2C
header cell repr : 'name'   len=5
col == 'name'    : False
seen as cp1252   : 'name'
utf-8-sig decode : 'name'  == 'name': True

In Node the same file gives header[0] === 'name' → false and row['name'] → undefined. Nothing throws. Your import runs, every row lands, and the first column is empty in all of them. The fix is one line: decode with utf-8-sig in Python, or .replace(/^/, '') in JavaScript.

On Excel. Excel is not installed on this machine, so we did not measure its behaviour and will not describe it. What we can say is what the bytes do: a file starting EF BB BF is self-labelled UTF-8, and a file without it is a pile of bytes whose charset the reader must guess. That is why "export CSV for Excel" usually means "write the BOM" — and why the same file then breaks a downstream parser that does not strip it. The BOM makes one tool work by breaking the next.

Can you detect the encoding automatically?

Partly, and the reason it works at all is that UTF-8 is a hard shape to hit by accident. We measured how hard:

So "does this decode as UTF-8?" is close to a proof for any file longer than a line or two. On real text it separated cleanly: French in Windows-1252 (68 bytes), French in ISO-8859-1 (69 bytes) and Vietnamese in Windows-1258 (262 bytes) all failed the check; UTF-8, UTF-8-with-BOM and ASCII all passed. A detector built from that check plus BOM sniffing plus a NUL-parity test for UTF-16 scored 10 of 11. It missed "Tên" as UTF-16LE with no BOM — 6 bytes is not enough signal for any heuristic.

The harder limit is that it can never get better than a family. These are the same four bytes:

bytes 43 61 66 E9
  as cp1252   -> 'Café'
  as latin-1  -> 'Café'
  as cp1258   -> 'Café'
  as cp1250   -> 'Café'
  as cp1251   -> 'Cafй'

Nothing in the file distinguishes them. The right move is not a better detector; it is to ask the sender, and to record the answer next to the file — the same discipline that makes a large import reproducible when you stream a big CSV instead of loading it.

Can broken text be repaired?

Often, exactly, and in one line — if the damage is the mislabel kind.

text.encode('windows-1252').decode('utf-8')

Across 16 Vietnamese strings mangled through Windows-1252, that recovered 7 exactly, 0 wrong, and raised UnicodeEncodeError on 9. The nine failures all have the same cause. Windows-1252 has no character at 0x81 0x8D 0x8F 0x90 0x9D, so when the broken reader hit one it wrote U+FFFD (�) and the byte was lost right there:

Nguyễn Văn Đức
utf-8 bytes : 4E 67 75 79 E1 BB 85 6E 20 56 C4 83 6E 20 C4 90 E1 BB A9 63
byte with no cp1252 char: index 15, 0x90
after decode(cp1252,'replace'): 'Nguyễn Văn �ức'
repair raises: UnicodeEncodeError - can't encode '�'
forcing it through: 'Nguyễn Văn �?ức'

9 of the 146 precomposed Vietnamese letters — 6.2%, including Đ, Á, ề, ọ, Ố — have a UTF-8 byte that falls in one of those five holes. In practice that is most Vietnamese name lists.

Repairing through latin-1 instead recovered 16 of 16, exactly, because ISO-8859-1 is a total mapping: all 256 byte values decode to something, so nothing is dropped on the way in and everything survives the way out. If you can influence the broken pipeline, make it read latin-1, not windows-1252.

The repair is safer than it looks. Run blindly over 16 clean Vietnamese strings it changed 0 of them — 'Trần'.encode('cp1252') raises first — and clean French raises UnicodeDecodeError on the way back. It refuses rather than corrupts, which makes it safe in a batch job.

Double encoding takes exactly as many passes as it took rounds to break:

ầ after two rounds is C3 A1 C2 BA C2 A7 — six bytes for one letter. Two repair passes brought the double-encoded string back exactly; one pass left Thá»\x8b still broken. If you see Ã and Â together, you are looking at at least two rounds. That growth is also why an encoding bug shows up as a size surprise first, the way an oversized payload does when you parse a 1 GB JSON file.

Why do two identical-looking names not match?

Because Trần has two legal spellings in Unicode and they are different byte strings:

NFD writes a + combining circumflex + combining grave. They render identically. In JavaScript nfc === nfd is false, new Set([nfc, nfd]).size is 2, and a JSON round trip preserves the difference. In Python the NFD string is not found in a dict keyed by the NFC one. Across our 16-string corpus all 16 differ, and NFD costs 383 bytes against 322 — 18.9% larger for the same text. MySQL agrees with the byte view, which is the trap: inserting both forms into a utf8mb4_bin column with a UNIQUE key gives two rows, both displaying Trần, with lengths 6 and 4.

The fix is one call at the edge — s.normalize('NFC') in JS, unicodedata.normalize('NFC', s) in Python — on every string as it enters, before it is compared, hashed, or used as a key. localeCompare(nfd, 'vi') does return 0, so sorting can be made to work; equality cannot.

What does MySQL do with Vietnamese?

Three columns, one insert, MySQL 8.4.11 with sql_mode cleared so nothing is rejected:

That latin1 row is the article's title, reproduced on demand. Under the default sql_mode — which on this image includes STRICT_TRANS_TABLES — the same insert raises ERROR 1366 (HY000): Incorrect string value: '\xE1\xBA\xA7n T...' instead. Strict mode is doing you a favour; turning it off is how this bug reaches production.

utf8mb3 and utf8mb4 stored byte-identical values, because every Vietnamese letter is at most three bytes in UTF-8. utf8mb3 breaks on four-byte characters: Hương 🇻🇳 comes back as Hương ?? with Warning 1366 ... '\xF0\x9F\x87\xBB\xF0\x9F...'. SHOW CHARACTER SET says it plainly — utf8mb3 maxlen 3, utf8mb4 maxlen 4.

The column charset is not usually the culprit, though. This is:

SET NAMES latin1;
SELECT c_utf8mb4 FROM t;   ->  Tr?n Th? H??ng
SET NAMES utf8mb4;
SELECT c_utf8mb4 FROM t;   ->  Trần Thị Hương

Same row, same perfectly stored bytes, converted to ? on the way out because the connection was labelled latin1. An old driver default will do this to a correct database.

One Node detail worth knowing

Node has no Windows-1252 encoder: Buffer.isEncoding('windows-1252') is false, as is 'cp1252' and 'iso-8859-1'. And on Node v23.5.0 with ICU 76.1, new TextDecoder('windows-1252') reports its encoding as windows-1252 but decodes byte 0x80 to U+0080, not to € (U+20AC) as the WHATWG index specifies — across 0x80–0x9F it matched latin1 on 32 of 32 bytes. Other legacy decoders in the same build are correct (iso-8859-2 0xB1 → U+0105, shift_jis 82 A0 → U+3042). We report the measurement, not an explanation.

That works in your favour: Buffer.from(broken, 'latin1').toString('utf8') is the repair that recovered 16 of 16 above. Just never use Buffer.from(text, 'latin1') to write Vietnamese — it truncates each code point to its low byte and turned our 20-byte name into 14 bytes of nonsense. Encoding is invisible until measured, like the per-language token tax on the same document.

Check it yourself

Python 3 only, no packages, no network. Runs in under a second.

import unicodedata

NAME = "Trần Thị Hương"
hx = lambda b: " ".join(f"{x:02X}" for x in b)

print("UTF-8   ", hx(NAME.encode("utf-8")))
print("UTF-8+BOM", hx(NAME.encode("utf-8-sig"))[:26], "...")
for e in ("cp1252", "latin-1"):
    try:    print(e, hx(NAME.encode(e)))
    except UnicodeEncodeError as ex: print(f"{e:8} cannot encode: {ex}")

# 1. mislabel -> reversible
print("\nread as cp1252 :", NAME.encode("utf-8").decode("cp1252", "replace"))
print("read as latin-1:", NAME.encode("utf-8").decode("latin-1"))
print("repair latin-1 :",
      NAME.encode("utf-8").decode("latin-1").encode("latin-1").decode("utf-8"))

# 2. conversion -> destroyed. This is the ? in the title.
print("\nascii/replace  :", NAME.encode("utf-8").decode("ascii", "replace"))
print("ascii/ignore   :", NAME.encode("utf-8").decode("ascii", "ignore"))

# 3. the BOM in a header
data = "name,email\nTrần,a@b.vn\n".encode("utf-8-sig")
col = data.decode("utf-8").split(",")[0]
print(f"\nheader {col!r} == 'name' ? {col == 'name'}")
print(f"utf-8-sig gives {data.decode('utf-8-sig').split(',')[0]!r}")

# 4. how strong is the UTF-8 check
import random; r = random.Random(1)
def valid(b):
    try: b.decode("utf-8"); return True
    except UnicodeDecodeError: return False
for n in (4, 16, 32):
    hits = sum(valid(r.randbytes(n)) for _ in range(50000))
    print(f"{n:>3}-byte random: {hits}/50000 valid UTF-8")

# 5. two names that look the same
nfc, nfd = unicodedata.normalize("NFC", "Trần"), unicodedata.normalize("NFD", "Trần")
print(f"\n{nfc} == {nfd} ? {nfc == nfd}   bytes {len(nfc.encode())} vs {len(nfd.encode())}")
print("after NFC on both:", unicodedata.normalize("NFC", nfd) == nfc)

And the database half, which needs Docker and about a minute:

docker run -d --name enc-demo -e MYSQL_ROOT_PASSWORD=x -p 3399:3306 mysql:8.4
sleep 25
docker exec -i enc-demo mysql -uroot -px -e "
CREATE DATABASE d; USE d;
SET SESSION sql_mode='';
CREATE TABLE t (a VARCHAR(32) CHARACTER SET latin1,
                b VARCHAR(32) CHARACTER SET utf8mb4);
SET NAMES utf8mb4;
INSERT INTO t VALUES ('Trần Thị Hương','Trần Thị Hương');
SELECT a, HEX(a), b, HEX(b) FROM t\G
SET NAMES latin1; SELECT b FROM t\G"
docker rm -f -v enc-demo

Column a will come back Tr?n Th? H??ng. Column b read over the latin1 connection will come back the same way — from bytes that are perfectly fine on disk.