## Purpose Excel file contents in Open XML, such as used with the file extensions 'xlsx' and 'xlsm', can be interpreted as a data source using the exceltable keyword. The rectangle specifies the rectangular area from which should be taken. The first worksheet is used when no rectangle is specified or a null value as worksheet name. Rows in Excel are interpreted as rows from the data source, whereas columns in Excel are interpreted as columns. The passing option specifies the source of the (binary) contents in zipped Open XML format such as used with the file extensions 'xlsx' and 'xlsm'. The source is often the outcome of a read_file() table function or URL download using httpget(). The interpretation process can be controlled on table level using the table options and the specification ends with the Excel columns being mapped to data source columns using their relative position within the rectangular area. ```sql -- -- Create an in-memory table using data taken from an -- Excel sheet. -- -- The Excel sheet has a named range called 'salesdata' with -- region, period, revenue (EUR), returns (EUR) and quantity -- (pieces). -- create or replace table salesdatacopy@inmemorystorage as select * from exceltable ( name 'salesdata' passing file 'FOLDER\sales.xlsx' columns region varchar2 position 1 , period varchar2 position 2 , revenue number position 3 , returns number position 4 , qty number position 5 ) ``` ## Syntax ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD excelTableSpec_start((START)) excelTableSpec_start --> excelTableSpec_0_0[EXCELTABLE]:::quoted excelTableSpec_0_0 --> excelTableSpec_0_1["("]:::quoted excelTableSpec_0_1 --> excelTableSpec_0_2[excelDataRectangle] excelTableSpec_0_2 --> excelTableSpec_0_3[excelTablePassing] excelTableSpec_0_3 --> excelTableSpec_0_4[excelTableOptions] excelTableSpec_0_4 --> excelTableSpec_0_5[excelTableColumns] excelTableSpec_0_5 --> excelTableSpec_0_6[")"]:::quoted excelTableSpec_0_6 --> excelTableSpec_end((END)) ``` ## excelDataRectangle ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD excelDataRectangle_start((START)) excelDataRectangle_start --> excelDataRectangle_0_0[WORKSHEET]:::quoted excelDataRectangle_0_0 --> excelDataRectangle_0_1[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>] excelDataRectangle_0_1 --> excelDataRectangle_0_2[NAME]:::quoted excelDataRectangle_0_2 --> excelDataRectangle_0_3[AREA]:::quoted excelDataRectangle_0_3 --> excelDataRectangle_0_4[TABLE]:::quoted excelDataRectangle_0_4 --> excelDataRectangle_0_5[NAME]:::quoted excelDataRectangle_0_5 --> excelDataRectangle_0_6[<a href="Invantive UniversalSQL/Grammar/Expression" class="internal-link">expression</a>] excelDataRectangle_0_6 --> excelDataRectangle_end((END)) ``` ## Purpose The rectangle specifies the rectangular area from which data should be taken. Rows in Excel are interpreted as rows from the data source, whereas columns in Excel are interpreted as columns. All cells within the rectangle are considered data; headings should be excluded from the rectangle specification. A rectangle can be either: - The contents of a complete worksheet, specified by an expression returning the worksheet name. Specify null to select the first worksheet independent of name. Alternatively, a worksheet can be selected by specifying an integer indicating the relative position, including hidden worksheet. - A named range within a specific worksheet, specified by expressions for optional worksheet name and named range name. - A cell range identified by an expression with it's dimensions including optional worksheet name and a range definition. The range definition can have various forms: 'A1:Z999' or '$A$1:$Z$999' for an area, 'A:Z' or '1:26' for all cells in column(s) or 'A1' to refer to a specific cell. - An Excel table identified by an expression with the table name. - A named range identified by an expression with the named range name. - Unspecified, selecting the first worksheet independent of name. ## excelTablePassing ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR Start((START)) --> excelTablePassing excelTablePassing -->| | passingSourceOrPathExpression passingSourceOrPathExpression --> End((END)) ``` ## excelTableOptions ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD excelTableOptions_start((START)) excelTableOptions_start --> excelTableOptions_0_0[SKIP_]:::quoted excelTableOptions_0_0 --> excelTableOptions_0_1[EMPTY_]:::quoted excelTableOptions_0_1 --> excelTableOptions_0_2[ROWS]:::quoted excelTableOptions_0_2 --> excelTableOptions_0_3[SKIP_]:::quoted excelTableOptions_0_3 --> excelTableOptions_0_4[FIRST]:::quoted excelTableOptions_0_4 --> excelTableOptions_0_5[numericConstant] excelTableOptions_0_5 --> excelTableOptions_0_6[ROWS]:::quoted excelTableOptions_0_6 --> excelTableOptions_0_7[SKIP_]:::quoted excelTableOptions_0_7 --> excelTableOptions_0_8[LAST]:::quoted excelTableOptions_0_8 --> excelTableOptions_0_9[numericConstant] excelTableOptions_0_9 --> excelTableOptions_0_10[ROWS]:::quoted excelTableOptions_0_10 --> excelTableOptions_end((END)) ``` ## Purpose The interpretation process can be controlled on table level using the table options. A position can be represented using an integer, or by specifying the keyword 'NEXT'. The keyword 'NEXT' specifies that the column is located one position higher than the previous column. When there is no previous column, the column is located at position 1. Empty rows will typically be found when consuming a rectangular area larger than the actual data, such a complete worksheet. The 'skip empty rows' statement eliminates all completely empty rows from the output. ## excelTableColumns ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart LR excelTableColumns_start((START)) excelTableColumns_start --> excelTableColumns_0_0[COLUMNS]:::quoted excelTableColumns_0_0 --> excelTableColumns_0_1[excelTableColumnSpec] excelTableColumns_0_1 --> excelTableColumns_0_2[","]:::quoted excelTableColumns_0_2 --> excelTableColumns_0_3[excelTableColumnSpec] excelTableColumns_0_3 --> excelTableColumns_end((END)) ``` ## Purpose A list of Excel table column specifications. ## excelTableColumnSpec ```mermaid %%{init: { 'theme': 'base', 'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' }, 'themeVariables': { 'fontSize': '11px', 'fontFamily': 'Arial' } }}%% flowchart TD excelTableColumnSpec_start((START)) excelTableColumnSpec_start --> excelTableColumnSpec_0_0[<a href="Invantive UniversalSQL/Grammar/Identifier" class="internal-link">identifier</a>] excelTableColumnSpec_0_0 --> excelTableColumnSpec_0_1[sqlDataTypeExtended] excelTableColumnSpec_0_1 --> excelTableColumnSpec_0_2[POSITION]:::quoted excelTableColumnSpec_0_2 --> excelTableColumnSpec_0_3[numericConstant] excelTableColumnSpec_0_3 --> excelTableColumnSpec_0_4[NEXT]:::quoted excelTableColumnSpec_0_4 --> excelTableColumnSpec_end((END)) ``` ## Purpose Each Excel column is mapped to a data source column using it's relative position within the rectangular area. Position 1 represents the first Excel column falling within the Excel rectangular area.