How to Export Excel Sheet to CSV with Unicode/UTF-8

By | August 30, 2018
0 Comment

Bismillahi-r-Rahmani-r-Rahim (In the name of Allah, the most Compassionate, the most Merciful)

Assalamu alaikum wa rahmatullahi wa barakatuh (May the peace, mercy, and blessings of Allah be with you)

This is the ad hoc tutorial on how to export the excel sheet to CSV format with unicode characters.

Below content is complementary to the video tutorial above.

Scenario

We have excel table with 3 columns and 4 rows that contains unicode data. We need to export the data into csv format and preserve the unicode characters. Also we will import the resulting csv file into the corresponding mysql table.

Unfortunately, the Excel’s built-in Save as .csv does not preserve the unicode characters. So we consider other options.

Option 1 – Save as Unicode text using Excel and Notepad

Use this option if you do not want to download other software and need to use only Excel.

1. Open the excel file using Microsoft Excel and in “Save as” window use Unicode Text (*.txt) for Save as type dropdown
Excel Save As Unicode
2. Open the exported .txt file with Notepad and choose File -> Save As. In Save As window change the Encoding input value to UTF-8 and click the Save button to replace the file
Notepad Save As UTF-8
3. Change the extension of the new file from .txt to .csv.
4. Import the new .csv file into the mysql table and choose Tab symbol (\t) as field terminator when importing.

Option 2 – LibreOffice Calc Save As .csv

I like this option, the Save As CSV function of LibreOffice is excellent and it provides CSV configuration options as well.

1. Download and install LibreOffice from https://www.libreoffice.org
2. Open the excel file using LibreOffice Calc and choose File -> Save As…
3. In Save As window choose Text CSV (.csv)(*.csv) as Save as type
LibreOffice Calc Save As CSV
4. Click Use Text CSV Format button if prompted.
5. Set Character Set: Unicode (UTF-8) in Field Options window and set other CSV options if needed and click OK button.
LibreOffice Save As CSV options
6. Import the new .csv file into the mysql table.

Related resources: