Define the ranges within a worksheet that will appear as tables.

Data Type

string

Default Value

""

Remarks

The value for this property is supplied as a semicolon-separated list of name-value pairs in the form [Table Name]=[Catalog].[Schema].[{Workbook Name}_{Worksheet Name}!{Range}].

The value can also be specified in the form [Table Name]=[{Workbook Name}_{Worksheet Name}!{Range}]. Note that in this case, the driver will attempt to push the defined table for each schema it identifies, should the specified workbook and worksheet exist in the schema.

Specifications of DefineTables, sometimes need to use protected characters, like .. Such characters must be protected ("escaped") when used in a specification, by ensuring that the name containing them are surrounded with angle brackets; for example, [work.book_work.sheet!A1:Z50]. This ensures that the command will be parsed properly. For this example, the driver searches for the workbook and worksheet identified by work.book_work.sheet, even though the specification includes reserved characters.

After setting DefineTables, you can then issue queries that reference the specified table name. Note that you can also use the range syntax directly in the SQL statement: append the range to the worksheet name with the # character.

For example, the following command will select the range of cells between A1 and E5:

SELECT * FROM Test_xlsx_Sheet1#A1:E5

Example DefineTables Value

DefineTables="Table1=Test_xlsx_Sheet1!A1:N25,Table2=[CData].[MySchema].[Spreadsheet1_Sheet2!C3:M53],Table4=xIsPcLs2-bF3AavQcSLCfzs3kGc_Sheet4!C20:N60"