Using Excel Files as Data Storages

Applies to TestComplete 15.45, last modified on December 01, 2022

One of the popular data storages for data-driven tests is Excel sheets. You can use DB Table variables to access Excel data from tests. In keyword tests, you can use the Data-Driven Loop operation to iterate through Excel file rows. See Creating Data-Driven Loops and Data-Driven Testing With Keyword Tests - Tutorial.

You can also use the ExcelDriver object to get data from Excel files and iterate through Excel file rows in script tests. See DDT.ExcelDriver and Using DDT Drivers.

Requirements

To retrieve data from Excel sheets, TestComplete uses the data drivers (or data providers) available in your system. Which exactly driver it uses depends on the TestComplete version and your Excel file:

TestComplete 64-bit

TestComplete x64 needs the 64-bit version of Microsoft Office 12.0 Access Database Engine OLE DB Provider (data provider or driver). If you have the 64-bit version of Microsoft Office 2010 or later installed, then, most likely, you have this provider on your computer. If not, you will have to install it. The provider is part of Microsoft Access Database Engine 2010 Redistributable x64. You can download its installation package from the Microsoft website:

https://www.microsoft.com/en-us/download/details.aspx?id=13255

Make sure to install the 64-bit version of this data provider. Else, you will get the “The needed data source driver is not installed...” error.

If you have 32-bit Microsoft Office 2010, the installation of the 64-bit provider will fail. In this case, perform the following steps to install it:

Show Steps

TestComplete 32-bit
  • For Excel files created in Excel 2007 or later (the file extension is .xlsx), TestComplete uses the 32-bit version of Microsoft Office 12.0 Access Database Engine OLE DB Provider. If you have the 32-bit version of Microsoft Office 2010 or later installed, then, most likely, you have this provider on your computer. If not, you will have to install it. The provider is part of Microsoft Access Database Engine 2010 Redistributable, which you can download from the Microsoft website:

    https://www.microsoft.com/en-us/download/details.aspx?id=13255

  • For Excel workbooks created in Excel versions earlier than 2007, TestComplete uses the Microsoft Jet Engine driver that is installed in the Windows operating system by default.

TestComplete x86 can also use the Microsoft Excel ODBC driver to work with Excel sheets. However, this approach has a number of limitations:

  • The ODBC driver does not support Excel 2007 - 2016.

Access data

To access the Excel data, you can use:

Specifics

  • TestComplete assumes that each row in your Excel sheet contains data for one test run. Using multiple rows for one run is not supported.

  • Default settings for the Excel ODBC driver are specified by the following registry key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

    The settings for the Microsoft Access Database Engine driver are specified by the following registry key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

    The structure of subkeys is the same for both keys.

  • The names and number of columns as well as the number of rows are determined by the populated cells in the underlying Excel sheet.

    The number of rows is determined by the last row that holds data in one of the “column” cells.

  • The FirstRowHasNames registry subkey of the driver setting key (see above) specifies the column names to be used:

    • If the key value is False (00), the data provider will name the column automatically (F1 will represent the first field, F2 will represent the second field, and so forth). The first row is considered as a data row.

    • If the key value is True (01), TestComplete reads textual values stored in the cells of the first row (A1, B1, C1, and so forth) and stores these values as column names. TestComplete exits the loop when it finds an empty cell. If the first row does not have headers (or contains numeric values), the data provider automatically names the fields for you and the data of the first row is omitted.

  • For the data provider to treat spreadsheet data correctly, all data in each column (except the column name) needs to be the same type, for example, text only or numbers only. This is because the provider applies only one type to the whole column, so, if it contains data of several types, some values may be treated incorrectly.

  • The Excel ODBC driver can truncate cell data to 255 symbols. To determine the data type of a column, Excel scans the column cells of the first 8 rows. If each of these cells contains text shorter than 256 characters, all the subsequent cell values will be truncated to 255 symbols. For more information, see docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/initializing-the-microsoft-excel-driver.

  • The Excel ODBC driver does not support more than 64 connections to a process. After your test that uses Excel sheets is complete, we recommend that you call the DDT.CloseDriver method to close the connection.

  • Due to specifics of the Microsoft Access Database Engine and Excel ODBC drivers the column names are truncated to 64 symbols (for more information on this limitation, see the Column Name Limitations article in the MSDN Library). To work around this limitation, you can read column names in your Excel documents via COM. For more information, see Working With Excel Files via COM.

See Also

Data-Driven Testing
Data-Driven Testing - Basic Concepts
Preparing Data for Data-Driven Testing
Organizing Data Storages
Using Keyword Tests for Data-Driven Testing
Creating Data-Driven Loops
Data-Driven Testing With Keyword Tests - Tutorial
Using Scripts for Data-Driven Testing
Using DDT Drivers
ExcelDriver Method

Highlight search results