Excel to CSV UTF8
In case excel contains any smart quotes or any other language data while converting the data from Excel to CSV, it won’t convert accurately and ends up getting question marks. So while importing the data also we get the same things. However, by using Excel CSV UTF8 as the encoding, we can choose the file format and language easily.
What is UTF8 Encoding?
Unicode Transformation Format is the format for converting any character to Unicode standard that can be as compact as ASCII.
ASCII: American Standard Code for Information Interchange
CSV format files are there for long years and all of we know is just the basic level of exporting and importing the data. But in addition to that, there are some advanced techniques that are also there. In the case of data that comes with special characters needs a different kind of procedure to convert the data from Excel to CSV. In this article, we will show you how to deal with special kinds of characters with excel CSV file i.e. CSV UTF8.
Example of UTF8 Encoding in Excel CSV File
For example, look at the below data in excel worksheet.
This are the names in Japanese and we have proper English meaning for each of the words here. Now let’s convert the file to CSV format now.
Step 1: Click Save As button or press the F12 shortcut in excel.
Step 2: Choose the destination folder to save the file as a CSV file.
Step 3: Change the save as file type to CSV (Comma Delimited).
Step 4: Click on the “Save” button and our data will be saved as a CSV file.
With the file extension, we can easily view the file format.
Look at the above files which are named the same but the file extension is “CSV” & “XLSM” so this shows that both the file formats are different from each other.
Now open the CSV file and see what we get.
Look at the values in column B & C (where we had Japanese words), we have got question marks instead of actual words and when we try importing the data to excel, still we get question marks only.
So this is the problem with special character data conversion to CSV file. To avoid this we need to follow different procedures and below the procedure.
Save File As Unicode Text Format
Before we save the data in the CSV file format we need to first save as “Unicode Text (*.txt)” then convert to CSV file, follow the below steps.
Step 1: Once again open Save As window and this time choose the file format as “Unicode Text (*.Txt)” format.
Step 2: Click on the “Save” button and the file will be saved in Unicode format.
Look now we have three format files, Excel, CSV & Unicode Text Format.
Now open a Unicode Text file and see how data looks.
Data looks as it is in the text file.
Now we need to save the above file as an encoding of UTF-8 method to get the correct data into excel. So now insert the delimiter for the above data by replacing the space characters. Copy the first space character.
Now open Find & Replace in Excel and replace the copied space with a comma (,).
Click on Replace All and all the space characters will be replaced by a comma.
Now choose Save As option.
This will open up save window and in that window choose the Encoding option as UTF-8.
Click on the “Save” button to complete the save option.
Now change the file extension from “.txt” to “.csv”.
Now open the CSV file and we should get the correct Japanese words instead of the question mark.
Things to Remember
- UTF converts special character words through encoding.
- Save the file as Unicode before converting to csv and encoding as UTF-8.
This has been a guide to Excel CSV UTF8. Here we discuss how to convert special kinds of characters with CSV files along with practical examples. You may learn more about excel from the following articles –