This article shows you some of the SQL methods for reading records from a Microsoft 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 spreadsheet 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 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 represents the first field, F2 represents the second field, and so on).
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 default number of rows to scan is eight (8) rows; you can change the number of rows to scan by specifying a value between one (1) and sixteen (16) in the MAXSCANROWS setting in the extended properties of your connection string.
Last Modified On:
No, open a new Support Case
