Note: If you are using a 64-bit system then you should use
Provider=Microsoft.ACE.OLEDB.12.0;
instead of
Provider=Microsoft.Jet.OLEDB.4.0;
The following is a modified 64-bit example for loading workbook.xls which is put in C:\qv_data folder:
/* Connect using the Jet provider. Specify the workbook path in the "Data Source" spec below. */
CONNECT TO [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\qv_data\workbook.xls;Extended Properties="Excel 8.0;"];
/*
The SQLtables statement will load the "tables" table with a set of fields
describing the tables in the ODBC datasource. For Excel, each sheet is a table.
*/
tables: // Name the table that will be created by SQLTables stmt
SQLtables;
DISCONNECT; // Don't need ODBC connection anymore
/*
One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.
We will loop through this set of sheet names.
*/
FOR i = 0 to NoOfRows('tables')-1
/*
Assign the TABLE_NAME to the variable "sheetName".
TABLE_NAMEs that contain spaces will be enclosed in single quotes.
The purgeChar function will remove any quotes.
*/
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
// Optional filtering logic to select certain sheets
IF wildmatch('$(sheetName)', 'Sales*') THEN // Only sheetNames that begin "Sales"
Sales:
// Now that we have a sheet name, a standard biff (Excel) load can be used.
LOAD *,
'$(sheetName)' as Sheet // Optionally, the sheetName value may be loaded as a field
FROM C:\qv_data\workbook.xls (biff, embedded labels, table is [$(sheetName)]);
END IF // End of optional sheet filtering
NEXT
DROP TABLE tables; // The table list is no longer needed
