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.

Example of a valid test data excel file

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

Click on the "Use variable" icon to switch a parameter from free text entry to variable selection.

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.

The "LoginData:Email" is used a parameter value

Test execution with Excel data (Visual Studio)

Result of the execution

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.

reopen the dialog

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

Change the selected row and close the dialog with save

Row 3 of the excel is now used as test data, so a different email is entered

Test execution with Excel data (Portal)

Upload the Test Case to the 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)

Select Test Type "Performance" and the number of instances which equals the number of data rows in the Excel (Column header row not included)

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.

Table in our test application which needs to be verified

Excel data used in this example

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.

The selected Row can still be used as preview for the data, but the loop will use all rows of the excel

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

Select the "Loop over all SignInData" Checkbox, it will become visible as soon as a Excel variable is selected as a parameter

During the execution all 3 rows are searched and verified

The Result of the execution shows that the 3 rows given in the excel file were verified

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.