Share via

Randomizing static test data in automated tests

  • Article

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

  1. // <copyright file="datareader.cs" company="TestingMentor">
  2. // Copyright © 2009 by Bj Rollison. All rights reserved.
  3. // </copyright>
  4.  
  5. namespace TestingMentor.TestTool
  6. {
  7.   using System;
  8.   using System.Collections;
  9.   using System.Globalization;
  10.   using System.Runtime.InteropServices;
  11.   using System.Threading;
  12.   using Excel = Microsoft.Office.Interop.Excel;
  13.  
  14.   /// <summary>
  15.   /// This class contains methods for reading test data from Excel spreadsheets
  16.   /// </summary>
  17.   public class TestDataReader
  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
  26.     /// spreadsheet to read</param>
  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.  
  37.       originalCulture = Thread.CurrentThread.CurrentCulture;
  38.       Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
  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.         {
  71.           tempCollection.Add(
  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.       {
  93.         Thread.CurrentThread.CurrentCulture = originalCulture;
  94.       }
  95.  
  96.       return testData;
  97.     }
  98.   }
  99. }

I must tell you that performance can be an issue especially if the columns contain a lot of data. For example, to read in approximately 700 elements of test data in 3 separate columns took slightly less than 1 second, and reading in 1800 elements in 3 columns required just over 4 seconds. Unfortunately, I didn’t compare total byte counts, but it is pretty obvious the greater the number of test data elements being read the longer the read operation will take and you certainly will have to take the read time into consideration in your automated test case.

Reading static test data line by line from a data file while looping through a data-driven automated test case is a useful test design approach in some situations, this is another useful approach that will allow the test designer to randomize the combinations of static test data values applied to multiple input parameters in multiple iterations of an automated test case.