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,

I have not used Power Query before. This looks really complicated.

Anyway, here is a copy of the CSV file in question. https://1drv.ms/u/s!AvifirwWT_5MgSJapF9B6lbDkQL-?e=CIPFX3

Please note that the delimiter needs to use comma, and not semicolon. If your editor doesn't show the columns correctly, then please specifically choose comma as the delimiter.

Thanks.

Answer 2

Try to open first the CSV file in notepad. Are you seeing the Chinese characters?

If yes, go to File >Save As. Change the extension name to .csv then the encoding should be UTF-8.

If not, then it means that the CSV file is not in UTF-8 encoding.

Answer 3

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.

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

What version of Excel are you using?

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