Read data from excel in python scripts
We often encounter scenarios where we have to read data from excel file stored. In this article, let’s see how we can read data from a xls(Excel) file and use the data in our Python scripts. At the end of this article, you will learn how to open a xls file, read data from a particular row/column.
Example:
Let us assume a scenario – we have to test login functionality of particular application. We need multiple test datas, i.e. user credentials to test this scenario. User credentials could be valid or invalid depends on the test case. We can hard code test data in test scripts but it is not a recommended practice considering reusability and maintanence.
In this example we have the Excel file that looks something like this as shown in the figure. We want to read the username/password to perform all the validations. Let us develop the code to read data from Column B and Column C in to 2 variables username and password.

Steps:
Step 1: The first step is to import required library, the xlrd library. We use xlrd package to read data from Excel spreadsheets.
import xlrd
Step 2: Second step is to open a Excel(.xls) file, which has data, from current (Pass filename with extension as parameter in case of script and data file exists in same directory) or other directory (Pass entire file path as parameter in case of script and data file exists in different directories).
workbook = xlrd.open_workbook(“filename.xls”);
or
workbook = xlrd.open_workbook(“/path/filename.xls”);
workbook = xlrd.open_workbook("DataFile.xls") sheet = workbook.sheet_by_name("UserCredentials") #Read data from Excel sheet named "UserCredentials"
Step 3: In the current example, we can see that data is saved from Column A to Column D. We have to read multiple rows and columns but do not exactly know how many rows and columns. Hence we need to first obtain the number of rows and columns
# Get number of rows with data in excel sheet rowcount = sheet.nrows # Get number of columns with data in each row. Returns highest number colcount = sheet.ncols
Step 4: The last step is to parse through each row and each column and read the data in the current cell.
result_data =[] result_data =[] for curr_row in range(1, rowcount, 1): row_data = [] for curr_col in range(1, colcount-1, 1): # Read the data in the current cell data = sheet.cell_value(curr_row, curr_col) print(data) row_data.append(data) result_data.append(row_data)
We have devised a logic in inner for loop to leave first column(contains serial number) and last column(contains description) as they are present there for our understanding.
So the complete script looks like below.
import xlrd workbook = xlrd.open_workbook("DataFile.xls") sheet = workbook.sheet_by_name("UserCredentials") # Get number of rows with data in excel sheet rowcount = sheet.nrows # Get number of columns with data in each row. Returns highest number colcount = sheet.ncols print(rowcount) print(colcount) result_data =[] for curr_row in range(1, rowcount, 1): row_data = [] for curr_col in range(1, colcount-1, 1): # Read the data in the current cell data = sheet.cell_value(curr_row, curr_col) print(data) row_data.append(data) result_data.append(row_data)
We can read data from excel file to use in our scripts directly or we can create a reusable component to read data from excel by passing required details. Let us know your comments below.
2 Responses
simply wow . . so easy
Thanks arun. Great help
keep posting