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

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

Answer 3

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 4

You need to know the encoding used for the CSV file so that you will be able to open the file properly.

If you have other tool that can open the file as Chinese Character, then copy the content of it and paste it to Notepad in that case.

Answer 5

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.