Excel Variables

Applies to LoadComplete 4.97, last modified on May 20, 2019

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:

GET /find/?first=John&last=Smith
GET /find/?first=Susan&last=McLaren
GET /find/?first=Charles&last=Dodgeson
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.

Requirements

To use Excel variables, you must have any of the following software installed on your computer:

Parameters

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:

Excel File Page

Name

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.

File name

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.

Select Database Object Page

Object

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.

Specify Column and Selection Type Page

Column

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.

  • Random.

For complete information on these settings, see Value Access Types.

Preview Data Page

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.

Remarks

  • 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:

    http://support.microsoft.com/kb/257819

    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.

See Also

Using External Data Sources
CSV Variables
Database Variables
List Variables

Highlight search results