Improved selection capabilities of exceltable table

Wiki (en)
,
1

Invantive SQL can access data stored in Excel files using the exceltable keyword as illustrated in Query verifymail.io using SQL statements or Combining Excel-based sales estimate with Exact Online actuals. An extensive explanation of the capabilities is available at:

Starting release 22.1.293-BETA, the specifications of the range containing the data within the Excel-file have become more flexible.

The following variants are enabled as of release 22.1.293-BETA.

Excel range without worksheet name

An Excel range can be specified without worksheet name in various formats:

  • A1:Z999
  • A:Z
  • 1:26
  • A1
  • $A$1:$Z$999

as in the code below. If no worksheet is specified, the first worksheet will be used.

select xlsx.*
from exceltable
       ( area 'A:I
         passing ...
         columns col1 varchar2 null position next
         ...

Excel worksheet by Number

Instead of a name, the number of the worksheet (including hidden worksheets) can be specified:

select xlsx.*
from exceltable
       ( worksheet 1
         passing ...
         columns col1 varchar2 null position next
         ...

No worksheet

By omitting the worksheet name or omitting the area specification altogether, the entire first worksheet is used, regardless of its name:

select xlsx.*
from exceltable
       ( worksheet null
         passing ...
         columns col1 varchar2 null position next
         ...

select xlsx.*
from exceltable
       ( passing ...
         columns col1 varchar2 null position next
         ...