Manage and Assign Data Sources

Creating a data source is as easy as creating a table. How exactly your data source looks like depends on your tests, so making recommendations on how to design your data source is beyond the scope of this user guide.

To follow along with the sample solution, you need a data source:

• Download the CSV table and unzip it to any directory on your computer, or
• Copy and paste the following text into a text file and save it as .csv.

FirstName,LastName,Age,Gender,Department,Num
John,Public,48,Male,Project Management,1
Mary,Smith,36,Female,Sales,2
Henry,Rogers,29,Male,Support,3
Thomas,Bach,42,Male,Development,4
Cindy,Martens,19,Female,Office,5
Hanna,Perry,48,Female,Management,6
Will,Hallmark,32,Male,Support,7
Nicole,Wallace,38,Female,Testing,8

Data sources, and the test data they contain, are managed per test suite. This means:

• Once added to a test suite, the data source can be assigned to the test containers of this test suite.
• You can’t access a data source in test suite A from test suite B. You first need to add the data source to test suite B.
• A data source can be used in multiple test suites at the same time if you add it to each test suite.

Open the Manage Data Source dialog

You can access data source management in these ways:

1. In the test suite view, click MANAGE DATA SOURCES…

2. Select a test container, then:

   • Test suite toolbar: Data source… > Manage data sources…

   • Context menu: Data source… > Manage data sources…

The data source management dialog

You can access data source management in these ways:

1. Add a new data source.

2. Delete an existing data source.

   Attention: Deleting a simple data table means deleting the data physically. Data will be lost! This is because simple data tables are stored directly in the corresponding test suite file.
   Deleting an Excel, CSV, or SQL data source in this dialog means deleting only the connector to this data source and the settings in the Configuration section, not the data source file itself.

3. Clone a data source. For simple data sources, this means cloning the contents of the data source and the settings in the Configuration section. For all other data sources, this means cloning the connector and the settings in the Configuration section. This option is useful for specifying different sheets of the same Excel data source, for example.
4. List of added data sources, each showing the connector type (Simple, CSV, Excel, SQL) and the Use count (how many times the data source has been assigned in the test suite).
5. Configuration section. Here you can manage certain settings depending on the data source type. The available settings are explained below for each data source type.

Once you add a data source, you need to assign it to a test container, so the modules/variables in it can access the data.

1. Select the test container you want to assign a data source to.
2. Open the context menu and click Data source…
3. Select the data source from the drop-down menu and click OK.
4. The data source appears next to the test container in the test suite view, with the number of rows in it indicated.
   

Data source assignment rules

The assignment of data sources to test containers is subject to the following three rules:

Example 1

Let’s see how these rules work by way of an example. Suppose you have two data sources available in a test suite: a CSV data source, myCSVData, and an Excel data source, myExcelData.

1. The CSV data source is assigned to the smart folder A-1.
2. The Excel data source is assigned to the smart folder A-2.
3. Because of rule 2, test case A and its module cannot access the data sources of the descendant test containers.
4. Because of rule 2, modules of the smart folder A-1 have access to the CSV data source.
5. Because of rules 2 and 3, all modules of the smart folder A-2 have access to both the CSV and Excel data sources.
6. Because of rules 2 and 3, all modules in descendants of the smart folder A-2 have access to both the CSV and Excel data sources.

Example 2

1. All data sources have been assigned below test case B and smart folder 1. Therefore, these test containers and their modules cannot access the data sources.
2. The Excel data source is assigned to the smart folder 1-1. Therefore, the descendants (modules, test containers) of this tree branch can access the Excel data source.
3. The CSV data source is assigned to the smart folder 1-2. Therefore, its modules can access this data source.
4. The Excel data source is assigned to smart folder 1-2-1. Therefore, its descendants can access both the CSV and the Excel data sources.
5. The Excel data source is also assigned to smart folder 1-3-1. Therefore, its descendants can access this data source.

Ranorex Studio supports four types of data sources: Simple, CSV, Excel, and SQL. Except for Simple tables, sources are added via connectors (Ranorex links to the file/database; it does not import all contents into the test suite).

Simple data table

Simple data tables are useful when you want to quickly set up small data-driven tests, e.g. for trial and error. We do not recommend them for anything more complex than a couple of data rows.

Simple data tables are stored directly in the test suite file (.rxtst), with all of their contents. This is why you have to create and maintain them directly in the Data source… dialog. It’s also why they are deleted entirely when you delete them in the data source management dialog, unlike other data sources.

To add a new simple data table:

1. Click New > Simple data table

   

2. Name the data source

3. Click OK

   

   1. The Mask option is explained separately further below. The Label option is explained separately further below.
4. In the Data source… dialog of a test container, select the simple data source and create the content in the table editor
   
   You can also specify a date range. This option is explained separately further below.
5. Click OK when you are done

Hint: You can paste tables from Excel files into the table editor.

Excel data connector

Excel data sources are added via a connector.

Hint: Instead of the default Excel file format xlsx, you can also use the native binary file format xlsb. This file format is supported in Microsoft Office 2007 and later, and is much faster than the non-binary version.

To add an Excel connector:

1. Click New > Excel connector…
2. Configure the connector and click OK.
   

1. Name the Excel connector and specify the location of the Excel file.

2. This copies the Excel file to your project folder. You must check this if you use version control.

   Hint: It is recommended to check this option in any case. This way, you don't have to worry about where your test data file is located. Also, when you deploy your test, for example, via Ranorex Remote, the file is transferred to the Ranorex Agent in the runtime environment automatically.

3. Worksheet selection: If your Excel file contains more than one worksheet, you can specify the sheet to be used here. You can also limit the test data to a specific range.
   Uncheck the auto-load option to decrease the start-up loading time for the test suite. However, this also means the number of rows won’t be displayed next to test containers.

   Hint: Use the Clone option to quickly create multiple Excel connectors that link to the same file, but different worksheets or ranges.

4. The Mask and the Label options are explained separately further below.

CSV data connector

CSV data sources are added via a connector. Once added, CSV data sources can be edited in Ranorex Studio in the Data source… dialog. When you save these changes by clicking OK or Apply, the actual CSV file is also changed.

To add a CSV connector:

1. Click New > CSV connector…
2. Configure the connector and click OK.

1. Name the CSV connector and specify the location of the CSV file.
2. This copies the Excel file to your project folder. You must check this if you use version control.
3. Data configuration. Specify whether the CSV file contains a header row or not.
4. The Mask and the Label options are explained separately further below.

Uncheck the auto-load option to decrease the start-up loading time for the test suite. However, this also means the number of rows won’t be displayed next to test containers.

SQL data connector

With the SQL data connector, you can access an SQL database and pull data from it using an SQL query. To illustrate this process, here is a simple example where you access a Microsoft Access database.

1. Click New > SQL connector…
2. Name the connector.
3. Click Create to specify the SQL connection string.
4. Specify the location of the database file.
5. Specify the optional connection settings (see below) and click OK.
   Optional connection settings:
   1. Change the database connection type to suit your database type. In this example, Microsoft Access Database File is correct, since you are using a Microsoft Access database.
   2. If the database requires a login, specify it here.
   3. Click to test the connection to the database (recommended).
      
6. Under Query, click Create to specify the database query.
   
7. Define the desired SQL query in your database (Microsoft Access, in our example) to provide the data to Ranorex Studio and click OK.
   
    
8. Set the auto-load behavior (disable for faster load time, but missing row indicators in the test suite view) and masking (explained separately below) and click OK.

You can mask data for all data source types. This way, you can hide sensitive data in the report while still allowing Ranorex Studio access to it for testing purposes.

1. Data from unmasked columns is displayed normally in the report.
2. Data from masked columns is hidden in the report.

Mask variables and parameters

You can mask the data that is bound to variables and parameters to avoid sensitive data being in a report.  Masking set at a test case level overrides any masking set at the test suite level.

In the properties of a test case, you can find the Data Binding editor. Enable the checkbox next to a variable name for masking the variable contents in the report.

In the Report Properties, select the Global parameter tab, and enable the checkbox next to the variable names for masking the variable contents in the report.

By default, data iterations carried out as part of data-driven tests use the iteration count as a label in the report, i.e. they are numbered. However, you can also assign column names from your data source as labels. Ranorex Studio then uses the values in that column as labels in the report.

1. Default iteration count with numbers from 1 to 18 in the report.
2. Data-source column LastName selected as the label instead.
3. The iterations now use the respective values of the column LastName as label, making them easier to identify.

You can limit the data range for all data sources. This allows you to make only certain rows of a data source available to a test container.

1. Enter a range of rows in the Data source… dialog.
2. Click Preview effective data set… to see the result.
   

You can automatically create a CSV data source from unbound variables defined in modules.

The test suite shown below contains the smart folder InsertData, which in turn contains four recording modules with 5 defined, but unbound variables.

1. Smart folder InsertData with four recording modules.
2. The 5 defined, but unbound module variables in the recording modules.

To auto-create a CSV data source from these variables:

1. Right-click the smart folder.
2. Click either Data source… or Data binding….
   
3. Click Auto-generate data source.

Result

Ranorex Studio automatically creates a CSV data source with the variable names as the column names.

1. Auto-generated CSV data source with the default name derived from the test container.
2. Table with the column names derived from the variable names.

Ranorex Studio also automatically binds the variables to the matching columns in the auto-created data source, visible under Data binding.

You can manage the data source as usual in the Manage data sources… dialog. By default, the CSV file is stored in the project folder of your solution.

1. Auto-generated CSV data source in the Manage data sources… dialog.
2. The CSV file is in the project folder of your solution.

To prepare the sample solution for the next step (data binding):

1. Ensure your CSV file is ready.

2. In the test suite view, click MANAGE DATA SOURCES…

3. Click New > CSV connector…

4. Name it myCSVData, select the CSV file, and enable the recommended options (commonly):

   • Copy file to project folder

   • Auto-load

   • Header row
     Then click OK.

5. In the test suite view, right-click the test case Data-driven_DB_Test and click Data source…

6. Select myCSVData and click OK.

You have now assigned the data source. Next, you need to connect columns to variables using data binding.