SQL Notebook (logo)

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

Syntax for the IMPORT XLS statement

Arguments

Options

Examples

  1. IMPORT XLS 'C:\Workbook.xls' INTO mytable;
    Imports the first worksheet in "Workbook.xls" 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.
  2. IMPORT XLSX 'C:\Workbook.xls' INTO tbl1; IMPORT XLS 'C:\Workbook.xlsx' INTO tbl2;
    The keywords XLS and XLSX are interchangeable and need not match the file's actual extension.
  3. IMPORT XLS 'C:\Workbook.xls' WORKSHEET 1 INTO tbl1;
    Imports the first worksheet in the workbook.
  4. IMPORT XLS 'C:\Workbook.xls' WORKSHEET 'Sheet1' INTO tbl1;
    Imports the worksheet named "Sheet1".
  5. IMPORT XLS 'C:\Workbook.xls' INTO mytable (foo, bar);
    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. If the source file does not contain the specified columns, then the import fails. If the destination table already exists and does not contain the specified column names, then the import fails.
  6. IMPORT XLS 'C:\Workbook.xls' INTO mytable (foo AS aaa, bar AS bbb);
    The source columns (foo, bar) and target columns (aaa, bbb) are explicitly specified.
  7. IMPORT XLS 'C:\Workbook.xls' INTO mytable (foo TEXT, bar INTEGER);
    A data type conversion is specified for each source column. If the conversion fails (for instance, if a non-numeric value appears in the CSV file in the bar column), by default the value is imported as text. SQLite treats column types as suggestions, so the integer column can contain a text value.
  8. IMPORT XLS 'C:\Workbook.xls' INTO mytable (column1 AS foo, column2 AS bar) OPTIONS (HEADER_ROW: 0);
    A file that lacks a header row is imported with new names given to the unnamed source columns.
  9. IMPORT XLS @filename INTO @tablename (@old_col AS @new_col);
    Various arguments to IMPORT XLS are provided in variables rather than using literal strings. This allows these names to be dynamically generated or otherwise determined at script runtime.