Imports the data from a delimited text file into a table or view.

Syntax

---

IMPORT TABLE <ImportTable>
FROM <FileName>
IN STORE <StoreName>
[(<ColumnName>[,<ColumnName>])]
[FORMAT DELIMITED|XML]
[ENCODING AUTO|ANSI|UNICODE]
[DELIMITER CHAR <DelimiterChar>]
[QUOTE CHAR <QuoteChar>]
[DATE FORMAT <DateFormat>]
[TIME FORMAT <TimeFormat> [AM LITERAL <AMLiteral> PM LITERAL <PMLiteral>]]
[DECIMAL CHAR <DecimalChar>]
[BOOLEAN TRUE LITERAL <TrueLiteral> FALSE LITERAL <FalseLiteral>]
[USE HEADERS]
[MAX ROWS <MaxRowCount>]

<ImportTable> = <TableName>|<ViewName>

<DateFormat> =

YYYY or YY = Year digits
MM or M = Month digits
DD or D = Day digits
Any other character = literal

<TimeFormat> =

HH or H = Hours digits
MM or M = Minutes digits
SS or S = Seconds digits
ZZZ or Z = Milliseconds digits
N = AM/PM literal
Any other character = literal

Usage

---

Use this statement to import data from a delimited or XML text file in the specified store into a table or view.

Use the optional FORMAT clause to specify the format of the incoming text file. The format can be specified as DELIMITED or XML, and defaults to DELIMITED if the FORMAT clause is not specified.

For delimited text files, ElevateDB expects each incoming row of data to be terminated with a carriage return (#13) and line feed (#10) character, or just a line feed character (#10). For XML text files, ElevateDB expects the incoming data to be in in the following format:

<row>
   <columnname>data</columnname>
   [<columnname>data</columnname>]
</row>

The store must have already been created using the CREATE STORE statement, and must be a local store.

Any existing data in the table or view is not overwritten, and the data in the text file is appended to the table or view. If you wish to replace the contents of a table or view with the contents of the text file, then you should execute the following DELETE statement before executing the IMPORT TABLE statement:

DELETE FROM <TableName>|<ViewName>

If importing into a view, the view must be updateable or an error will be raised.

You can specify the columns in the import file by including them in parentheses after the FROM clause.

Use the ENCODING, DELIMITER CHAR, QUOTE CHAR, DATE FORMAT, TIME FORMAT, DECIMAL CHAR, and BOOLEAN LITERAL clauses to control how ElevateDB reads the data from the import file.

The ENCODING clause is used to determine the character encoding of the import file. If this cause isn't included, then the default encoding is AUTO, meaning that ElevateDB will attempt to determine the character encoding of the import file by first looking for BOM (Byte Order Mark) bytes at the beginning of the file. If it doesn't find them, then it will attempt to determine the character encoding by examining the actual import file characters themselves. If the import file does not include BOM bytes, and you know that the character encoding is UNICODE, then you should specify the UNICODE encoding option.

If the DELIMITER CHAR clause is not included, then the default delimiter character is the comma ',' character.


If the QUOTE CHAR clause is not included, then the default quote character for character strings is the double quote '"' character.

If the DATE FORMAT clause is not specified, then the default date format is the ANSI SQL standard date format 'YYYY-MM-DD'.

If the TIME FORMAT clause is not specified, then the default date format is 'HH:MM:SS.ZZZ N'. The AM LITERAL and PM LITERAL clauses are only used if the N format specifier is included in the time format.

If the DECIMAL CHAR clause is not included, then the default decimal separator character for character strings is the period '.' character.

If the BOOLEAN TRUE LITERAL is not included then the default boolean True literal value is 'TRUE'. If the BOOLEAN FALSE LITERAL is not included then the default boolean False literal value is 'FALSE'.

For delimited text files, the USE HEADERS clause determines whether ElevateDB interprets the first line in the import file as a list of column names that are included in the import file. If any column specified in this line is not a valid column for the table, then it is simply ignored.

For XML text files, the USE HEADERS clause determines whether ElevateDB looks for the following tags in the import file to control which columns are imported:

<columns>
   <column>columnname</column>
   [<column>columnname</column>]
<columns>

If any column specified using these tags is not a valid column for the table, then it is simply ignored. Also, due to the nature of XML, it is possible to have multiple <columns> sections in the same XML import file in order to allow for different columns for the incoming data.


The MAX ROWS clause can be used to limit the number of rows that are imported. This is useful when you simply want to do a preview in order to determine whether the first few lines of the import file are importing correctly.

Examples

---

-- The following example imports tab-delimited
-- data from an import file into the Customer table,
-- using the first line in the import file to determine
-- which columns to import

IMPORT TABLE Customer
FROM "custdata.txt"
IN STORE "ImportFiles"
DELIMITER CHAR #9
USE HEADERS

Required Privileges

---

The current user must be granted the INSERT and SELECT privileges on the table or view into which the data is being imported, and the SELECT privilege on the store from which the file is being imported. Please see the User Security topic for more information.

SQL 2003 Standard Deviations

---

This statement deviates from the SQL 2003 standard in the following ways: