Looking for something in particular?

Reading Data from Excel

Data-driven testing is a helpful feature for the following scenarios:

  • When business users would like to have ownership over the test data in your automated tests
  • When you have multiple data permutations and you would like to loop through data sets
  • When you would like to perform various calculations on your data and use Excel as a method of deriving the fields

Automation has Read and Write test steps to read data from an Excel sheet or a database and write data to an Excel spreadsheet.

This page describes how to configure Automation to test using data from Excel. You can also refer to Write Data Into Excel for more information.

To read from the Excel sheet, first, get your Excel file ready on your local machine.

Note: It is a good practice to save the file in the templates folder in your workspace.

Next, read the Excel file into your test case using Add Parameter Value Source. You can do this from any test step, but you will often find it helpful to do it on your For Each test step if you use one. (For Each is often used within Data-Driven Testing to iterate the same action through each data row.)

To read the Excel data into your Test Case, click the Add Parameter Value Source icon () in the top-right of the Test Step.

This will add the following new section to your Test Step:

Setting up test parameter to read data Excel

You can populate this as follows:

Step 1: If you are on a Windows machine, navigate to your Excel file and highlight the range of cells you want to read into Provar. Then select CTRL+C to copy. Navigate to Provar and select the Populate From File icon (). This will populate the File, Worksheet, and Values Range fields.

Step 2: If you are using a Mac, you will need to populate File, Worksheet, and Values Range manually. Use File Chooser () icon to help you set the File path.

Step 3: In the Name field, select the orientation of the headers.

Step 4: In the Where field, specify any filter you want to be applied to the data being imported.

Step 5: In Variable, specify your preferred object name for the results (the default is ExcelData).

Step 6: Parameterize the Test Case.

Add to Test Case

Once you have read the data from the Excel sheet, you need to use it in your test case. This is typically done using a For Each or While test step to loop through multiple rows and create a test step to make the data

Step 1: Use Content Assist to access the fields in the SourceData object.

Step 2: Specify the list Name (e.g., SourceData) and the Value Name For Each test step. (The Value Name is the name for the current iteration.)

Step 3: Use the full stop to access the column headings.

Use the full stop to access the column headings to read data Excel

Load Multiple Records

To increase efficiency, Automation allows you to loop through rows of a spreadsheet to iterate upon the data. This means that the same actions will be performed for every row in the spreadsheet. By using a single test case for many input parameters, you can save time and minimize errors.

For example, this set of data will load in three rows of data.

In order to make the test run through all the records, we need a test step that will loop through the new rows we added. The For Each step in the Test Palette allows us to execute the steps for each record.

Step 1: Drag For Each step from the Test Palette into your test.

Step 2: Now that we have the For Each step added to the test, we need to fill in the required fields. The first field is the List field. Our list is already loaded, so use Content Assist to add SourceData as the list.

Step 3: Now that we’ve provided the list of records, let’s look at the other fields.

  • From Item tells the loop we’re starting with the first record
  • Value Name field is the name used to refer to each row of data
  • Continue on Failure will keep looping through rows even if one iteration fails

Since we’re referring to rows of data, let’s call this Row for the value name. 

Step 4: Now that we’ve set the parameters, you need to add the steps you wish to repeat inside the For Each step. To maintain order, make sure to select all of the steps and move them together.

Step 5: Before running the test, we need to update the steps that refer to our rows. In the For Each settings, we provided the Value Name as Row. Now we can call it in the test steps we want to loop. Use Content Assist to call Row for the value. After choosing Row, use dot notation to refer to the field you wish to use.

Step 6: Update the other steps under For Each with the Row variable and the dot notation for the correct field. When you are done, your test case should look something like this. 

Now that your test is set up, it will execute all of the steps within the For Each loop for all of the rows of data on your spreadsheet.

For more information on Data-Driven Testing, check out the University of Provar course Provar Automation Essentials: Data-Driven Testing

Review Provar on G2
Documentation library

Trying to raise a case with our support team?

We use cookies to better understand how our website is used so we can tailor content for you. For more information about the different cookies we use please take a look at our Privacy Policy.

Scroll to Top