How to Load Test Data from Spreadsheets

Test steps often rely on parameters, such as menu item names, user names, or numeric values. You can either hard-code these values -- or use parameters with custom test data. BlazeMeter can load spreadsheet data either from files with comma separated values (CSV), or from shared folders in your Workspace.

This article covers parameterizing test data using CSV data. Alternatively, you can also parameterize test data in a Taurus YAML script or Generate Synthetic Test Data.

Tip: The Debugger in the Scriptless Scenario Editor is also integrated with CSV files. The debugger uses the provided parameter values just as the standard test execution would.

This article covers the following topics:

1. How to create or edit test data
2. How to create and upload test data files
3. How to use test data parameters
4. How to manage test data

How to create or edit test data using the in-tool editor

1. Open a test and go to the Configuration tab.
2. Click Test Data.
3. Click the Plus button, and click New CSV File (Data Table).
   The CSV Editor opens.

Each column represents a parameter. The first row must contain parameter names. Each row below the first contains parameter values. The CSV editor supports editing, selecting, copying, and pasting text. Note that you cannot edit column headers (parameter names) when the CSV file is loaded from shared folders.

The empty last row and last column are shown only so you can more easily add data. Empty last rows and columns are removed automatically when you save the test data.

• To add a column, start typing in the last empty column, or right-click a cell and use the context menu. Press tab to save the cell.
• To add a row, start typing in the last empty row, or right-click a cell and use the context menu. Press tab to save the cell.
• To define a new parameter and its values, enter the name into a column's header (without "${}" characters), and enter the values in the rows below the name.
• To fill neighboring cells with an incremental series of numbers, select consecutive cells that contain numbers, and drag the cell corner down or to the side.
• To delete rows or columns, right-click a cell and use the context menu.

When you're done, click Save to save the file in CSV format. Or click Cancel to discard your changes.

How to create and upload test data files

CSV is a human-readable plain-text file format for simple tabular data where fields are separated by commas. You can create CSV files outside BlazeMeter, either in a text editor or in MS Excel.

To create test data in a text editor:

Define the test data in a plain-text file:

• For GUI Functional tests, API Monitoring, and Service Virtualization, the first row of CSV data must be the header containing parameter names.
  For Performance tests, a header row is expected by default, unless specified otherwise in the JMeter CSV Dataset Config.
• In the second row and following, enter the respective parameter values, separated by commas. Each column contains values for one parameter.
• If a field contains a line-break, double-quote, or comma, wrap the field in double-quotes.
• If a quoted field contains a double-quote, replace it by two double-quotes.

Save the plain text file with a .csv suffix.

Return to BlazeMeter to atach it to a test:

1. Open a test and go to the Configuration tab.
2. Click Test Data and then the Plus button.
3. Click Import CSV File, and select the CSV file from your local machine to upload it.
4. Define the number of test data rows (iterations) to use.

In the following three-column example, the parameter names are name, city, year:

name,city,year
Kim,"Canberra, Australia",2019
Jan,"Paris, France",2020

To create test data in MS Excel:

You can also use MS Excel to provide the test data in the described format: The column headers must be parameter names (in this example, the parameter names are name, city, year), and the rows below contain the values.

Export the spreadsheet from Excel as a CSV file. Other formats than CSV are not supported as test data and will lead to your parameters not being resolved properly.

How to use test data parameters

After defining and uploading test data, you can reference the values in parameters of action steps in test scripts. For example, you reference the columns name, city, and year from your test data as ${name}, ${city}, and ${year} in your test.

How to manage test data

When you upload multiple CSV files, the test definition uses parameter values from all files. Parameter names must be unique, that means, if the same column header is used in multiple files, the last uploaded data definition has priority.

Open the Test Data pane to see which data files are active for a specific test. You can also create, edit, preview, download, and delete test data here. To review shared CSV files, click Test Data in the top menu bar for your component.