Read Data From Excel

Introduction

This page describes how to configure Provar to test using data from Excel.

Demo: Data Driven Testing (3:27)

Use the following links to navigate to the relevant section:

Add Parameter Value Source

In order to read from an Excel sheet, first get your Excel file ready on your local machine.

It is 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 useful to do it on your For-Each test step, if you are using 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:

You can populate this as follows:

  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 the ‘Values Range’ fields.
  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.
  3. In the ‘Name’ field, select the orientation of the headers.
  4. In the ‘Where’ field, specify any filter you want to be applied to the data being imported.
  5. In ‘Variable’, specify your preferred object name for the results (the default is ‘ExcelData’).
  6. Then parameterize the Test Case.

Here is an example Excel sheet with the corresponding Parameter Value Source parameters populated below:

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 API, to loop through multiple rows, and a Create Test Step to create the data at the API level.

  1. Use the Variable picker to access the fields in the ‘ExcelData’ object.
  2. Specify the list Name (e.g. ‘ExcelData’) and the Value Name in the For Each API. (The Value Name is the name for the current iteration.)
  3. Use the full stop to access the column headings:

Learn more

If you’d like to learn more about data-driven testing, why not have a look at our Quickstart Extension Guide? This guide provides a step-by-step introduction to Data Driven Testing, with guided lessons for reading data from Excel, writing data back to Excel, and a practise exercise where you can test your skills. Visit the Quickstart Extension page for more information.

Reading JSON

As of Provar version 1.9, Provar also supports the reading of JSON files using Parameter Value Source in much the same way as the reading of an Excel file. The GSON library is used to parse the JSON.


To read a JSON file, select ‘JSON File’ from the Source Type dropdown.

Then set the filepath:

Note that absolute and relative filepaths are both supported.

Example absolute path:

Example relative path:

If the JSON file is nested, the parameter names will be presented as parentName.childName in the ‘Where’ parameter:

Then define your where condition as wanted, for example:

Then provide the variable name for where the data values should be stored, and define the scope of the variable:

Then save the test.

Once the test is run, if the ‘Where’ condition is met, a hierarchical list (or lists) will be returned representing the JSON record.

For example:

Reading CSV

As of Provar version 1.9, Provar also supports the reading of CSV files using Parameter Value Source in much the same way as the reading of an Excel file.

To read a CSV file, select ‘CSV File’ from the Source Type dropdown.

Then set the filepath:

Note that absolute and relative filepaths are both supported.

Example absolute path:

Example relative path:

To check the values contained in the CSV, select the right arrow button:

Then define your where condition as wanted. Note that the parameters can be populated automatically from the file by clicking the left arrow button:

Then provide the variable name for where the data values should be stored, and define the scope of the variable:

Then save the test.

Once the test is run, if the ‘Where’ condition is met, a hierarchical list (or lists) will be returned representing the CSV data.

For example: