CODEWITHSUNDEEP
pythonpython-2.7csvxlrd
user6727547
user6727547

Reputation:

Convert xlsm to csv

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)

Upvotes: 2

Views: 1663

Answers (2)

jjk
jjk

Reputation: 61

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.)

Upvotes: 0

Marc vT
Marc vT

Reputation: 506

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

Upvotes: 1

Related Questions