Using Excel as test data source

Use Cases

The two main use cases for Excel data are:

• Use different test data as input for each test case execution (e.g. login with different credentials, enter different data in a form etc.)

• During a test execution loop over all data (e.g. verify all rows in a table, )

Excel content requirements

To be able to import your Excel file please make sure to meet the following requirements:

• Row 1 contains the name of each column. Make sure each name is unique. These names will be used as variable names in the automated test case.

• There are no empty rows in between data rows.

• Only the first sheet of the excel will be imported

• Save the file as .xlsx

• The name of the file will also be imported and used as a prefix for all variables.

The following image displays a simple example: Row 1 contains the header names and there a 3 data rows.

Import Excel

To import your Excel file into the Test Case click on the icon in the Test Case Designer (Designer Version 4.8 or newer required)

Before the actual import a dialog will show you what data has been found. If this preview does not show the expected data please check the chapter Excel content requirements.

The "Manage Variables" dialog will be opened and shows a preview of the currently selected row. Note that the value in "Selected Row" corresponds to the row number in Excel. If you start an execution of your Test Case the values of that row are loaded into the corresponding Variable keys.

If you change the selected Row make sure to close the Dialog with the Save button.

Use Variables in a Test Case

The values of the currently selected row are loaded as Variables. These variables can be used as values for parameters. In this example we selected the Operation "Enter" on the Textbox "Email Address". Click on the "Use variable" icon to switch the parameter from free text entry to variable.

Test execution with Excel data (Visual Studio)

After selecting the Excel Variable as parameter in the Test Case designer, start an execution and the value of the excel row 2 is entered into the textbox.

To execute the Test Case with different test data, reopen the "Manage variables" dialog and change the selected row.

Test execution with Excel data (Portal)

When you are done with the Test Case Design, upload it to the portal as usual.

Open the Test Case in the Portal and switch the Test Type to Performance and enter the number of Data Rows in the the Instances field (in this example we had 3 data rows in the excel)

Start an execution, multiple instances will be started and all executions are run in parallel. In the results you will have multiple report instances, each using a different excel row as test data.

Looping interactions with different Test Data

The second use case for excel test data is to repeat an interaction with different data during a single test execution. In this example we will verify the rows of a table in our application with the test data given in an excel file.

Please note that in this use case the excel also has to meet the same requirements and can be imported in the same way. Just the "Selected Row" in the variables dialog has no impact as all rows will be used in the loop.

Use the Variables from the excel as Parameter values and also select the Checkbox "Loop over all TestData"

Update Excel data

To update the Excel data simply repeat the import of the updated file.

A warning dialog will be displayed that your data will be updated.

The columns will also be updated. Note that if you remove a column Test Cases that used this property will need to be changed before you can successfully run it again.