xls to csv with chainese character support

Question

Hi,

While saving an excel file with chainese character to csv, these characters are converting to ??? (Question marks) junk characters.

Please let me know if any of you have any solution for this. I tried saving it in unicode text, it worked fine but when I tried saving it as .csv, its not working.

Thanks

Answer 1

I has a similar problem with Japanese characters before. At the time Excel 2003 only exported CSV to Latin1 (or maybe Windows 1352). I basically wrote my own Excel Macro to iterate over the rows and columns, and build up an in memory string of what the CSV file would look like. Then, I used an ADODB.Stream to save it myself. This sample code should get you started.

Dim csvdata As String
Dim CRLF As String
Dim objStream As Object

CRLF = Chr(13) & Chr(10)

csvdata = """key"",""value""" + CRLF
csvdata = csvdata + """a"",""a""" + CRLF
csvdata = csvdata + """aacute"",""á""" + CRLF

Set objStream = CreateObject("ADODB.Stream")
objStream.Open
objStream.Position = 0
objStream.Charset = "UTF-8"
objStream.WriteText csvdata
objStream.SaveToFile "test.csv", 2 ' adSaveCreateOverWrite
objStream.Close

Answer 2

Why do you need a CSV file? What encoding do you need it in? UTF-8? GBK? What software is going to read the CSV file? What version of Excel are you using?

If you know Python, you could use the xlrd module to read the Excel file, format the data, encode it, and write it to a CSV file, or use it to update a database, or whatever.