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.
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
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
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
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.
6. Import the new .csv file into the mysql table.