IMPORT XLS Statement

Imports an Excel worksheet (in either .XLS or .XLSX format) from disk into a notebook table. This statement is the scripting equivalent of the visual import wizard accessed via the Import menu. If the workbook contains multiple worksheets, use the which-sheet argument to specify which worksheet to import. If needed, use the LIST_XLS_WORKSHEETS function to get a list of the worksheets in the workbook.

Syntax

Parameters

• filename: text
  The absolute path to the Excel workbook (.XLS or .XLSX) to be imported.
• which-sheet: text or integer (optional)
  If specified, this indicates which worksheet to import. If omitted, the first worksheet is imported. This value can be either the name of the worksheet or its 1-based index in the workbook.
• table-name: text or identifier
  The name of the notebook table to import the worksheet into. If the table does not exist, it will be created. If it does exist, by default new rows will be appended, but the TRUNCATE_EXISTING_TABLE option can be used to overwrite the existing table data.
• column-name: text or identifier
  The name of a column in the source file to import. If this column name is not found in the source file, then the import operation fails with an error. If no column list is provided, then all columns are imported.
• target-column-name: text or identifier
  If provided, this maps the source column to a different name in the destination table. If not provided, then the target column name is the same as the source column name. If multiple columns are mapped to the same target column name in this way, then the import operation fails with an error.
• data-type: keyword
  If provided, the column data will be parsed into the specified data type. data-type may be one of the following values:
  • TEXT: The input is imported without change (default)
  • INTEGER: A positive or negative integer
  • REAL: Any numeric value
  • DATE: Best-effort conversion into the text format: "YYYY-MM-DD"
  • DATETIME: Best-effort conversion into the text format: "YYYY-MM-DD hh:mm:ss.sss"

Options

• FIRST_ROW: integer (≥ 1, default: 1)
  The number (starting at 1) of the first row of the data to read. If a row of column names is present (see the HEADER_ROW option), FIRST_ROW specifies the row containing the column names, with the data to follow on the next row. If no column names are present, then FIRST_ROW specifies the first row of data.
• LAST_ROW: integer (≥ 0, default: 0)
  Indicates how many rows of data to read. If a value of 0 is specified, then all available rows in the worksheet are imported. If a positive integer is specified, then it is the last row number (inclusive) to be imported.
• FIRST_COLUMN: integer ≥ 1 or text (default: 1)
  The leftmost column (inclusive) to import. This may be a column number (starting at 1) or a column string (A, B, C, ..., XFC, XFD).
• LAST_COLUMN: integer ≥ 0 or text (default: 0)
  The rightmost column (inclusive) to import. This may be a column number (starting at 1) or a column string (A, B, C, ..., XFC, XFD). If 0 is specified, then all available columns in the worksheet (after and including FIRST_COLUMN) are imported.
• STOP_AT_FIRST_BLANK_ROW: integer (0-1, default: 1)
  Whether to stop processing rows when the first blank row is encountered, even if it's before the configured LAST_ROW or the last row in the spreadsheet.
  
• HEADER_ROW: integer (0-1, default: 1)
  Indicates whether the worksheet begins with a column header line. If the sheet contains a column header but not on the first line of the file, use the FIRST_ROW option to indicate how many rows to skip before the column header appears.
  • 0 = No column header. The generic column names column1, column2, etc. will be used.
  • 1 = A column header row exists.
• BLANK_VALUES: integer (1-3, default: 2)
  Determines how blank or empty values in the input file will be imported.
  • 1 = Import as an empty string. If the target data type is not TEXT, then the data conversion will fail.
  • 2 = Import as NULL.
  • 3 = If the target data type is TEXT, then import as an empty string. For other target data types, import as NULL.
    
• TRUNCATE_EXISTING_TABLE: integer (0-1, default: 0)
  If the target table name exists, this option indicates whether the existing data rows should be deleted.
  • 0 = Keep existing rows and append new rows
  • 1 = Delete existing rows
• TEMPORARY_TABLE: integer (0-1, default: 0)
  If the target table name does not exist, and therefore a new table will be created, this option indicates whether the new table will be a temporary table.
  • 0 = Use CREATE TABLE
  • 1 = Use CREATE TEMPORARY TABLE
• IF_CONVERSION_FAILS: integer (1-3, default: 1)
  If data conversion fails (for instance, if a non-numeric value appears in the file in a column defined in the IMPORT CSV statement as INTEGER), this option controls what happens.
  • 1 = Import the value as plain text
  • 2 = Skip the data row
  • 3 = Abort with an error

Examples

-- Imports the first worksheet in "Workbook.xlsx" into a notebook
-- table called mytable. Because no options are specified, it is
-- assumed that the file has a column header on the first line.
-- Because no column list is specified, all columns are imported
-- as text and the original column names are preserved.
IMPORT XLS 'C:\Workbook.xlsx' INTO mytable;

-- Worksheet can be specified by number or by name.
IMPORT XLS 'C:\Workbook.xlsx' WORKSHEET 1 INTO tbl1;
IMPORT XLS 'C:\Workbook.xlsx' WORKSHEET 'Sheet1' INTO tbl1;

-- The source columns (foo, bar) are explicitly specified. If
-- the source file contains other columns besides those two, then
-- they are not imported into the destination notebook table.
IMPORT XLS 'C:\Workbook.xlsx' INTO mytable (foo, bar);

-- Import a specific range of cells.
IMPORT XLS 'C:\Workbook.xlsx' INTO mytable
OPTIONS (
    FIRST_ROW: 1,
    LAST_ROW: 5,
    FIRST_COLUMN: 'A',
    LAST_COLUMN: 'B'
);