Python Convert Excel to CSV

Question

It seems that there are a lot of posts on this topic, and my solution corresponds to what is perhaps the most general answer, however I encountered a coding error that I do not know how to handle.

>>> def Excel2CSV(ExcelFile, SheetName, CSVFile): import xlrd import csv workbook = xlrd.open_workbook(ExcelFile) worksheet = workbook.sheet_by_name(SheetName) csvfile = open(CSVFile, 'wb') wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL) for rownum in xrange(worksheet.nrows): wr.writerow(worksheet.row_values(rownum)) csvfile.close() >>> Excel2CSV(r"C:\Temp\Store List.xls", "Open_Locations", r"C:\Temp\StoreList.csv") Traceback (most recent call last): File "<pyshell#2>", line 1, in <module> Excel2CSV(r"C:\Temp\Store List.xls", "Open_Locations", r"C:\Temp\StoreList.csv") File "<pyshell#1>", line 10, in Excel2CSV wr.writerow(worksheet.row_values(rownum)) UnicodeEncodeError: 'ascii' codec can't encode character u'\xa0' in position 14: ordinal not in range(128) >>> 

Any help or understanding is greatly appreciated.

Answer 1

As @davidism points out, the Python 2 csv module does not work with unicode. You can get around this by converting all of your unicode objects to str objects before sending them to csv :

 def Excel2CSV(ExcelFile, SheetName, CSVFile): import xlrd import csv workbook = xlrd.open_workbook(ExcelFile) worksheet = workbook.sheet_by_name(SheetName) csvfile = open(CSVFile, 'wb') wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL) for rownum in xrange(worksheet.nrows): wr.writerow( list(x.encode('utf-8') if type(x) == type(u'') else x for x in worksheet.row_values(rownum))) csvfile.close() 

Answer 2

The Python 2 csv module has some problems with Unicode data. You can either encode everything up to UTF-8 before recording, or use unicodecsv to do it for you.

First pip install unicodecsv . Then instead of import csv just import unicodecsv as csv . The API is the same (plus encoding options), so no other changes are required.

Answer 3

Another mod for this: paste in a string, since you have a string, you can code it as "utf-8".

 str(worksheet.row_values(rownum)).encode('utf-8') 

The whole function:

 def Excel2CSV(ExcelFile, SheetName, CSVFile): import xlrd import csv workbook = xlrd.open_workbook(ExcelFile) worksheet = workbook.sheet_by_name(SheetName) csvfile = open(CSVFile, 'wb') wr = csv.writer(csvfile, quoting=csv.QUOTE_ALL) for rownum in xrange(worksheet.nrows): wr.writerow(str(worksheet.row_values(rownum)).encode('utf-8')) csvfile.close()