How To Display CSV Files with Unicode UTF-8 Encoding in Excel

CSV, comma separated values, is a common format when saving information in a tabular form. Unlike native Excel format, it is cross-platform and requires no special software to read the content. You can drop a .csv file in a text editor and would still be legible. Any content separated by comma can turn into a .csv file.

By default, if you have Excel installed, .csv file will load just like any other Excel spreadsheet. The difference is, you won’t get rich content and Excel will not auto-detect if a given .csv file is encoded in Unicode UTF-8. That means when a .csv file encoded in Unicode Excel will not be able to display the text correctly when first opened like below.

2016-11-29_1726

Fix The Unicode CSV File By Import Data From Text

It would be nice if Microsoft actually implements Excel to auto-detect Unicode and turn on the encoding when loading a .csv file. Unfortunately, Excel is dumb, and it just parses whatever you give as an ASCII format.

But there is a solution you can use to make Excel decode the Unicode CSV file correctly.

First, go to Data > From Text to launch a Text Import Wizard.

2016-11-29_1702

Now select the file origin to pick “65001: Unicode (UTF-8)”, this will turn your CSV file into something that’s legible. Also while we are here, select “Delimited” so that we can tell Excel to use comma as the separator.

2016-11-29_1704

At the Text Import Wizard second step, select Comma. Now you should see from the Data preview everything is properly aligned into its own column.

2016-11-29_1704_001

Now click Next to finish the wizard.

2016-11-29_1705_001

We want to start Import the Data from the very top left corner of the sheet. Click OK, and you should be able to see all your CSV Unicode characters come back to life. Now, make sure save your spreadsheet again into native Excel Workbook format (.xlsx)

That’s how you can convert and read Unicode .csv into an Excel .xlsx format.

Jonathan Hu

Programming by day, Web Development, Canucks & Movies for spare time!
Co-founder of Next of Windows and a cool geek 🙂

Last updated: 11/30/2016

Posted in: How to , Office
Discover more: , ,

Leave a Reply

Notify of
avatar
wpDiscuz