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

By David

I am a supporter of free software and run Burke Software and Consulting LLC. I am always looking for contract work especially for non-profits and open source projects. Open Source Contributions I maintain a number of Django related projects including GlitchTip, Passit, and django-report-builder. You can view my work on gitlab. Academic papers Incorporating Gaming in Software Engineering Projects: Case of RMU Monopoly in the Journal of Systemics, Cybernetics and Informatics (2008)

Leave a comment

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 )

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

%d bloggers like this: