How to read data from excel or spreadsheet file with Python

We all are used to managing data using Excel sheets or spreadsheets, sometimes it becomes necessary for us to use the data stored in excel sheet for some computations using python. 

In this tutorial we will be reading the data in an excel file using python xlrd module.

According to official documentation at pypi xlrd is:

xlrd module is a library to extract data from Excel sheets or spreadsheet files.

Before we start we will have to install this module so that it can be used in our python script. If you have not yet installed this module on your computer then you can do the following:

$ pip install xlrd

You can also install using easy_install or by downloading the package from pypi, all of them have the same effect.

We will start with IDLE so that we can understand each and every command in the interpreter easily.

We will using roc.xls for this tutorial and the spreadsheet(roc.xls) contains the following data:

Roll Number	Name	Marks	Rank
1	Pearson	86	4
2	John	89	3
3	Habib	64	5
4	Venkat	98	2
5	Suri	100	1

First import xlrd module:

>>> import xlrd

Now we will open the workbook:

>>> wb = xlrd.open_workbook('roc.xls')

We will see the list of sheets present in the workbook:

>>> wb = xlrd.open_workbook('roc.xls')
>>> sh = wb.sheet_names()
>>> sh
[u'Sheet1']

Next we will open the sheet(or worksheet) in the spreadsheet.
Opening the sheet by name:

>>> sh = wb.sheet_by_name('Sheet1')

Opening the sheet by index:

>>> sh = wb.sheet_by_index(0)

After we have opened the sheet we will have to read the data as this is our main goal. You can do that in many ways:
Read data from a cell:

>>> sh.cell_value(0,2) #sh.cell_value(row,column)
u'Marks'

>>> sh.cell(1,2).value #sh.cell(row,column).value
86.0

Read data from each row:

>>> sh.row_values(2)
[2.0, u'John', 89.0, 3.0]

Read data from a column:

>>> sh.col_values(2)
[u'Marks', 86.0, 89.0, 64.0, 98.0, 100.0]

Finding the number of columns present in the spreadsheet:

>>> sh.ncols
4

Finding the number of rows present in the spreadsheet:

>>> sh.nrows
6

If we want to get the whole data, then we can use for loop to loop through cells or rows or columns. This can be done as follows:
For loop through cells:

>>> for i in range(sh.nrows):
 for j in range(sh.ncols):
  print sh.cell_value(i,j) #Or can be written as sh.cell(i,j.value)

  
Roll Number
Name
Marks
Rank
1.0
Pearson
86.0
4.0
2.0
John
89.0
3.0
3.0
Habib
64.0
5.0
4.0
Venkat
98.0
2.0
5.0
Suri
100.0
1.0

For loop through rows:

>>> for i in range(sh.nrows):
 print sh.row_values(i)

 
[u'Roll Number', u'Name', u'Marks', u'Rank']
[1.0, u'Pearson', 86.0, 4.0]
[2.0, u'John', 89.0, 3.0]
[3.0, u'Habib', 64.0, 5.0]
[4.0, u'Venkat', 98.0, 2.0]
[5.0, u'Suri', 100.0, 1.0]

For loop through columns:

>>> for i in range(sh.ncols):
 print sh.col_values(i)

 
[u'Roll Number', 1.0, 2.0, 3.0, 4.0, 5.0]
[u'Name', u'Pearson', u'John', u'Habib', u'Venkat', u'Suri']
[u'Marks', 86.0, 89.0, 64.0, 98.0, 100.0]
[u'Rank', 4.0, 3.0, 5.0, 2.0, 1.0]

Usually I will be using the for loop through rows. Now putting all together in a script, what we have learnt:

import xlrd

#Open the workbook
wb = xlrd.open_workbook('roc.xlsx')

#Open the sheet by index
sh = wb.sheet_by_index(0)

#Read data with for loop
for i in range(sh.nrows):
    print sh.row_values(i)

The output for the above code will be as follows:

>>> 
[u'Roll Number', u'Name', u'Marks', u'Rank']
[1.0, u'Pearson', 86.0, 4.0]
[2.0, u'John', 89.0, 3.0]
[3.0, u'Habib', 64.0, 5.0]
[4.0, u'Venkat', 98.0, 2.0]
[5.0, u'Suri', 100.0, 1.0]

I would like to end this tutorial here because this is a beginner tutorial and I don't want to confuse you by introducing you to more and more methods and classes. If you want to learn more then see the further tutorials in which I will be using a few more xlrd module functions.

As always I have tried to explain each and every thing in this post in such a way that it is easy for everyone to understand. But if you haven't understood anything or have any doubt then please do comment in the comment box below. You can also contact me if you are want to. I will reply to you in both the ways.

Please do comment on how I can improve this tutorial such that it will be useful even for very beginners.

"Let us all together improve by sharing our knowledge."

Thank you. Have a nice day :)