Pure python – convert any spreadsheet format to list

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

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

Rock python in fence 1

Leave a Reply

Your email address will not be published. Required fields are marked *