parsing excel documents with python

Question

I want to parse excel document to lists in Python. Is there a python library which is helpful for this action? And what functions are relevant in that library?

possible duplicate of [How can I open an Excel file in Python?](http://stackoverflow.com/questions/3239207/how-can-i-open-an-excel-file-in-python) — orlp, Sep 10 '11 at 15:37

score 51 · Accepted Answer · edited May 23 '17 at 12:03

51

You're best bet for parsing Excel files would be the xlrd library. The python-excel.org site has links and examples for xlrd and related python excel libraries, including a pdf document that has some good examples of using xlrd. Of course, there are also lots of related xlrd questions on StackOverflow that might be of use.

One caveat with the xlrd library is that it will only work with xls (Excel 2003 and earlier versions of excel) file formats and not the more recent xlsx file format. There is a newer library openpyxl for dealing with the xlsx, but I have never used it.

UPDATE: As per John's comment, the xlrd library now supports both xls and xlsx file formats.

Hope that helps.

edited May 23 '17 at 12:03

Community

1
1

answered Sep 10 '11 at 15:44

Mark Gemmill

5,889
2
27
22

@John Machin, the principal maintainer of xlrd, has been known to frequent SO. – PTBNL Sep 10 '11 at 15:56
1

Related question concerning xlsx http://stackoverflow.com/questions/4371163/reading-xlsx-files-using-python – dgorissen Oct 19 '11 at 16:31
8

`xlrd` now reads both .xlsx and .xls files. – John Y Jan 17 '13 at 16:53
4

xlrd no longer supports xlsx. The last version supporting xlsx is 1.0.2 – L. P. Feb 16 '21 at 20:54

wordsforthewise · Answer 2 · 2019-02-20T21:59:39.630

28

The pandas library has a quick and easy way to read excel. If it's mostly just data and nothing too complicated it'll work:

import pandas as pd
ex_data = pd.read_excel('excel_file.xlsx')

It reads it into a pandas DataFrame, which is handy for data munging, etc.

To go to a list:

ex_data['column1_name'].values.tolist()

If you have multiple tables and things in each worksheet then you may want to use another library such as xlrd or openpyxl.

edited Feb 20 '19 at 21:59

answered Jan 06 '17 at 21:08

wordsforthewise

13,746
5
87
117

1

pandas library depends on xlrd < 2.0 or openpyxl to read xslx files under the hood – Yuriy Petrovskiy Sep 06 '21 at 12:04

score 14 · Answer 3 · edited Apr 07 '19 at 12:21

14

openpyxl is a great library and supports read/write to 2010 xlsx files.

sample parsing code

from openpyxl import load_workbook
wb = load_workbook('Book1.xlsx')
ws = wb.active
for row in ws.iter_rows():
   for cell in row:
     print cell.value

sample writing code

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

wb = Workbook()

dest_filename = 'empty_book.xlsx'

ws1 = wb.active
ws1.title = "range names"

for row in range(1, 40):
    ws1.append(range(600))
wb.save(filename = dest_filename)

you can read more here: https://openpyxl.readthedocs.io/en/stable/index.html

edited Apr 07 '19 at 12:21

mohammad RaoofNia

317
1
12

answered Sep 12 '15 at 06:28

Ryu_hayabusa

3,666
2
29
32

2

And is the recommended library on python-excel.org – FelixEnescu Nov 28 '17 at 20:26
thanks for the sample code, the docs are lacking. – pastephens Apr 02 '19 at 22:55
I am a developer just researching parsing techniques. This is excellent – Jason V Oct 17 '19 at 14:45

James Atwood · Answer 4 · 2015-09-03T15:06:20.177

6

xlrd is great for simple tasks, but if you need to work with any of Excel's deeper functionality (macros, advanced plotting, etc), and you are working on a windows machine, you can use the pywin32 library to control the win32com layer. This provides access to just about everything that can be controlled via macros / Visual Basic.

edited Sep 03 '15 at 15:06

answered Sep 10 '11 at 16:09

James Atwood

4,289
2
17
17

the link is dead now – kmonsoor May 28 '15 at 16:10

score 2 · Answer 5 · answered Sep 10 '11 at 17:29

2

If you want to parse xlsx try python-xlsx

answered Sep 10 '11 at 17:29

Vader

3,675
23
40

Adam Lewis · Answer 6 · 2013-01-17T20:53:43.553

-1

pyExcelerator does not seem to be maintained any more, but I have been using it for quite some time and have come to really like it.

Key Points:

Platform independent
Does not require Excel to be installed (meaning does not us COM communications)

Update

All of my new projects have moved to xlrd.

edited Jan 17 '13 at 20:53

answered Sep 11 '11 at 05:36

Adam Lewis

7,017
7
44
62

This is worse in every way than the newer `xlrd`. – John Y Jan 17 '13 at 16:51
1

@JohnY I would have to agree with you at this point. This still however worked at the time. – Adam Lewis Jan 17 '13 at 20:52

parsing excel documents with python

6 Answers6

Linked