How to Fix Chinese Characters Displaying Incorrectly in Excel CSV Files
Question details
The user needs to resolve an issue where Chinese characters in a CSV file appear as garbled text or unreadable symbols when opened in Microsoft Excel.
- Product
- Microsoft Excel, CSV files
- Device & OS
- not provided
- Scenario
- Opening a UTF-8 encoded CSV file containing Chinese text directly in Excel.
- Observed behavior
- Excel fails to detect the UTF-8 encoding without a Byte-Order Mark (BOM), causing Chinese characters to display incorrectly, even though other text editors read the file fine.
Before altering file encodings or running commands, make a backup copy of your original CSV file to ensure your raw data is safe from accidental corruption.
Convert to UTF-8 with BOM Using a Text Editor
The most straightforward method is to re-save the CSV file with a Byte-Order Mark (BOM) using a text editor like Notepad++ (Windows) or VS Code (macOS/Windows).
Excel requires a BOM signature at the beginning of a file to correctly recognize it as UTF-8. Standard text editors can easily append this.
Right-click your CSV file and open it with an advanced text editor like Notepad++ or Visual Studio Code (VS Code).
In Notepad++, go to the 'Encoding' menu at the top and select 'UTF-8-BOM'. If using VS Code, click the current encoding at the bottom right of the window, select 'Save with Encoding', and choose 'UTF-8 with BOM'.
Save the file (Ctrl + S or Cmd + S). Double-click the file to open it again in Excel; the Chinese characters will now display correctly.
Use Excel's Data Import Feature
If you prefer not to change the file's encoding permanently, you can use Excel's built-in data import wizard to manually specify UTF-8 encoding.
Add BOM via Programming or Command Line
For developers and advanced users, generating the CSV with the correct encoding from the start or using shell commands can automate the fix.
Seamlessly Open UTF-8 CSV Files with WPS Spreadsheet
WPS Office automatically detects file encoding or provides a clear Text Import prompt when opening CSV files, preventing garbled Chinese characters without requiring workarounds like adding a BOM.
- 1. Open the CSV with WPS: Right-click your CSV file, select 'Open with', and choose WPS Office.
- 2. Select UTF-8 Encoding: If prompted by the File Conversion dialog, simply choose 'UTF-8' from the Text Encoding dropdown to ensure Chinese characters display perfectly.
- 3. View and Edit Data: Click 'OK' to open the file. You can now read, edit, and save your data seamlessly without modifying the original file encoding via command line.
Frequently Asked Questions
Why do Chinese characters turn into garbled text in Excel?
Excel relies on a Byte-Order Mark (BOM) to identify a CSV file as UTF-8 encoded. If the file lacks this BOM, Excel defaults to the system's local ANSI encoding, which cannot interpret Chinese characters, resulting in garbled text (mojibake).
What exactly is UTF-8 with BOM?
UTF-8 with BOM includes a special hidden character (U+FEFF) at the very beginning of the file. This acts as a signature that explicitly tells applications like Microsoft Excel that the file is encoded in UTF-8, ensuring international characters are displayed properly.
Can I fix the CSV encoding on Mac without downloading third-party editors?
Yes, you can use the built-in Terminal application on macOS. Run `echo -ne '\xEF\xBB\xBF' > fixed.csv` to create a file with a BOM, and then append your original file using `cat original.csv >> fixed.csv`.
Does saving the file as an Excel Workbook (.xlsx) fix the issue permanently?
Yes. Once you have imported or opened the CSV correctly so that the Chinese text is readable, going to 'Save As' and choosing Excel Workbook (*.xlsx) will preserve the characters natively without needing to worry about text encodings in the future.
