Connecting T-Loader to a Microsoft Excel Data Source

The use of T-Loader to pull data from Microsoft Excel for population to a database is made complex by the fact Microsoft does not ship any 64-bit drivers natively on 64-bit Windows platforms excepting those for SQL Server. As well, the base installer for T-Loader will adhere to the Operating System’s bit-ness. For example, when run on 64-bit Windows, it will run as a 64-bit application. Consequently, connecting to Excel (and other Office file sources) requires additional configuration effort:

Prerequisites

To connect to any Office application’s file for data reading, you must first install the “Microsoft Access Database Engine 2010” package that fits your system.

• If you run 32-bit Office you must install the 32-bit version of this provider set.
• If you run 64-bit Office you must install the 64-bit version of this provider set.

Note: You cannot mix and match these providers. Only one of them can be installed, and the bit-ness of your Office install dictates which it is.

Once that package is installed, how you proceed depends on your Operating System’s bit-ness.

What T-Loader Setup do I Install To Use the Product Against an Excel Data Source?

The following table defines what you must do to make T-Loader see the Excel data sources.

Note: The 32-bit Edition is also useful if, on a 64-bit Operating System the Source or Target databases do not have native 64-bit drivers. For example, a MySQL install that has a 32-bit provider only will be invisible to T-Loader. Also, beware that the bit-ness of the source and destination must be consistent. 32-bit Software on 64-bit Windows runs in a sandboxed emulator.

Connecting to an Excel File

Once your system is configured, you use T-Loader as normally used, with two distinct differences:

The Source Connection String uses the format:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=[ Excel File];Extended Properties="[Version];HDR=[Header]"

o    Valid settings for the bracketed elements are:

• [Excel File] :: the complete qualified path to your actual Excel file. E.g., C:\My Documents\My Data\ExcelWorkbook.xlsx” (It has to be an actual Workbook, or other valid Excel format.)
• [Version] :: The valid entries are:

• [Header] :: the valid entries are:
  • YES, if the first row contains column names (it must for the T-Loader to identify field names of sensible reference)
  • NO, if there is no field name header row

An example for an Excel 2010 Workbook located at C:\ TEMP \Data.xlsx is:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TEMP\Data.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES"

The Source Definition String uses the format:

SELECT [FIELD_1], [FIELD_2], [Etc.] FROM [TABLE$];

The FIELD names are the headers of the sheet (or will be generic, in which case an asterisk is wiser than named fields), and the TABLE name is the name of the worksheet tab, followed by a dollar sign ($). The dollar sign is a required suffix or the sheet will be unknown.

For example if our workbook contains a sheet named “invoices” we could write:

SELECT * FROM [invoice$];

Or if we knew some of the field names, perhaps:

SELECT ESN, COST, UNIT_NUMBER FROM [invoice$];