Microsoft Excel

Automation Guide

Application testing

Testing with databases

Microsoft Excel

Information specific to testing with Microsoft Excel

Introduction

Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows.

Configuration

Supported Microsoft Excel versions: 2007, 2010 and 2013.
Microsoft Excel Supported Extensions: xls, xlsx, xlsm, xlsb.
Connecting to and querying a database from a test requires that the correct ODBC driver be used. The correct driver must be specific to both the DBMS and the architecture (64-bit) of the TestArchitect Controller application that uses it. The following utilities may be used to determine whether the correct driver is already installed:
- Windows: Open the ODBC Data Source Administrator dialog box. A list of installed ODBC drivers is available under the Drivers tab. (Learn more.)
Should you need to obtain the ODBC driver installer for your particular data source, following the appropriate link below:
- Excel: The ODBC driver for Microsoft Excel is automatically installed with Microsoft Office. If Microsoft Office is not currently installed on your test machine, you can download and install the correct driver from the appropriate link below:
  - ODBC driver for Excel 2010 (64-bit): http://www.microsoft.com/en-us/download/details.aspx?id=13255

Actions

TestArchitect provides the following built-in actions for working with SQL-compliant databases. They are listed in the typical order of their workflow:

create connection string: Generate a connection string for a specified database on a given host.
connect database: Open a database connection using a connection string.
use database: Specify a database against which the ensuing SQL action lines are to be applied.
execute sql: Execute an SQL command against the currently in-use database; optionally, store returned data into one or more data sets.
disconnect database: Close the database connection.

Connecting to Excel from your test

Use the action create connection string to establish an ODBC connection to the Excel file. Note that the action’s driver argument should have one of the following values:


For Excel version 2007 and later	Microsoft Excel Driver (.xls, .xlsx, .xlsm, .xlsb)
For Excel 2.0 – 2003	Microsoft Excel Driver (*.xls)

Use the others argument to specify the path and name of the Excel file, using the key DBQ. For example, DBQ=C:\Customers.xlsx.

Querying Excel from your test

For the purpose of running an SQL query, Excel offers a few constructs that can act as tables:

named range
Excel allows you to create a defined name for a given range of cells. To use this within an SQL query, simply treat it as a table name. For example, with a named range of customers, you could issue a query with:
```
                 command                       result
execute sql      select * from customers       customers_ds
```
Notes:
The query treats the last row of the named range as the last row of the table. Note that any empty rows at the end of the range are treated as table rows with NULL values in each cell.
worksheet
An Excel spreadsheet file can consist of any number of worksheets, each of which must have a unique name. In your SQL query, you can use a worksheet name in place of a table by appending a $ to it and enclosing it in square brackets. For example, with a worksheet named customer_sheet, you could issue a query in this manner:
```
                 command                             result
execute sql      select * from [customer_sheet$]     customers_ds
```
Notes:
For a query on a full worksheet, the last non-empty row is treated as the end of the table. Note that any empty rows preceding it are treated as table rows with NULL values in each cell.
a specified range within a worksheet
You can also restrict the “table” to a range within a worksheet by following it with the columns and rows that define its edges. For example:
```
                 command                                    result
execute sql      select * from [customer_sheet$C3:H320]     customers_ds
```
Notes:
With a table defined by a specified range within a worksheet, the last row of the range, or the last non-empty row – whichever comes first – is treated as the end of the table. Note that any empty rows preceding the last non-empty row are treated as table rows with NULL values in each cell.

It’s important to note that, for whichever type of table construct you use, TestArchitect treats the first row of the given range as one of column headers. The values in this row are used as the field names of the dynamic data set (customer_ds in all the above examples) that is generated.