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 GeriSk,

I am truly sorry for the confusion and inconveniences.

I have been trying to find a solution on Excel and Google Sheets. The best solution is of course if we could have a solution that works on Google Sheets, because this will be more readily available.

What I am looking for, if possible, is a formula which will take the strings in a column (say C2), and display the converted Chinese characters in another column (say G2), when there are Unicode codes for Chinese characters in that column (C2).

My input file is a CSV file, and for that particular column, it could have the following:

1. A string of Unicode codes only, for example: 香辣猪
2. A string of Unicode codes, and some other characters, for example: 超多汤肉骨茶500
3. A string of alphanumeric characters, which do not need to be converted, for example: USB Pen drive 32GB

I got the message "Unknown function: 'LAMBDA'." when I entered the formula in Google Sheets.

Thanks.

Answer 2

Hello PuppyMeow,

did I just get you right - you have been looking for an import into excel for Chinese Characters but now you are looking for a conversion tool since your clients do not have excel?

Please clarify your needs: your input is a csv file containing unicode codes and what exactly should be the result?

"(...) could be a mixture of Unicode codes for the Chinese Characters and other numerical characters (...)" will not be a problem for the formula if all characters in a field are encoded the same way.

"(...) an error message "Unknown function: 'LAMBDA'." (...)" - it's time to tell the community which Version of Excel you are using!

[Edit]

Sorry, I missed that at the moment LAMBDA function (microsoft.com) is just available for Office Insiders. If you clarify your needs I will take a look again.

Kind Regards

Gerhard

Answer 3

Hi GeriSk,

I am actually looking for a solution for my clients, and since not many of them have Excel readily available, I believe a better solution will be to try to come up with a workaround or solution on Google Sheets. I tried your code, and found that it doesn't run, with an error message "Unknown function: 'LAMBDA'.".

For your info, for the particular column, the values could be a mixture of Unicode codes for the Chinese Characters, and other numerical characters.

Answer 4

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

Did not work, that opens as Excel file.

Again: Zip your CSV file!

Andreas.

Answer 5

Hi,

When I open the CSV file in notepad, I don't see Chinese characters. I only see characters such as 香辣猪

How can I get Excel to display these in actual Chinese characters?

Thanks.