SQL methods for reading records from a MS Excel Spreadsheet via ADO/ODBC

From Support

This article details some of the SQL methods for reading records from a MS Excel Spreadsheet via ADO/ODBC

Table Naming Conventions

There are several ways you can reference a table (or range) in an Excel workbook:

Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner consists of the entire used range of the worksheet.

"Select * from [Sheet1$]"
              

Use a range with a defined name (for example, [Table1]).

"Select * from Table1"
              

Use a range with a specific address (for example, [Sheet1$A1:B10]).

"Select * from [Sheet1$A1:B10]"
              

Table Headers

With Excel workbooks, the first row in a range is considered to be the header row (or field names) by default. If the first range does not contain headers, you can specify HDR=NO in the extended properties in your connection string. If the first row does not contain headers, the OLE DB provider automatically names the fields for you (where F1 would represent the first field, F2 would represent the second field, and so forth).

Data Types

Unlike a traditional database, there is no direct way to specify the data types for columns in Excel tables. Instead, the OLE DB provider scans a limited number of rows in a column to "guess" the data type for the field. The number of rows to scan defaults to eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) for the MAXSCANROWS setting in the extended properties of your connection string.