How to Import CSV Files with Unicode and Chinese Characters into Excel
Question details
The user needs to import a CSV file containing Chinese characters and numeric character references into Excel without the text appearing garbled.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening or importing a CSV data export that includes non-Latin scripts (like Chinese) or HTML-style Unicode character codes.
- Observed behavior
- The Chinese characters display incorrectly due to an encoding mismatch, or they appear as raw numeric character references (e.g., 香) instead of the actual characters.
Ensure you know which delimiter your CSV file uses (e.g., comma, semicolon, or tab) and avoid opening the file directly by double-clicking, as this triggers default ANSI encoding.
Use the Text Import Wizard with UTF-8 Encoding
This is the best method to ensure Excel correctly decodes Chinese characters and other complex text elements during the CSV import process.
By default, double-clicking a CSV file in Windows forces Excel to read the file using the system's regional encoding. To successfully read UTF-8 encoded files containing Chinese characters, you must force Excel to use the correct file origin via the Data tab.
Open a blank workbook in Excel. Navigate to the 'Data' tab on the ribbon and click on 'From Text' or 'From Text/CSV' in the Get External Data group.
Browse your computer for the target CSV file, select it, and click 'Import'.
In step 1 of the Text Import Wizard, select 'Delimited' as the data type. In the 'File origin' drop-down menu, scroll down and select '65001: Unicode (UTF-8)'. Click 'Next'.
Check the box corresponding to your CSV's delimiter (usually 'Comma' or 'Semicolon'). Ensure the Data preview displays the columns correctly, then click 'Next'.
In the Data preview window, highlight all columns containing data. Under 'Column data format', select 'Text' to prevent Excel from dropping leading zeros or altering data. Finally, click 'Finish'.
Convert Numeric Character References Using UNICHAR
Use this solution if your imported data contains HTML-style numeric character codes (like 香) instead of actual Chinese characters.
Import and Edit CSV Files Flawlessly with WPS Spreadsheet
WPS Spreadsheet features a highly intuitive Text Import Wizard that seamlessly detects UTF-8 encoding. It allows you to quickly open CSV files containing Chinese characters, Unicode symbols, and complex data formats without encountering garbled text.
- 1. Import the Data: Open WPS Spreadsheet, go to the 'Data' tab, click 'Import Data', and select 'Import External Data'.
- 2. Select the CSV File: Browse for your CSV file and open it to trigger the Text Import Wizard.
- 3. Choose UTF-8 Encoding: Select 'Delimited' and ensure the File Origin is set to 'UTF-8' so Chinese characters render perfectly in the preview box.
- 4. Apply and Finish: Pick your delimiter, set your desired columns to 'Text' format, and click 'Finish' to load your spreadsheet.
Frequently Asked Questions
Why do Chinese characters look like gibberish when I open a CSV directly in Excel?
When you double-click a CSV file, Excel defaults to your system's regional ANSI encoding. If the CSV was exported using UTF-8 (which is standard for Chinese characters), Excel fails to decode it properly, resulting in garbled text. You must use the Data > From Text wizard to manually specify UTF-8 encoding.
What does a code like 香 mean in my CSV file?
This is an HTML numeric character reference representing a specific Unicode character. Software systems often export special characters in this format for web compatibility. Excel reads it as literal text, so you have to use the UNICHAR function to convert the numeric string back into its visual character.
Can I save a CSV file with Chinese characters in Excel without losing the text formatting?
Yes. When saving your edited file, navigate to File > Save As, and under the file format drop-down menu, select 'CSV UTF-8 (Comma delimited) (*.csv)'. This newer format ensures all Unicode and Chinese characters are perfectly preserved upon saving.
How do I fix UTF-8 CSV garbled text issues permanently on Windows?
You can change your Windows system locale settings to use UTF-8 for worldwide language support. Go to Control Panel > Clock and Region > Region > Administrative tab > Change system locale, and check the box for 'Beta: Use Unicode UTF-8 for worldwide language support'. Restart your computer for the changes to take effect.
