Garbled CSV text? Reopen the UTF-8 original before saving another copy

01Choose a repair from the symptom

Garbled names, an entire line in one cell and missing leading zeros point to character decoding, field boundaries and column types respectively. One bad-looking preview is not a single diagnosis. Identify the affected layer on a copy instead of deleting unusual characters or turning the entire table into numbers.

Microsoft’s text and CSV import documentation distinguishes direct opening from a controlled import. This workflow leaves system-wide regional settings unchanged and chooses encoding and separation for this particular file.

02Two originals with exactly the same text

The pack’s source-utf8.csv and source-utf8-bom.csv both use UTF-8, commas and LF newlines. The second adds only the three leading bytes EF BB BF. These are fictional teaching records; both language versions retain the same names for direct comparison.

A damaged-replacement.csv control deliberately replaces one name with U+FFFD. It is not the valid source to recover from. It demonstrates that opening a file successfully as UTF-8 does not establish that its content survived.

ID,Name,Amount
0007,上海,12.50
0008,Zoë,0.00
0009,東京,7.50

03Reopen the source with UTF-8 selected explicitly

Microsoft’s UTF-8 CSV guidance recommends a BOM for normal opening, or using Text/CSV import through Get Data or the Text Import Wizard. Menus differ across versions and platforms. If your version lacks the named path, consult its documentation rather than treating a changed file extension as a conversion.

In Excel, preserve the source and choose Data → From Text/CSV to import it. Confirm UTF-8 as the file origin and comma as the delimiter. Use the transform step to keep ID as text and treat only Amount as numeric. Check every name and all three columns before loading a fresh workbook. This article does not claim an execution in your Excel version.

For a local browser route to XLSX, use CSV to Excel: select a valid original, UTF-8, comma separation and a header; keep ID and Name as text and select a numeric Amount. Convert and download. The tool reads the encoding you select; it cannot identify every unknown historical encoding or restore a name that has already been damaged.

1. 1
   Preview source-utf8.csv and confirm four rows including the header, three columns and every name.
2. 2
   Keep identifiers and names as text, changing only Amount to a numeric type.
3. 3
   Download the XLSX, reopen the actual file from disk and compare every record with the answer below.
4. 4
   Repeat with source-utf8-bom.csv under the same settings. Compare cell contents, not the resulting files’ hashes.

04Check original text, types and totals separately

There are only three short records, so compare every character. 0007, 0008 and 0009 must each keep four digits. Zoë ends with ë rather than e. The two CJK names are distinct source strings; merely seeing readable Chinese is not sufficient. Use the literal source and reference cells below for the exact characters.

For the numeric Amount column, 12.50 + 0.00 + 7.50 = 20.00. An XLSX display format may show 12.50 as 12.5 without changing its value. Compare name characters, identifiers and numeric values separately. Passing these three records does not validate an unrelated large file.

05Separate reading the same bytes from writing new bytes

If an incorrectly decoded preview has not been saved, the unchanged original can usually be reopened with the correct encoding. Once the wrong display has been re-encoded and saved, compare an earlier backup. Do not repeatedly convert it until it resembles a plausible name. For an actual GBK or UTF-16 file, confirm the source encoding; this exercise does not offer universal automatic detection.

If names are correct but columns or identifiers are still wrong, continue with the CSV field-boundary and type guide. To agree which original was received, use the file identity and SHA-256 handoff. Different hashes for BOM and non-BOM files do not by themselves mean different names or amounts.

Specify UTF-8, BOM presence, comma separation, a header and the ID text requirement. Include the three-record/20.00 sample checks. CSV does not carry Excel cell types, so a future import still needs those conditions.