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

Syntax

Connect-ExcelOnline -DefineTables string

Data Type

cstr

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.

Sometimes, specifications of DefineTables include characters, like ., that can interfere with the parsing of the command unless they are protected ("escaped"). To protect such specifications, surround the name containing the protected character with angle brackets; for example, [work.book_work.sheet!A1:Z50]). This enables the command to be parsed properly. In the previous example, the driver searches for the workbook and worksheet identified by work.book_work.sheet, even though the specification includes reserved characters.

After you set DefineTables, you can 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 selects the range of cells between A1 and E5:

SELECT * FROM Test_xlsx_Sheet1#A1:E5

Example

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