iPirate iPirate - 2 months ago 39
Python Question

How to extract sheet from *.xlsm and save it as *.csv in Python?

I have a *.xlsm file which has 20 sheets in it.
I want to save few sheets as *.csv (formatting loss is fine) individually.
Already tried xlrd-xlwt and win32com libraries but could not get through.
Can anybody please provide a code snippet which does the above processing in Python? I have other python dependencies so no other language would work.
Thanks

Answer

xlrd should work fine on xlsm files as well. I tested the code with a random xlsm file, and it worked perfectly.

import csv
import xlrd

workbook = xlrd.open_workbook('test.xlsx')
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))

If you've encoding issues, try the code below:

import csv
import xlrd

workbook = xlrd.open_workbook('test.xlsm')
for sheet in workbook.sheets():
    if sheet == "Sheet_name_to_be_saved":
        with open('{}.csv'.format(sheet.name), 'wb') as f:
            writer = csv.writer(f)
            for row in range(sheet.nrows):
                out = []
                for cell in sheet.row_values(row):
                    try:
                        out.append(cell.encode('utf8'))
                    except:
                        out.append(cell)
                writer.writerow(out)