You use Excel variables to retrieve values (logins, passwords, product IDs and so on) from Excel files and insert them into scenarios. Excel variables are similar to list variables, but are linked to a column in an Excel file, not to a static list. Since values are stored apart from your test project, there is no need to modify the project when you need to add or remove values.
Each Excel variable also defines the order in which different virtual users access values from the list. For example, you can configure the variable so each virtual user can use only one specific value from the spreadsheet or each virtual user can access the spreadsheet sequentially and take the next value every time it simulates a request to the server.
You can use Excel variables to specify query string parameters of GET requests, as well as form fields sent via POST requests, for example:
GET /find/?first=@FirstName&last=@LastName HTTP/1.1
|We recommend that you select a variable whose data type matches the parameter data type. If LoadComplete fails to convert the variable value to the parameter data type, the test server may raise an error.|
Depending on the value access type specified for the list, different virtual users will be able to play back the parameterized request using different parameter values, for example:
and so on
This way, the behavior you will get is more realistic than that when using the same recorded value for all virtual users.
For more information on using variables in requests, see About Variables.
To use Excel variables, you must have any of the following software installed on your computer:
Microsoft Office Excel 2007–2013.
Microsoft Access Database Engine 2010 Redistributable. You can download it on the Microsoft web site:
2007 Office System Driver: Data Connectivity Components which can be obtained from Microsoft’s web site:
You create and edit Excel variables using a wizard that assists you with filling in and verifying variable parameters. The wizard includes the following pages:
The variable name that will be used to refer to this variable in request parameters. The name can include letters (A..Z, a..z), digits (0..9) and underscores ( _ ) and must be unique within the project.
The fully-qualified name of the Excel file. The file can be stored on the local computer (for example, C:\Tests\Data.xls) as well as in a network share (for example, \\Server\SharedFolder\TestData.xls). The latter is recommended if you are going to copy or move your test project to other computers -- in this case, the Excel file name will be computer-independent.
First line contains column names
Select this option if the first line in the Excel file is used as a header and specifies custom names for the columns. This will instruct LoadComplete to use values from the first line as column names. Otherwise LoadComplete will use the default column names - F1, F2 and so on.
The sheet, among those defined in the Excel file, to retrieve values from. You can enter the sheet name manually or select it from the drop-down list.
The name of the column that the variable will retrieve values from. You can enter the column name manually or select it from the drop-down list.
Value access type
Specifies the order in which virtual users take values from the worksheet column:
Item index equals virtual user index.
Item index equals virtual user index within a station.
Next value on each use.
Next value on each use within a station.
Next value on each use by a virtual user.
For complete information on these settings, see Value Access Types.
This page displays a preview of the selected Excel sheet column. When you run the load test, the variable will return individual values from this column according to the Value access type option.
LoadComplete fully supports Excel files containing values with ASCII characters only (English letters, numbers and punctuation marks). It may handle values containing characters from other character sets incorrectly.
The cells within the same column should contain the same data type, for example, only text or numbers.
If a column contains text values along with numbers, the Excel data provider may return empty values instead of the text. This happens if the first few rows contain more numbers than text and depends on how the Excel data provider determines the type of column data. For more information on this, please see the “A Caution about Mixed Data Types” section in the following Microsoft KB article:
To avoid the issue with mixed data, you can store numeric values as text. To do this, use one of the following techniques:
Prefix the numbers with an apostrophe ( ' ), for example '1.
Format the cells containing numeric values as text:
Select the cells containing numbers.
Right-click the cells and select Format Cells from the context menu.
On the Number tabbed page, select the Text category.
An Excel variable fetches values from the specified file on each use. Thus, changes made to the Excel file during the test run affect the variable’s value set.
A Excel variable fetches all needed values from the specified file upon the test start:
External changes made to the Excel file during the test run will not affect the variable values.
If you simulate virtual users on remote stations or on cloud computers, you will not have to configure access to the file from them. Only the computer, from which you control the test run, must have access to the file.