The Ultimate Guide to Converting Excel (XLS/XLSX) to CSV with UTF-8 Encoding

Data conversion is a common task in various fields, and one frequent scenario involves converting Excel files (both .xls and .xlsx formats) to CSV (Comma Separated Values) files. The challenge arises when dealing with special characters or non-English alphabets, requiring UTF-8 encoding to ensure data integrity. This article provides a comprehensive guide on how to convert Excel files to CSV with UTF-8 encoding, ensuring that your data is accurately represented and ready for use in various applications.

Why UTF-8 Encoding Matters?

UTF-8 is a character encoding capable of encoding all possible characters (called code points) in Unicode. It's crucial when your Excel files contain characters outside the standard ASCII range, such as accented letters (é, à, ü), characters from non-English languages (Hebrew, Chinese, etc.), or special symbols. Without UTF-8 encoding, these characters may be garbled or replaced with incorrect symbols during the conversion process, leading to data corruption.

Method 1: Using Microsoft Excel

Microsoft Excel offers a built-in method to save files as CSV with UTF-8 encoding, although it requires a few extra steps:

1. Open the Excel file: Launch Microsoft Excel and open the .xls or .xlsx file you want to convert.
2. Save As CSV: Go to "File" > "Save As". Choose "CSV (Comma delimited) (*.csv)" as the file type.
3. Access Web Options: Click on "Tools" next to the "Save" button, and select "Web Options".
4. Set Encoding to UTF-8: In the "Web Options" window, go to the "Encoding" tab. Under "Save this document as", select "Unicode (UTF-8)".
5. Save the File: Click "OK" and then "Save" to save the file as a CSV with UTF-8 encoding.

Note: some older version of excel might not have the option to directly save as UTF-8 CSV. If this is the case see method 2 for a workaround.

Method 2: A Notepad Workaround

If the direct method doesn't work or isn't available, you can use a combination of Excel and Notepad to achieve the desired result:

1. Open the XLSX File in Excel: Open your .xlsx file using Microsoft Excel.
2. Save as Unicode Text: Go to "File" > "Save As" and select "Unicode Text (*.txt)" as the file type. Save the file.
3. Open in Notepad: Open the newly created .txt file with Microsoft Notepad.
4. Replace Tabs with Commas: Press Ctrl+H to open the "Find and Replace" window. Copy a tab character from the file (the space between two column headers) and paste it into the "Find what" field. Enter a comma (,) in the "Replace with" field. Click "Replace All".
5. Save as UTF-8 CSV: Go to "File" > "Save As". In the "Save As" dialog box, name your file with a .csv extension. Crucially, change the "Encoding" option to "UTF-8". Click "Save".
6. Verify: Open the .csv file in Excel to ensure your data is displayed correctly.

Method 3: Using LibreOffice Calc

LibreOffice Calc, a free and open-source spreadsheet program, often handles UTF-8 encoding more reliably than Excel in some cases:

1. Open the Excel File: Open your .xls or .xlsx file with LibreOffice Calc.
2. Save As CSV: Go to "File" > "Save As". Choose "Text CSV (*.csv)" as the file type.
3. Specify Encoding: In the "Save As" dialog, you'll usually see an option to specify the character set. Select "UTF-8" from the dropdown menu. You can also customize the field delimiter (usually a comma) and text delimiter (usually a quotation mark).
4. Save the File: Click "Save". LibreOffice Calc will prompt you to confirm the CSV format; click "Use Text CSV Format".

Method 4: Using a VBScript

For those who need to automate the conversion process, a VBScript can be used. This is particularly useful for developers or users who regularly convert files:

'***********************************************************************
'* file: SaveAs.CSV.bat
'***********************************************************************
sInputFile = Wscript.Arguments(0)
WScript.Echo "Excel input file: " & sInputFile
Set ex = CreateObject("Excel.Application")
Set wb = ex.Workbooks.Open(sInputFile)
ex.Application.DisplayAlerts = False
'https://learn.microsoft.com/en-us/office/vba/api/office.msoencoding
wb.WebOptions.Encoding = 65001 ' UTF-8 Encoding
ex.Application.DefaultWebOptions.Encoding = 65001 ' UTF-8 Encoding
'https://learn.microsoft.com/en-us/office/vba/api/excel.xlfileformat
sOutputFile = Replace(sInputFile & "*",".xlsx*",".txt")
ex.Worksheets(1).SaveAs sOutputFile, 20
ex.ActiveWorkbook.Close
ex.Application.Quit
WScript.Echo "CSV file has been created."
WScript.Quit

1. Save the Script: Save the above code as a .vbs file (e.g., SaveAsUTF8.vbs).
2. Create a Batch File: Create a .bat file with the following content, replacing the file path with your Excel file's path:

cscript SaveAsUTF8.vbs "D:\Documents\YourExcelFile.xlsx"
pause

1. Run the Batch File: Double-click the .bat file to execute the script. This will convert the first sheet of your Excel file to a tab-separated .txt file with UTF-8 encoding.

Note: This script saves the file as a TXT file with tab separated values. You may need to manually change the file extension to .csv and replace tab characters with commas for full CSV compatibility

Troubleshooting : Common Issues and Solutions

• Garbled Characters: If you still see garbled characters after conversion, double-check that your target application (e.g., database, text editor) also supports UTF-8 encoding and is configured to use it.
• Incorrect Delimiters: Ensure that the field delimiter (usually a comma) and text delimiter (usually a quotation mark) are correctly set when saving as CSV. Incorrect delimiters can lead to parsing errors.
• Leading/Trailing Spaces: Sometimes, Excel can add leading or trailing spaces to cell values, which can cause issues when importing the CSV data. Use Excel's TRIM() function to remove these spaces before converting to CSV.

Conclusion

Converting Excel files to CSV with UTF-8 encoding can be tricky, but by following these methods and troubleshooting tips, you can ensure that your data is accurately converted and ready for use in various applications. Choose the method that best suits your needs and technical skills, and always verify the output to ensure data integrity.