Burke Software Blog

Pure python - convert any spreadsheet format to list

February 13, 2013

If you ever import data in python, your probably need to convert ods, xls, etc to a python list. Here's a quick hack to support ods, xls, xlsx, and csv. I'll assume you just want the data from the list, not work with format specific formula, graphs, etc. It has dependencies of 'openpyxl', 'odfpy', 'xlrd' and this wrapper for the gross odfpy

[python]file_ext = filename[-3:] data = [] if file_ext == "xls": import xlrd wb = xlrd.open_workbook(filename) sh1 = wb.sheet_by_index(0) for rownum in range(sh1.nrows): data += [sh1.row_values(rownum)] elif file_ext == "csv": import csv reader = csv.reader(open(filename, "rb")) for row in reader: data += [row] elif file_ext == "lsx": from openpyxl.reader.excel import load_workbook wb = load_workbook(filename=filename, use_iterators = True) sheet = wb.get_active_sheet() for row in sheet.iter_rows(): data_row = [] for cell in row: data_row += [cell.internal_value] data += [data_row] elif file_ext == "ods": from odsreader import ODSReader doc = ODSReader(filename) table = doc.SHEETS.items()[0] data += table[1] return data[/python]

Rock python in fence 1