Description

An Excel List G-Query can be used to query data from a specified sheet within an Excel file. The data queried from an Excel file is populated for the defined Domain Attributes during test data generation and blended with synthetically generated data.


 


When should you use an Excel List Test Data Query (G-Query)? 

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


How do you configure Excel List Test Data Query (G-Query)?

  1. Create an Excel List Test Data Query
  2. Configure the Test Data Query Parameters
  3. Import the Excel file into the Test Data Query
  4. Add Domains and Attributes for the real data to populate


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 Excel file after imported.
  • Domain Attributes - Defines the Attributes that will be populated by the real data obtained from the imported file.


Sample Address Excel File Test Data Query

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


In this Article


Step 1: Access the G-Queries Management Platform

  • Expand the Self Serve Menu Options drop-down menu for the appropriate Project Version and then select G-Queries.


Step 2: Add an Excel List G-Query

  • Click on the Add G-Query button.



  • Enter a Name and Description for the G-Query. 
  • Select the Excel List query type. 
  • Click the Save button. 



Step 3: Configure Test Data Query Parameters

Test Data Query Parameters define any resources and variables to be used during the query. Available parameters within the Parameters Pane will vary based on the type of query. 


Excel List Parameters

Items with an asterisk (*) are required to perform this type of Test Data Query:

  • 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. The default value is 1000.
  • setLoop* - setLoop gives you the ability 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." Note: This does not apply to GenRocket Express as it does not use Scenarios.


Below are the Parameters for the example shown at the beginning of this article. For this example, values have been entered for the fileName and sheetName Parameters. Remember to click the Save button after making changes to the Query Parameters. 



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 on 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 will need to add the Domain Attributes that will be populated with data read from the Excel file columns for the Test Data Query. Complete these steps for each queried column of data in the Excel file.

  • Click on Add below the Domain Attributes Pane.


  • Select the Domain and Attribute from the drop-down menus.

  • Enter the name of the Column being read by the query in the Excel file.

  • Click Save to finish.



  • The new Domain Attribute will appear in the table as shown below:


    Note: Use the options within the Action Column to edit or delete a Domain Attribute from the Test Data Query.

  • Click on the Column Check button to verify.



  • A Notification dialog window will appear to show if the setup is valid. Click OK to close the dialog window.



  • For this example, the city, state, and zipCode columns within the Excel file will be queried and blended with synthetic data.


Sample Output

A delimited file has been created using the Test Data Query from this article: 


The city, state, and zip code match the data within the Excel file: