How do you open/import a CSV file with Unicode characters?

Question

I tried to import a CSV file with Chinese characters in Unicode (for example 香辣猪) in Excel, although I have set the "File Origin" to "65001: Unicode (UTF-8)", but seems like it doesn't do anything.

The following is a screenshot of the import screen. You can see the column "Product Title" has Chinese characters in Unicode, but the characters are not being displayed properly.

How can I import the CSV file with Chinese characters in Unicode, successfully in Excel?

Answer 1

Hi PuppyMeow, hope you're doing well. I’m Ian, and I’m happy to help you today.

Please follow steps below.

1. Open Excel then go to Data Menu
2. Under Get External Data, click From Text.
3. Select the CSV file that you want to open.
4. Choose the Delimited option.
5. Set the character encoding File Origin to 65001: Unicode (UTF-8) from the drop-down list.

6.Set the delimiter, in your case it is semicolon.

7. Click Next to move on to the next step.
8. Select all columns and set Column data format to Text.
9. Click OK and then Finish.

This is a user-to-user support forum and I am a fellow user.

I hope this helps, but please let me know if you need anything else.

Answer 2

Hi rianvillareal,

Thank you so much for your answer. However, it doesn't work.

Your steps are for a different version of Excel (most likely older than mine).

I tried to use your steps and options as much as possible in my version of Excel (actually, I have already tried all these, as you can see in my screenshot which I have shared in my question), but, it doesn't work. Excel doesn't display the Unicode characters properly.

By the way, my delimiter is comma, and not semicolon.

Thanks.

Answer 3

What version of Excel are you using?

You mean you don't have an option to import from text file?

Answer 4

Have a look into this article how to set the locale in PQ:

Power Query - How to import a CSV file that does not match your locale - Microsoft Community

If you need further help we need to see your CSV file.

IMPORTANT: Zip your CSV file!
https://support.microsoft.com/en-us/windows/zip-and-unzip-files-8d28fa72-f2f9-712f-67df-f80cf89fd4e5

Login to https://onedrive.live.com (Use the same Login ID and password as for this forum).
Click Upload in the top and choose your file.
After uploading, right click the file and choose Share.
Click Copy Link in the lower left edge (no need to enter an email).
Copy the link and paste it here.

Andreas.

Answer 5

Hi,

I am using a very new version of Excel.. but I can't seems to locate the version number though.

Anyway, there is an option to import from text, and I am using it. Please check my screenshot in my question.

Thanks.