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

[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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s