CSV Shows Garbled Chinese in Excel — Three Different Problems, Three Real Fixes

Data · Published 2026-11-11 · YYSI Editorial · 中文版

The short answer

"My CSV is broken" is almost always one of three unrelated problems, and each has its own fix: garbled characters mean an encoding mismatch, everything squeezed into one column means a delimiter mismatch, and 007 turning into 7 means Excel converted your text to numbers. A single file can have any combination — our test file below deliberately contains all three traps, plus commas, quotes and a line break inside fields, in three encodings you can download.

The test file (and what really happened in Excel)

We made one original 4×4 demo table — Chinese and English names, a comma inside a quoted field (北京, 上海 仓发货), doubled quotes (含"双层"包装), a line break inside a field, and order numbers 007 / 0210 / 00 — saved in three encodings plus a semicolon variant, all downloadable below. Then real desktop Excel (Windows 11, Chinese locale, ANSI code page 936/GBK) opened each file exactly as a double-click would, and we recorded what was actually displayed:

Three lessons fall straight out of this table. One: the same file is fine or garbage depending on the encoding guess — GBK files open correctly on Chinese-locale Windows but break on UTF-8-default platforms, and UTF-8-without-BOM files break exactly the same way here (the UTF-8 bytes read as GBK produce 鍝佸悕, the textbook mojibake for 品名). Two: the BOM fixes encoding but nothing else — 007 became 7 in every single file, because Excel converts numeric-looking cells after decoding. Three: a different delimiter quietly demolishes the table structure instead of raising an error.

Fix 1: garbled encoding — verified with the CSV cleaner

The CSV cleaner runs in your browser and never uploads your file. Its file reading is what makes the rescue work: it first tries UTF-8, and when that produces replacement characters it re-reads the same bytes as GB18030. With our GBK file this is exactly what happened — the pasted preview came out as clean Chinese (编号/品名…, no replacement characters), while Excel had needed the file to be GBK already. The tool does not claim to auto-detect every encoding on earth: its documented support is UTF-8 (with or without BOM) and GBK/GB18030. Files in other legacy encodings need conversion at the source.

1. Add the garbled-source file. Open the CSV cleaner and drop the original file (keep the original — see the warning below). A GBK or UTF-8 file decodes automatically; you can also paste text directly.
2. Apply and check the preview. Press Apply. Our GBK file showed 4 rows × 4 columns with every value readable — including the quoted comma, the doubled quotes and the line break inside the 规格 cell. The preview shows the first 50 rows; the export covers everything (limits: 5 MB, 100,000 rows).
3. Export and get a BOM for free. Press Export CSV. The download is always UTF-8 with BOM and CRLF line breaks — exactly what double-click Excel expects on Chinese Windows. Our export was byte-checked: 247 bytes starting with EF BB BF, and the parsed values were identical to the source table, leading zeros included.

After this fix, the exported file opened in real Excel with correct Chinese — same result as our utf8-bom.csv test row. The alternative documented by Microsoft is Data > Get Data > From File > From Text/CSV and choosing the 65001 (UTF-8) origin at import time; that flow is from Microsoft's own pages but we did not machine-test the wizard UI in this round.

Fix 2: one giant column — the delimiter

With the semicolon file left on the cleaner's default comma delimiter, our table parsed as 4 rows × 2 columns — the header landed in one cell and one row split at the comma inside 北京, 上海. Choosing Semicolon explicitly, or switching the delimiter to Auto-detect, both produced the correct 4 rows × 4 columns with all values intact. The same choice exists in Excel's import flow (the wizard lets you set the delimiter), which is why the fix belongs at import time — double-click gives you no say.

Fix 3: 007 became 7 — leading zeros

The CSV file is not where zeros are lost. Our exported file still contains 007 byte for byte — verified after the round trip. The loss happens when Excel opens the file by double-click: it parses 007 as the number 7 in every encoding we tested. Microsoft's documented fix is to import instead of double-clicking and set that column's data format to Text in the import wizard; the file then keeps its zeros. We did not machine-run the wizard in this test, so treat that step as official-documentation-based; what we did verify is that the data survives in the file and that double-click always destroys it.

One more trap: cells that look like formulas

A cell beginning with = + - @ is executed by spreadsheet apps as a formula — a known attack vector in CSVs people exchange (for example =HYPERLINK(...) links). The CSV cleaner ships with safe export on by default: cells starting with those characters get an apostrophe prefix so Excel shows text instead of running anything (the tool tells you how many cells were prefixed; ours prefixed zero because the sample contains none by design — we keep the downloadable samples injection-safe on purpose). Turn the guard off only if you understand why it exists. This page does not demonstrate the dangerous payloads themselves.

Limits and what was not tested

• CSV cleaner limits: 5 MB of text, 100,000 rows, preview of the first 50 rows; encodings UTF-8 (± BOM) and GBK/GB18030 — it does not auto-detect every encoding.
• Excel tests ran on Windows 11, zh-CN locale (ANSI 936), desktop Excel via automation equivalent to double-click. Other locales use different ANSI code pages and list separators — the symptom pattern transfers, the exact garbage characters will differ.
• Not machine-tested here: Excel for macOS, Excel's import wizard UI, Numbers, Google Sheets, WPS. The Microsoft wizard steps above are documentation-based for that reason.
• The paste box in the CSV cleaner cannot repair text that was already mangled before pasting — rescue works from the original file's bytes.
• Full-width semicolons（；）inside Chinese text are ordinary characters and do not affect delimiter detection; ASCII semicolons in cells do affect the auto-detector's vote, which is why explicit delimiter selection is the reliable path.

Try it with our files

Everything below is the exact material from the runs: the same table in three encodings, the semicolon variant, the cleaner's actual export (byte-verified), and the expected-values file a checker can diff against. All samples are injection-safe by design (no cell starts with = + - @).

• utf8-nobom.csv — the mojibake-on-double-click file (246 bytes) — Download sample · 246 bytes
  SHA-256: 97c967fee779688ad56e87273225ebeaaca85eaf29d86611976b4e9ffeadb563
• utf8-bom.csv — same table with BOM; opens correctly (249 bytes) — Download sample · 249 bytes
  SHA-256: 4818beab292d042731cd6feb920fcc5d67bce84f15942cca1b658d7b9abfdfb5
• gbk.csv — same table in GBK/ANSI 936 (190 bytes) — Download sample · 190 bytes
  SHA-256: 95ae4c574779039c3b60dd138243652263cefd7c17fac391a58b2f532281d27a
• semicolons.csv — semicolon-delimited variant (247 bytes) — Download sample · 247 bytes
  SHA-256: c91768e01553d2248bb87216e59b2ad35b6b959cee0fbcf9fe27cd4d8a35d6a9
• cleaned-export.csv — the CSV cleaner's actual export from gbk.csv (247 bytes, UTF-8 BOM + CRLF) — Download sample · 247 bytes
  SHA-256: aeb4d2d9ec22996bdedbe839a2648a470f751dee2918a91dd818c0b3cc120527
• expected-values.json — the ground truth a checker can diff against — Download sample · 491 bytes
  SHA-256: af57dbb2efdc750584aa4da965f4e983657ff931f5135118f10f3e6e62d7a713

Capability claims about the CSV cleaner follow its tool page and engine source (checked 2026-11-11); the import steps follow Microsoft's official pages linked above.