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,
I’m a newbie to Python
Should my default directory be where the python.exe is found or somewhere else?
Your help is greatly appreciated
Stan
You have two options
1. Either specify the exact path to the excel file
2. Or maintain the same directory for both the excel file and the .py file
I think there is a mistake in the code block “Extracting Multiple rows and columns at a time”: the instruction
# for extracting multiple rows at a time
for i in range(sheet.nrows):
print(sheet.cell_value(0, i))
should be replaced by:
# for extracting multiple rows at a time
for i in range(sheet.nrows):
print(sheet.cell_value(i, 0))
Laurent
hi i am learning python now, i am currently using xlrd module to read a file. i have doubt here what if there are multiple sheets in my xls and i need to access a specific sheet based on the sheet name? eb.sheet_by_name() works?