+91 9790664230
kumar.arun211@gmail.com

Read data from excel in python scripts

Blog to learn python selenium from java selenium

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.

 

http://allselenium.info/read-data-from-excel-in-python-scripts/

Steps:

Step 1: The first step is to import required library, the xlrd library. We use xlrd package to read the 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

rowcount = sheet.nrows #Get number of rows with data in excel sheet
colcount = sheet.ncols #Get number of columns with data in each row. Returns highest number

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):
        data = sheet.cell_value(curr_row, curr_col) # Read the data in the current cell
        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")

rowcount = sheet.nrows #Get number of rows with data in excel sheet
colcount = sheet.ncols #Get number of columns with data in each row. Returns highest number

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):
        data = sheet.cell_value(curr_row, curr_col) # Read the data in the current cell
        print data
        row_data.append(data)

    result_data.append(row_data)

We can use the data read from excel file in our scripts directly or we can create a reusable component to read data by passing required details. Let us know your comments below.

 

One Response

  1. […] Read data from excel in python scripts June 12, 2018 […]

Leave a Reply

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

© 2018 Copyrights. All Rights Reserved. Arunkumar Velusamy