How do I export an Excel file with Chinese characters to a CSV?

Question

I having a Excel document with a data table containing Chinese characters. I am trying to export this Excel spreadsheet to a CSV file for importing into a MySQL database.

However, when I save the Excel document as a CSV file, Notepad displays the resulting CSV file's Chinese characters as question marks. Importing into MySQL preserves the question marks, completely ignoring what the original Chinese characters are.

I'm suspecting this may have to do with using Excel with UTF-8 encoding. Thanks for your help!

Answer 1

The following method has been tested and used to import CSV files in MongoDB, so it should work:

1. In your Excel worksheet, go to File > Save As.

2. Name the file and choose Unicode Text (*.txt) from the drop-down list next to "Save as type", and then click Save.

3. Open the unicode .txt file using your preferred text editor, for example Notepad.

4. Since our unicode text file is a tab-delimited file and we want to convert Excel to CSV (comma-separated) file, we need to replace all tabs with commas.

5. Select a tab character, right click it and choose Copy from the context menu, or simply press CTRL+C as shown in the screenshot below.

6. Press CTRL+H to open the Replace dialog and paste the copied tab (CTRL+V) in the Find what field. When you do this, the cursor will move rightwards indicating that the tab was pasted. Type a comma in the Replace with field and click Replace All.

7. Click File > Save As, enter a file name and change the encoding to UTF-8. Then click the Save button.

8. Change the .txt extension to .csv directly in Notepad's Save as dialog and choose All files (.) next to Save as type, as shown in the screenshot below.

9. Open the CSV file from Excel by clicking File > Open > Text files (.prn, .txt, .csv) and verify if the data is Okay.

Source here

Answer 2

As far as I know Excel doesn't save CSV files in any Unicode encoding. I have had similar issues recently trying to export a file as CSV with the £ symbol. I had the benefit of being able to use another tool altogether.

My version of Excel 2010 can export in Unicode format File > Save As > Unicode Text (.txt), but the output is a tab-delimited, UCS-2 encoded file. I don't know MySQL at all but a brief look at the specifications and it appears to handle tab delimited imports and UCS-2. It may be worth trying this output.

Edit: Additionally, you could always open this Unicode output in Notepad++ convert it to UTF-8 Encoding > Convert to UTF-8 without BOM And possibly replace all tab chars with commas too (Use the Replace dialogue in Extended Search mode, \t in the Find box and , in the Replace box.)