Selecting Data

Excel worksheets can be organized in different ways. The adapter offers settings to select data based on various organizational methods.

With Header Cells

If a spreadsheet has column headers the adapter can use them to represent the column names of the worksheet table. To use header cells set the Header property to True. Then you can query like so:

SELECT RowId, Name, Item, Quantity, Amount FROM Sheet1 WHERE Amount > '50'

The query above assumes that the first row of the spreadsheet has the column names RowId, Name, Quantity, and Amount. The spreadsheet may have more columns than were selected and they can be in any order.

Without Header Cells

If a spreadsheet does not have header columns, or if you would like to ignore the header columns, you can set the Header property to false. In this case each column is represented by the column name in Excel; i.e., A, B, C, ...

SELECT A, B, C, D FROM Sheet1 WHERE D > '50'

Orientation

If a spreadsheet is laid out horizontally (i.e., the column names are arranged vertically in the first column) then you can set the Orientation property to Horizontal to read each column as a row in the table. The Orientation property can be used in conjunction with the Header connection property, and the range syntax described below.

If header columns are not set and the orientation is horizontal, the column names will be R1, R2, R3, ...

SELECT R1, R2, R3 FROM Sheet1 WHERE R2 > '50'

Range

You can use the range feature to select data from a specific portion of the spreadsheet; for example, if you have a sparse spreadsheet that is not top-left aligned or if the data of interest is somewhere in the middle of the sheet. The range is specified as part of the table name. For example the following command will select the range of cells between A1 and E5:

SELECT * FROM Excel_Sheet#A1:E5

In case the sheet name contains a quote, if you want to select only a range, the correct Excel syntax involves using an extra quote as an escape character and enclosing the full sheet name with quotes. For example the following command will select the range of cells between A1 and E5 for the sheet named Excel'Sheet:

SELECT * FROM ['Excel''Sheet'#A1:E5]

You can also use wildcards (*) in the ending cell for the range. Ranges similar to '#A5:F*', '#A5:*20', and '#A5:**' are supported. For example the following command will select the range of cells between A5 and column F, up to the last row in the spreadsheet:

SELECT * FROM Excel_Sheet#A5:F*

Named Range

If a spreadsheet has named range defined in it, then you can query this range as if it were a table. You can issue the following query to select data from the named range SALES.