Load data from local or remote files

Learn how to load data into Exasol from files stored on a local or remote system.

This article explains how to use the IMPORT statement to load data into Exasol from files stored on a local or a remote system. When importing files from a remote system, the FTP/FTPS, SFTP, and HTTP/HTTPS protocols are supported.

Files can be imported in parallel across nodes, enabling higher throughput for larger datasets. Single files can be read using multiple parallel operations, which results in faster and more efficient data read operations.

Importing from a local file is only supported using EXAplus CLI or the Exasol JDBC driver.

Supported formats

Exasol supports importing CSV and FBV files. For more details, see CSV and FBV file formats and details.

  • For more details about how to use the IMPORT command, see IMPORT.

Create connection to remote system

To prevent connection details from being exposed in logs and audit tables, we recommend that you always create a connection object using CREATE CONNECTION and authenticate using the saved connection instead of providing the details openly in the IMPORT/EXPORTstatements.

Example:
Copy 
CREATE CONNECTION my_conn
    TO 'ftp://<my_server>/path_to_file/'
    USER '<my_username>' 
    IDENTIFIED BY '<my_password>';

Import CSV/FVB files

  1. Create a table in Exasol with column names and types that match the source.

  2. Use one of the following methods depending on the source file location:

    Local file

    Copy 
    IMPORT INTO <target_table> 
        FROM LOCAL CSV FILE '/path/to/filename.csv';

    Remote file

    Copy 
    IMPORT INTO <target_table>
        FROM CSV AT '<connection_string>' 
        FILE '<path_to_file';

File options

You can specify options with IMPORT such as column separator/delimiter, row separator (line break character), and character encoding. You can also specify rows that should be omitted in the import (for example, a header row), and whether to remove spaces before/after columns in a CSV file.

To learn about the available file options for the IMPORT command, see file_opts.

Example:
Copy 
-- CSV file
IMPORT INTO my_table 
    FROM LOCAL CSV FILE '/path/to/filename.csv' 
    ENCODING = 'UTF-8'
    SKIP = 1
;

Specify data format

If data in the imported file does not match the default format for the data type (such as date and timestamp formats), you can either change the default format for the session or specify the format for a column explicitly in the IMPORT statement.

To learn more about data formats, see Format models.

Example 1 - change the default date format for the current session:
Copy 
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';
Example 2 - specify the date format for column 6 in the IMPORT statement:
Copy 
... 
FILE 'myfile.csv' (1..5,6 format='DD.MM.YYYY',7..12) 
...

Error handling

Errors can be written to a table in the database or to a local CSV file. For more details, see error_clause.

Write to error table:
Copy 
IMPORT INTO my_schema.my_table
    FROM LOCAL CSV 
    FILE '/home/user/test.csv'
    ERRORS INTO my_schema.error_table
    REJECT LIMIT 1 ERRORS
;
Write to local CSV file:
Copy 
IMPORT INTO my_schema.my_table
    FROM LOCAL CSV 
    FILE '/home/user/test.csv'
    ERRORS INTO LOCAL CSV 
    FILE '/home/user/error/error.csv'
    REJECT LIMIT 1 ERRORS
;

All WITH clauses in a statement must be defined before the ERROR clause.

Monitoring

To monitor the progress of the import process, you can open a new session in your SQL client and connect to the database, then select the information from the corresponding system table. The number of rows that have been read till now are displayed in the column ACTIVITY.

Copy 
SELECT * FROM EXA_DBA_SESSIONS;

Examples

The following example imports data from a local CSV file, applies custom data and timestamp formats for two columns, defines the encoding and separator/delimiter characters, and specifies that the first row should be omitted in the import.

The REJECT LIMIT 0 clause specifies that no invalid rows in the source file are allowed. For more information, see error_clause.

Copy 
IMPORT INTO DWH.STATE_DIM
    FROM LOCAL CSV 
    FILE 'C:\Work\SampleData\STATE_DIM.csv' (1..3,4 FORMAT = 'DD/MM/YYYY HH24:MI:SS.FF6', 5 FORMAT = 'DD/MM/YYYY HH24:MI:SS.FF6')
    ENCODING = 'UTF-8'
    ROW SEPARATOR = 'CRLF'
    COLUMN SEPARATOR = ','
    COLUMN DELIMITER = '"'
    SKIP = 1
    REJECT LIMIT 0
;

For more examples, see IMPORT.