# Randomizing static test data in automated tests

A significant percentage of static test data is stored in tabular comma delimited or tab-delimited formats and saved in Excel spreadsheets. Reading in comma or tab-delimited static test data into an automated test is pretty straight forward and there are numerous examples in many programming languages illustrating how to read in these types of test data repositories. Reading in rows of data is the foundation of data-driven automation and definitely has its place in any automation project.

I am a big proponent of stochastic (random) test data generation that is customized to the context, but I also know that sometimes static test data is useful for establishing baselines and more exact emulation of ‘real-world’ customer-like inputs. But, if the automated test is simply passing the same variable arguments to the same input parameters in the same order over and over again the value of subsequent iterations of that automated test using that static data set diminishes rather quickly. So how can we more effectively utilize static test data in our automated tests?

One possible solution is to randomly select an argument from a collection of static variables that is passed to the specific input parameter. The advantage of this approach is that it effectively increases the test data permutations in each iteration of the test case. For example, let’s consider 2 input parameters; one for a given name and one for a surname. In a traditional data-driven approach in which the static test data is read in by rows our test data file might be similar to:

Bob,Smith
John,Johnson
Roger,Williams
Steve,Abbot

This static data file would give us 4 sets of test data, but each time the test data is read into the test case the given and surnames are always the same.

However, if we read in the given names and surnames into 2 collections, and then randomly select a given name and surname from the appropriate collection to pass to the respective parameter we effectively have 16 possible combinations of static test data to work with. An advantage of this approach is that our ‘collections’ of given names and surnames can contain differing numbers of elements (in which case the number of possible combinations of test data is the Cartesian product of the number of elements in each collection).

Of course there are many ways to accomplish this. For example, one approach is to continue to use a comma or tab-delimited file format and list given names in one row and surnames in a second row. Another approach is to list the given names and surnames in columns in a spreadsheet and read in each column into a collection of some sort. The latter is the approach I used in developing my PseudoName test data generator tool. I chose this approach for 2 reasons; first an Excel spreadsheet is a simple yet powerful file format for storing static test data, and secondly because lists of test data are sometimes better represented in columns rather than rows.

The following code shows one way to read in test data by columns from an Excel spreadsheet.

Code Snippet

4.
5. namespace TestingMentor.TestTool
6. {
7.   using System;
8.   using System.Collections;
9.   using System.Globalization;
10.   using System.Runtime.InteropServices;
12.   using Excel = Microsoft.Office.Interop.Excel;
13.
14.   /// <summary>
15.   /// This class contains methods for reading test data from Excel spreadsheets
16.   /// </summary>
18.   {
19.     /// <summary>
20.     /// This method reads all the data elements in the specified number of
21.     /// columns in the specified Excel spreadsheet containing the test data
22.     /// and copies the data into a multi-dimensional array
23.     /// </summary>
24.     /// <param name="dataFileName">The filename containing the test data</param>
25.     /// <param name="columnCount">The number of columns in the Excel
27.     /// <returns>A multi-dimensional array containing the data eleements for
28.     /// each column </returns>
29.     public static string[][] ExcelColumnReader(string dataFileName, uint columnCount)
30.     {
31.       CultureInfo originalCulture = null;
32.       Excel.Application excelApp = null;
33.       Excel.Workbook excelWorkbook = null;
34.       Excel.Worksheet excelActiveWorksheet = null;
35.       string[][] testData = new string[columnCount][];
36.
39.
40.       excelApp = new Excel.Application();
41.       excelWorkbook = excelApp.Workbooks.Open(
42.         dataFileName,
43.         0,
44.         false,
45.         5,
46.         String.Empty,
47.         String.Empty,
48.         false,
49.         Type.Missing,
50.         String.Empty,
51.         true,
52.         false,
53.         0,
54.         true,
55.         false,
56.         false);
57.       excelActiveWorksheet = (Excel.Worksheet)excelWorkbook.ActiveSheet;
58.
59.       for (int i = 0; i < columnCount; i++)
60.       {
61.         // Start at column 1
62.         object columnIndex = i + 1;
63.
64.         // Row 1 is the column title; test data starts on Row 2
65.         object rowIndex = 2;
66.         ArrayList tempCollection = new ArrayList();
67.         while (
68.           ((Excel.Range)
69.           excelActiveWorksheet.Cells[rowIndex, columnIndex]).Value2 != null)
70.         {
72.             ((Excel.Range)
73.             excelActiveWorksheet.Cells[rowIndex, columnIndex]).Value2);
74.           rowIndex = (int)rowIndex + 1;
75.         }
76.
77.         testData[i] = new string[tempCollection.Count];
78.         testData[i] = (string[])tempCollection.ToArray(typeof(string));
79.       }
80.
81.       // Clean up
82.       excelWorkbook.Close(false, Type.Missing, Type.Missing);
83.       excelWorkbook = null;
84.       excelApp.Quit();
85.       excelApp = null;
86.
87.       // Garbage collection is not pretty, but necessary to release Excel proc
88.       System.GC.Collect();
89.       System.GC.WaitForPendingFinalizers();
90.
91.       if (originalCulture != null)
92.       {