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.