Using Excel Files as Data Storages

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 2016 Redistributable x64. You can download its installation package from the Microsoft website:

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

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 Show StepsHide Steps Run the installer with the /passive command-line argument. Run the Registry Editor. To do this, open the Start menu, type regedit and press Enter. Find the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Common\FilesPaths. Delete or rename the mso.dll registry value. Otherwise, the data provider will get corrupted after the first Excel run.

TestComplete 32-bit

To access the Excel data, you can use:

• DB Table variables. See Variables of the DB Table Type.

• The Data-Driven Loop operation, in keyword tests. See Creating Data-Driven Loops.

• The DDT.ExcelDriver object, in script tests. See Using DDT Drivers.

• 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.