Description

An Excel List G-Query pulls rows of data with one or more columns from a specified sheet within an Excel file into memory as a list of data. The queried data is populated for the defined Domain Attributes during test data generation and blended with synthetically generated data.

When Should an Excel List Query Be Used?

Any time you want to query real data from a sheet within an Excel file and blend it with synthetically generated data.

Excel List Query Configuration Components

A Test Data Query configuration has the following primary components:

Parameters - Define the resource path, subdirectory, file name, and sheet name for the Test Data Query.
Excel Column Names - Select an Excel file to be imported for the Test Data Query. Displays the column names within the Excel file after importing.
Domain Attributes - Defines the Attributes that will be populated by the real data obtained from the imported file.

This G-Query will query three columns within an Excel file and blend the queried data with synthetically generated data.

Simple Use Case Example

A tester wants to query a CSV file for the Customer's first and last name. They want to blend the queried data with the following synthetically generated data:

social security number (ssn)
date of birth (dob)
phone number

Additionally, they want the generated output format to be an XML file that contains all 25 records that are in the source Excel file.

Step 1 - Setup Project

For this example, the tester will need to complete the following:

Create a Customer Domain with the following Attributes.
Set the Domain loopCount. It is set to '25' because that's how many records are in the file.
Assign the XMLFileReceiver to the Domain for XML formatted output.
Create a Scenario for the Domain.
Configure the Receiver parameters.

Step 2 - Add an Excel List Query

Click on Add G-Query in the dashboard.
Enter the Name and a Description.
Select Excel List and click Save.

Step 3 - Configure Test Data Query Parameters

Query Parameters define any resources and variables to be used during the query.

Use the fields and drop-down menus to modify query parameters.
Click Save once finished.

Parameter	Description
path*	Defines the path for the Excel file.
subDir	Defines an optional subdirectory under the resourcePath where the Excel file exists.
fileName*	Defines the name of the Excel file located on the user's system.
sheetName*	Defines the sheet name in the Excel file.
bufferSize*	Defines how many rows to keep in memory at once from the Excel file to avoid the memory out-of-exception error. The default value is 1000.
setLoop*	setLoop allows you to OVERRIDE the loop count at the Scenario level and ONLY produce the number of rows defined in this list. The default value is "False."

Step 4 - Import the Excel File

Click on Import within the Excel Column Names Pane.
Click on Choose File to select a local file from your computer.
Browse to the file, select it, and click Open.

Click Save to import the file.

The file columns will appear within the Excel Column Names pane, as shown below:

Step 5 - Add Domain Attributes and Columns

To finish the configuration, you must add the Domain Attributes that will be populated with data read from the Excel file columns for the Test Data Query.

Note: This step must be completed for each queried column of data in the specified sheet of the Excel file (e.g., first name and last name).

Click on Add below the Domain Attributes pane.

Select the Domain and Attribute.
Select the Column being read by the query in the Excel file.
Click Save to finish.

Repeat the above steps for each Domain Attribute/Column.
Click on Column Check to verify the configuration.
Click OK to close the message. If the check fails, check the query configuration.

Step 6 - (Optional) Add to a Test Data Case

Queries can be added to one or more individual test cases. When this is done, the query does not have to be downloaded, just the case and Scenario. For this example, a case will not be used. To learn more, click here.

Step 7 - Generate Test Data

It is time to generate the test data. The query will be performed during test data generation. The tester will perform the following for this example:

Download the Scenario.
Download the Test Data Query.
Copy the Query Command in the Dashboard.
Open a Command Prompt or Terminal window.
Run the command at the command line.

Original Source File (Excel Format)

Generated Output File (XML Format)

The first and last names match the data within the Excel file above:

<rows>
  <row id="1" firstName="Garth" lastName="Strang" dob="12/11/1981" ssn="001-01-0001" phoneNumber="(690) 100-1000"/>
  <row id="2" firstName="Corley" lastName="Sheets" dob="07/24/1986" ssn="001-01-0002" phoneNumber="(690) 100-1001"/>
  <row id="3" firstName="Brad" lastName="Stephan" dob="05/25/1991" ssn="001-01-0003" phoneNumber="(690) 100-1002"/>
  <row id="4" firstName="Lenny" lastName="Murdoch" dob="08/27/1996" ssn="001-01-0004" phoneNumber="(690) 100-1003"/>
  <row id="5" firstName="Wood" lastName="Rabe" dob="06/18/2001" ssn="001-01-0005" phoneNumber="(690) 100-1004"/>
  <row id="6" firstName="Apple" lastName="Banister" dob="06/16/2006" ssn="001-01-0006" phoneNumber="(690) 100-1005"/>
  <row id="7" firstName="Page" lastName="Kautz" dob="11/18/2011" ssn="001-01-0007" phoneNumber="(690) 100-1006"/>
  <row id="8" firstName="Neuman" lastName="List" dob="06/06/2016" ssn="001-01-0008" phoneNumber="(690) 100-1007"/>
  <row id="9" firstName="Greg" lastName="Baker" dob="04/19/2021" ssn="001-01-0009" phoneNumber="(690) 100-1008"/>
  <row id="10" firstName="Archy" lastName="Fink" dob="02/24/2026" ssn="001-01-0010" phoneNumber="(690) 100-1009"/>
  <row id="11" firstName="Garvin" lastName="Kimbrough" dob="09/20/2031" ssn="001-01-0011" phoneNumber="(690) 100-1010"/>
  <row id="12" firstName="Meadows" lastName="Quist" dob="04/05/2036" ssn="001-01-0012" phoneNumber="(690) 100-1011"/>
  <row id="13" firstName="Philip" lastName="Bosworth" dob="03/09/2041" ssn="001-01-0013" phoneNumber="(690) 100-1012"/>
  <row id="14" firstName="George" lastName="Mains" dob="06/23/2046" ssn="001-01-0014" phoneNumber="(690) 100-1013"/>
  <row id="15" firstName="Valente" lastName="Ragsdale" dob="09/20/2051" ssn="001-01-0015" phoneNumber="(690) 100-1014"/>
  <row id="16" firstName="Marcus" lastName="Edgerton" dob="11/19/2056" ssn="001-01-0016" phoneNumber="(690) 100-1015"/>
  <row id="17" firstName="Pinto" lastName="Coles" dob="08/13/2061" ssn="001-01-0017" phoneNumber="(690) 100-1016"/>
  <row id="18" firstName="Pellerin" lastName="Gott" dob="08/04/2066" ssn="001-01-0018" phoneNumber="(690) 100-1017"/>
  <row id="19" firstName="Sean" lastName="Pass" dob="02/04/2071" ssn="001-01-0019" phoneNumber="(690) 100-1018"/>
  <row id="20" firstName="Depew" lastName="Maddux" dob="02/12/2076" ssn="001-01-0020" phoneNumber="(690) 100-1019"/>
  <row id="21" firstName="Jesse" lastName="Levan" dob="04/21/2081" ssn="001-01-0021" phoneNumber="(690) 100-1020"/>
  <row id="22" firstName="Paul" lastName="Grindstaff" dob="09/18/2086" ssn="001-01-0022" phoneNumber="(690) 100-1021"/>
  <row id="23" firstName="Sam" lastName="Millett" dob="11/25/2091" ssn="001-01-0023" phoneNumber="(690) 100-1022"/>
  <row id="24" firstName="Eric" lastName="Ernest" dob="10/25/2096" ssn="001-01-0024" phoneNumber="(690) 100-1023"/>
  <row id="25" firstName="Betty" lastName="Welton" dob="03/16/2101" ssn="001-01-0025" phoneNumber="(690) 100-1024"/>
</rows>

Support

How can we help you today?

How to Add and Setup an Excel List Query Print

Description

In This Article

When Should an Excel List Query Be Used?

Excel List Query Configuration Components