Reading XLSX files in Python with xlrd

This is useful for import scripts. The xlrd [https://pypi.python.org/pypi/xlrd/] python package handles the hard work here, this snippet is for future reference.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
import xlrd

path = 'workbook.xlsx'

workbook = xlrd.open_workbook(path)
worksheet = workbook.sheet_by_index(0)

# Change this depending on how many header rows are present
# Set to 0 if you want to include the header data.
offset = 1

rows = []
for i, row in enumerate(range(worksheet.nrows)):
    if i <= offset:  # (Optionally) skip headers
        continue
    r = []
    for j, col in enumerate(range(worksheet.ncols)):
        r.append(worksheet.cell_value(i, j))
    rows.append(r)

print 'Got %d rows' % len(rows) - offset
print rows[0]  # Print column headings
print rows[offset]  # Print first data row sample

Assuming a column layout such as:

First Name Last Name Email
Jane Smith jane@example.com
John Sanders john@example.org
1
2
3
4
5
6
from django.contrib.users.models import User

for row in rows:
    User.objects.create(first_name=row[0],
                        last_name=row[1],
                        email=row[2])