Excel (XLS) to CSV with UTF-8

Question

I have read the following advice for converting UTF-8 encoded(Hebrew) XLS to CSV via Google Docs, and it worked. When I open the CSV in Sublime2 with UTF8 encoding the Hebrew is showing correctly. But then, when I try to import the Data to My DB using SQLyog, after making sure that both my target table and the import definitions are set to UTF8, I get gibberish, like: מדרשות Where did I go wrong?

Answer 1

The best way to export from excel to csv is:

1. Open the excel file and click on "Save as..."
2. Insert a name and then in "Save as File Type" select "CSV (Comma delimited)"

3. Then, click on "Tools" and select "Web Options"

4. Go to "Encoding", under the option "Save this document as" select "Unicode (UTF-8)".

Listo! I couldn't leave the answer in the proper question : (

Original post found> eHow(spanish)

Some images of this.

Answer 2

1. In Microsoft Excel, open the *.xlsx file.
2. Select Menu | Save As.
3. Enter any name for your file.
4. Under "Save as type," select Unicode Text.
5. Click Save.
6. Open your saved file in Microsoft Notepad.
7. Replace all tab characters with commas (","). Select a tab character (select and copy the space between two column headers) Open the "Find and Replace" window (Press Ctrl+H) and replace all tab characters with comma .
8. Click Save As.
9. Name the file, and change the Encoding: to UTF-8.
   10. Change the file extension from ".txt" to ".csv".
   11. Click Save.
   12. Open the .csv file in Excel to view your data.

source: https://help.salesforce.com/articleView?id=000003837&type=1

Answer 3

For development purpose, I need to change regularly an Excel file and to generate a "CSV" file that is a text file where column's elements are separated by TAB character.

To facilitate my work, I have created following VBS script

'***********************************************************************
'* 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 = 28591
ex.Application.DefaultWebOptions.Encoding = 28591

'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

To start "CSV" file creation for a specific XLSX file, I have created following BAT file

cscript SaveAs.CSV.vbs "D:\Documents\+Informatique\Application\@Visual Basic.NET\DrawPlanUnifilaire\Plan-Unifilaire.xlsx"
pause

So, I only click on BAT file and a TXT tab separated file is automatically generated from first sheet in XLSX file.

The UNICODE UTF8 characters contained in XLSX file (éèàüäù) are correctly converted to Windows ANSI characters.

Answer 4

The solution I came up with was skipping the conversion from CSV to SQL using RegExp. Something like:

FIND: "(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)","(.*)" REPLACE: INSERT INTO aminadav VALUES (NULL,$1,"$2",$3,"$4","$5","$6","$7","$8","$9","$10");