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

Hello,

it seems your csv is not containing Chinese Characters but instead it is holding the unicode codes for the Chinese Characters. You can't import what isn't there ...

In this case following solution may help you:

The Excel function UNICHAR returns the unicode character responding to the code (see cells B3 and C3).

Cell C6 contains following formula:

=SUBSTITUTE(ARRAYTOTEXT(MAKEARRAY(1,20,LAMBDA(row,col, IFERROR(UNICHAR(MID(B6,FIND(CHAR(160),SUBSTITUTE(B6,"&",CHAR(160),col))+2,5)),"")))),",","")

This formula converts strings like "香辣猪" to its corresponding unicode characters. Each single character code needs to start with '&' followed by another character (doesn't matter which, '#' does the job) followed by five digits as character code. This formula works for up to 20 unicode characters ... (but may be extended).

Kind Regards

Gerhard

Answer 2

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 3

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 4

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 5

Hello,

perhaps following formula does what you want. It replaces up to 20 different unicode codes by their corresponding character. Replacement starts at every '&#' - so your imported data shouldn't have '&#' in other positions than as start for unicode codes.

In the screenshot you see the formula (no LAMBDA, no MAKEARRAY, ...) of C9 converting B9 (C10 linked to B10 and C11 linked to B11). Here the formula of C9 for copying:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B9,IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),1)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),1))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),2)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),2))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),3)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),3))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),4)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),4))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),5)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),5))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),6)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),6))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),7)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),7))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),8)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),8))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),9)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),9))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),10)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),10))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),11)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),11))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),12)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),12))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),13)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),13))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),14)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),14))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),15)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),15))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),16)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),16))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),17)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),17))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),18)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),18))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),19)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),19))+2,5)),"")),IFERROR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),20)),8),CHAR(160)),IFERROR(UNICHAR(MID(B9,FIND(CHAR(160),SUBSTITUTE(B9,"&#",CHAR(160),20))+2,5)),""))

Kind Regards

Gerhard