IMPORT CSV Statement

Imports a CSV (comma-separated values) file from disk into a notebook table. This statement is the scripting equivalent of the visual import wizard accessed via the Import menu.

Syntax

Parameters

• filename: text
  The absolute path to the CSV file to be imported.
• table-name: text or identifier
  The name of the notebook table to import the CSV file 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.
• src-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.
• dst-column-name: text or identifier (optional)
  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 (optional)
  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

• SKIP_LINES: integer (non-negative, default: 0)
  Indicates how many initial lines should be skipped in the input file.
  
• TAKE_LINES: integer (-1 or non-negative, default: -1)
  Indicates the maximum number of data lines to read from the file (not including the column header and any lines skipped due to the SKIP_LINES option). If -1 is specified, then the whole file is read.
• HEADER_ROW: integer (0-1, default: 1)
  Indicates whether the CSV file begins with a column header line. If the file contains a column header but not on the first line of the file, use the SKIP_LINES option to indicate how many lines 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.
• SEPARATOR: text (default: ',')
  Allows separators other than commas. The separator must be a single character (specifically, a single UTF-16 code unit). To specify a tab separator, use CHAR(9) which uses the ASCII code 9 to produce the tab character.
• 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
• FILE_ENCODING: integer (0-65535, default: 0)
  Indicates the text encoding to use when reading the CSV file. Specify 0 to detect the encoding automatically. Any nonzero integer is treated as a Windows code page number. See Character Encodings in CSV and Text Files for a list of these code page numbers.
  
• 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

Example

-- All column imported as text.
IMPORT CSV 'C:\file.csv' INTO table1;

-- Selected columns imported as text.
IMPORT CSV 'C:\file.csv' INTO table2 (foo);

-- Selected columns imported with specified data conversions, falling
-- back to text for any value in the input data that can't be converted.
IMPORT CSV 'C:\file.csv' INTO table3 (foo INTEGER, bar TEXT);

-- No header row in the file. Default names "column1", "column2", etc.
-- can be renamed with "AS".
IMPORT CSV 'C:\file.csv' INTO table4 (column1 AS foo, column2 AS bar)
OPTIONS (HEADER_ROW: 0);

-- Semicolon-separated file.
IMPORT CSV 'C:\semicolon.csv' INTO table5 OPTIONS (SEPARATOR: ';');