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, Find Test Data (TDM), or Generate Synthetic Test Data.

For GUI Functional tests and Mock Services, the first row of CSV data must be a header containing parameter names. For Performance tests, a header row is expected by default, unless specified otherwise in the JMeter CSV Dataset Config.

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 when you're done.
• To add a row, start typing in the last empty row, or right-click a cell and use the context menu. Press tab when you're done.
• 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 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. Or click Cancel to discard your changes.

How to create and upload test data files

You can also create CSV files, either in a text editor or in MS Excel.

To create test data in a text editor:

1. Define the test data in a plain-text file:
   
   • In the first row, enter the parameter names, separated by commas.
   • In the second row and following, enter the respective parameter values, separated by commas.
   • If a data cell contains commas, enclose the cell content in quotation marks.
2. Save the text file with a .csv suffix.
3. Return to BlazeMeter. Open a test and go to the Configuration tab.
4. Click Test Data and then the Plus button.
5. Click Import CSV File, and select the CSV file from your local machine to upload it.
6. Define the number of test data rows (iterations) to use.

In this 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 as a CSV file. Other formats than CSV are not supported 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 this test. You can also create, edit, preview, download, and delete test data here.