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.
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.
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.
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.
Now click Next to finish the wizard.
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.
Co-founder of Next of Windows and a cool geek 🙂
Latest posts by Jonathan Hu (see all)
Last updated: 11/30/2016