Selecting ExcelOnline Data
The following connection properties enable you to execute SELECT queries that closely match how your sheet is organized.
Selecting Column HeadersIf your worksheet has column headers, you can set the Header property to use column names defined in a header row. Then you can query like so:
SELECT Id, Name, Item, Quantity, Amount FROM Test_xlsx_Sheet1 WHERE Amount = 50The query above assumes that the first row of the worksheet has the column names Id, Name, Quantity, and Amount. The worksheet may have more columns than were selected and they can be in any order.
Selecting Alphabetical ColumnsIf a workbook does not have headers, or if you would like to ignore the headers, you can set the Header property to false. In this case each column is represented by alphabetical names. For example:
SELECT A, B, C, D FROM Test_xlsx_Sheet1 WHERE D = 50
Selecting RangesInstead of working with the spreadsheet as a single table, you can work with ranges as tables. Some examples: In a sparse workbook, you can use ranges to select data without the empty rows. Or, in a worksheet that is not top-left aligned, you can use a range to select data in the middle of the sheet.
To select a range of data on the worksheet, append the range to the table 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
To assign a table name to a range, and avoid specifying the range repeatedly, see the Tables property.