Convert xlsm to csv

Question

I am parsing through vba macros encrypted Excel file and trying to copy all of the sheets in the Excel file but my script keeps blowing up. What am I doing wrong in my snippet.

import csv
import xlrd

workbook = xlrd.open_workbook('P:/NEW.xlsm')
for sheet in workbook.sheets():
    with open('{}.csv'.format(sheet.name), 'wb') as f:
        writer = csv.writer(f)
        writer.writerows(sheet.row_values(row) for row in range(sheet.nrows))

Error:

Traceback (most recent call last):
  File "C:/Users/datainput.py", line 8, in <module>
    writer.writerows(sheet.row_values(row) for row in range(sheet.nrows))
UnicodeEncodeError: 'ascii' codec can't encode character u'\u2013' in position 20: ordinal not in range(128)

Answer 1

Clearly there is an issue dealing with the unicode in your original file. You could look at removing the unicode with before writing into your new file:

import re
text_without_unicode = re.sub(r'[^\x00-\x7f]', r'', text_with_unicode)

or use the .encode('utf-8') / decode functions

Answer 2

I just wanted to comment that I ran into a similar issue, and what seems to work for me was changing:

writer.writerows(sheet.row_values(row) for row in range(sheet.nrows))

to:

writer.writerows([x.encode('utf-8') for x in sheet.row_values(row)] for row in range(sheet.nrows))

(Props to Marc vT for the suggestion.)