Reading an excel sheet using “xlrd” module in Python 3.x or earlier

In this tutorial, we will be learning how we can read the data from an excel spreadsheet file in Python. For this purpose, we use the inbuilt module “xlrd” in Python 3.x or earlier

Read data from excel file in Python using xlrd module

Using xlrd module, one can easily retrieve information from a spreadsheet. All operations like reading, writing or modification of the data can be done in Python 3.x. or earlier. User can go through various spreadsheets. He can also access data based on some criteria or can modify some rows and columns.

Read my next tutorial to learn, Writing to an excel sheet using xlwt module in Python 3.x or earlier

Installation of xlrd – Python

With the help of pip, we can install the module very easily.
For Windows User: Command Line run
For Linux User: Terminal run

(>>> or $) pip install xlrd

Python program to fetch data from an Excel File

# Reading an excel file using Python 3.x. or earlier 
import xlrd as x
  
# Give the address of the file on the local computer, i.e, path location
loc_file= ("<location of file>") 

# To open Workbook we declare a hadling variable wb
wb = x.open_workbook(loc_file) 
sheet = wb.sheet_by_index(0) 
  
# Prints the value of element at row 0 and column 0 
print(sheet.cell_value(0, 0))
Output: Element at 0th row and 0th column

Here while importing if the module not found, the error is raised and execution stops. Here with the help of variable ‘wb’ we are storing the open spreadsheet. Sheet specifies which sheet we are opening (currently the sheet at 0th index). The last statement prints the value of the element by the help of row and column arguments specified.

Also, learn,

Find Total no of Rows and Columns of an excel file in Python

# Program to extract the number of rows & columns in Python 3.x. or earlier

import xlrd as x 

loc_file = ("<address of file>")

wb = xlrd.open_workbook(loc_file) 

sheet = wb.sheet_by_index(0) 

# Extracting number of rows 
print(sheet.nrows) 

# Extracting number of columns 
print(sheet.ncols)

Output:

Total no of rows
Total no of columns

Here with the help of sheet.nrows & sheet.ncols we are extracting the total no of rows and columns present in the sheet

Extracting Multiple rows and columns at a time

# Program extracting multiple rows and columns 
import xlrd as x  
loc_file = ("path of file") 
  
wb = x.open_workbook(loc_file) 
sheet = wb.sheet_by_index(0) 
  
# for extracting multiple rows at a time
for i in range(sheet.nrows): 
    print(sheet.cell_value(0, i)) 

# for extracting multiple columns at a time
for i in range(sheet.ncols): 
    print(sheet.cell_value(0, i))

Output:

Value of multiple rows 

Values of multiple columns

Here we have initiated a for loop which helps us access all the values present in rows and columns.
By using the print statement we are displaying multiple rows and columns by the help of sheet.cell_value().

Also, learn,

Leave a Reply

Your email address will not be published. Required fields are marked *