You might use spreadsheets to define test for the following reasons:
Data Validation Option users can work offline when creating tests.
Analysts who are familiar with the data and testing requirements but are not familiar with Data Validation Option can define the tests. A Data Validation Option user can then import the test definitions and create the tests in Data Validation Option.
Data extraction, transformation, and loading (ETL) processes and testing specification documents are often defined as a source-to-target spreadsheet. Some of the definitions in those spreadsheets can often be repurposed for test creation.
Large numbers of tests, particularly those that follow certain patterns, can be defined quickly in a spreadsheet with copy and paste and other basic spreadsheet features. Importing multiple tests into Data Validation Option can save a significant amount of time and effort over creating multiple tests in the Data Validation Client.
For example, a flat file contains 200 columns. Each column is defined as a string in the source definition. During the ETL, about 90 of the fields are converted to decimal and written to the final target table.
Testing this scenario would require creating 200 value tests, one for each column that is read and written to the target table. The value tests for the columns that are converted to decimal must contain the expression TO_DECIMAL (<fieldname>).
On the Data Validation Client, it would be tedious to manually create the 90 tests that include an expression. But by using basic cut and paste or search and replace capabilities within a spreadsheet, you can define these 90 tests in about one minute or less. And if, instead of 90 tests, there were 200 or even 900 similar tests, the time to create them would be minutes at most. This is a big productivity increase over using the Data Validation Client.
Importing from a spreadsheet provides an alternative to the Data Validation Client that is very effective when a large number of similar tests must be generated.
The following examples show the how you can import tests from spreadsheets in different scenarios:
Import and export of table pair tests
Import of table pair tests for compare tables
Each of the scenarios uses a table pair as an example, but analogous functionality and processes can be used for single tables as well.