How to read data from excel file in Python

In this article, you will learn how to read data from an excel file using the Python programming language. As we know, Microsoft Excel has been widely used in many different application fields. It is a helpful and powerful program for data analysis and documentation and is rich in features like calculation, graphing tools, pivot tables, producing graphs and charts, and much more.

Install xlrd module

Python provides modules for reading data from excel files. The xlrd module is mostly used to retrieve data from a spreadsheet. We can easily get data based on some criteria. It works same on the excel files .xls or .xlsx. Here is the command to install the xlrd module using the pip tool in a virtualenv. If you want to know more about virtualenv, please follow the article.  

pip install xlrd

You can see the output as in the below screenshot.

Suppose we have the following excel file containing school birthday charts of the June month. With the help of this excel sheet, we will provide different programming examples to extract data.

Read excel file in Python

In the given Python program, we first import the xlrd module and specify the location of the excel sheet. The open_workbook() method of this module opens the excel sheet, and the sheet_by_index() method loads the requested excel sheet. Next, we loop over the rows and print the records.

import xlrd 
  
# the location of the file 
loc = ("birthday_june.xlsx") 
  
# To open Workbook 
wb = xlrd.open_workbook(loc) 
sheet = wb.sheet_by_index(0) 
  
for i in range(sheet.nrows): 
    print(sheet.row_values(i))

The above code returns the following output -

Read particular column in excel using Python

We can easily select particular column or columns from the excel sheet. Suppose we want to select all the second column, i.e., 'Name' from the excel sheet, the code will be-

import xlrd 
  
# the location of the file 
loc = ("birthday_june.xlsx") 
  
# To open Workbook 
wb = xlrd.open_workbook(loc) 
sheet = wb.sheet_by_index(0) 
  
for i in range(sheet.ncols): 
    print(sheet.cell_value(i, 1))

Read particular row in excel using Python

We can easily select particular row or rows from the excel sheet. Suppose, we want to select all the records in the third row from the excel sheet, the code will be-

import xlrd 
  
# Give the location of the file 
loc = ("birthday_june.xlsx") 
  
# To open Workbook 
wb = xlrd.open_workbook(loc) 
sheet = wb.sheet_by_index(0) 
  
print(sheet.row_values(3)) 

Read particular column and row in excel using Python

Suppose, we want to select second column of the second row, then the code should be-

import xlrd 
  
# Give the location of the file 
loc = ("birthday_june.xlsx") 
  
# To open Workbook 
wb = xlrd.open_workbook(loc) 
sheet = wb.sheet_by_index(0) 
  
print(sheet.cell_value(1,1))

The above code returns the following output-

Priska

Related Articles