Data Generation Add-in for Excel

• Introduction

• Installation Requirements

• Using the Data Generation Functions

• Setting the Locale

• Combining Functions in Complex Formula

• Referencing Other Cells

• Referencing Databases Using Excel Connectors

• The Spill Handle

Introduction

The Data Generation Add-In provides a comprehensive range of data generation functions that can be used in exactly the same way as standard Excel functions. It allows users to generate realistic data quickly, and combines with broader Excel functionality for a powerful but simple data generation tool.

Basic proficiency in Microsoft Excel is required to use the Data Generation Add-In. This article assumes such knowledge, and provides instructions on how to use the additional data generation functionality provided by the add-in. It also highlights standard Excel functionality, which is often useful for test data generation in conjunction with the Data Generation Add-In.

Watch the tutorial video for a quick overview of the Add-In’s basic functionality, including an example of generating synthetic personal information.

Installation Requirements

The add-in requires a Windows machine, and runs on both the 32 bit and 64 bit version of Microsoft Office. You will need your own license for Microsoft Office, and Excel installed.
Installing the Add-In is quick and easy if you already have VIP installed. Simply double-click the .XLL file for either the 32 bit or 64 bit version of Microsoft Office. The .XLL files can be found in the following locations:

• 32 bit: C:\Program Files\Curiosity\Visual Integration Processor\VIPXLDataGenAddIn-packed.xll

• 64 bit: C:\Program Files\Curiosity\Visual Integration Processor\VIPXLDataGenAddIn64-packed.xll

Please note: Functions for invoking VIP flows from Excel only work when using a 64 bit version of Microsoft Office. Users wishing to perform test data management actions using VIP must be running a 64 bit version of Microsoft Excel.
Microsoft Excel will open once the plug-in has been processed. A pop-up security notice will open; click "Enable this add-in for this session only" in the bottom left of the notice:

You will then need to enable the plug-in for subsequent sessions.
Navigate to File–>Options–>Add-Ins to open the following pane:

First, select the "VIP Data Generation Add-In" from the list displayed.
Next, navigate to "Excel Add-Ins" on the "Manage" drop-down menu.
Click "Go" to open the following pane:

Select the "VIP Data Generation Add-In" and click Browse. Reselect the relevant .XLL file, found in the following locations:

• For 32 bit versions of Microsoft Office: C:\Program Files\Curiosity\Visual Integration Processor\VIPXLDataGenAddIn-packed.xll

• For 64 bit versions of Microsoft Office: C:\Program Files\Curiosity\Visual Integration Processor\VIPXLDataGenAddIn64-packed.xll

Click "okay". A pop-up might ask if you would like to replace the file that exists in that location. Click "Yes":

The Add-In is now set up.

Using the Data Generation Functions

The data generation functions are accessed in the same way as standard Excel functions: by typing "=" in a cell, followed by the function name.

The name of every data generation functions provided by the Add-In begins with "DataGen". The full list of data generation functions can be browsed by typing "=DataGen" into a cell, and scrolling through the list.
A description is provided for each function:

The data generation formula are written using symbols standard to Excel formula, and follow the standard structure of Excel formulae.

Setting the Locale

You can specify a Locale for each sheet before generating data, using the Locale function to set the language and/or geography of the generated data. This is relevant to functions that generate country specific information, such as phone numbers, addresses, or zip codes. It also determines the lists used to create fictitious personal information such as names.

To set the local, use the function =DataGen.SetLocale() , and type the locale code in double quotes.

For instance, the function =DataGen.SetLocale("SV") sets the locale to Sweden.

The cell in which you have set the locale will confirm the locale set.

Note: the locale codes are case-sensitive.

The following locales are provided in the Add-In:

Combining Functions in Complex Formula

The data generation functions can be combined with one another, as well as with standard Excel functions. This flexibility creates a comprehensive set of functions for generating synthetic test data.

Combining functions sequentially with "&"

Functions can be combined using the standard "&" symbol. This creates data sequentially within a cell, with each function separated by an "&" executed independently. The generated data is then combined in the order written in the formula.
For instance, the formula

=DataGen.Name.FirstName() & " " & DataGen.Name.LastName()

Will return a first name followed by a space, and then a last name, separated by a space:

Ramon Karlsson

The Parse Function

The Data Generation Add-In also provides a parse function for combining functions in a complex formula.

The parse function returns a string value after resolving a set of functions specified in order. It provides an alternative approach to combining functions using only squiggly brackets, which some users might find quicker and simpler.

Returns a string value after resolving a set of functions specified in the format =DataGen.Random.Parse("function1 function2 functionN").

For example, the formula 

=DataGen.Random.Parse("address.streetname address.city address.zipcode")

Will return a street name, followed by a city name and a zipcode:

Tyrique Oval South Jerod 98725

O'Conner Junctions New Nicolaberg 71746

Rosenbaum Gardens East Vernice 68307

Referencing Other Cells

The data generation functions can reference other cells and sheets, in exactly the same way as standard Excel functions.

This is useful for creating variable data and interdependencies within and across sheets, and also allows data to be edited quickly and consistently across sheets.

To reference another cell or range of cells, enter the name of the cell or cell range at the relevant point in the formula.

The cell name or range can be typed manually, for example "A3" or "A3:15" for rows 3-15 in the A column. Alternatively, the name or range can be selected at the appropriate place in the formula, by clicking the cell or highlighting the cell range to be incorporated in the formula.

Referencing Databases Using Excel Connectors

The data generation functions can be used in conjunction with the full range Excel connectors into files, databases, Azure, and other data sources.

Using Excel's dependency mapping, the Data Generation Add-In can then be used to edit data from existing systems. Data in test databases, for example, can be enhanced synthetically for greater test coverage, before being invoked existing test automation frameworks or used in manual testing. The data generation Add-In thereby provides a simple and lightweight tool to improve the rigour of testing.

First, the data is imported to Excel. Navigate to Data/Get Data and select the relevant location:

In the navigator pane, select the relevant table and click "Load".

This will import the table into Excel, where it can be edited:

The data generation functions can then be used in a separate sheet to edit data in the connected database or file, referencing the cells as set out here. Existing data can be strung together using the Parse function, and fresh synthetic data generated, supplementing existing data sets according to test and development needs.

The newly created data can be maintained as the files or databases changes, using Excel's refresh controls. Right-click the table name in the "Queries & Connections" panel, and click "properties".

Under "Refresh Control", select the nature and regularity of the refresh. As the data source, the imported data will automatically refresh:

Data can also be refreshed manually, by navigating to the data tab and clicking "Refresh All" in the "Queries & Connections" tab:

Data generation functions that reference the imported data will auto-update as the data is refreshed. This automatically maintains test data, keeping it up-to-date as systems data changes. Interdependencies in the data and synthetic data are also maintained by Excel, providing a quick and simple way to maintain the validity of test data.

The Spill Handle

Simple and complex data generation functions can be repeated rapidly using the spill handle. This is useful where large quantities of data are needed, especially when the data contains complex relationships and dependencies that need to be retained consistently.

Note: Excel does not execute functions asynchronously. Functions are typically therefore executed at varying speeds, so that the Spill Handle will not necessarily execute functions in a linear manner. When the sequence of data needs to be retained, the Spill Handle should not therefore be used, and the functions should be copy and pasted row by row. For instance, the Spill Handle will not provide a set of linear, sequential integers when the sequential integer function is used; if sequential integers are required, the function should be copied and pasted.

To use the Spill Handle, highlight the cell or cells that contain the data and formulae that you would like to repeat. Next, click the square in the bottom right-hand corner of the selection, and drag vertically or horizontally to cover the cells in which you would like to repeat the formulae and values.

The interdependencies with other cells are retained when the Spill Handle is used. For instance, if a formula in cell "A1" references cell "B1", and the spill is dragged to row 10, rows A1:10 will use the data in rows B1:10.

TEST DATA AUTOMATION DATA GENERATION