Load Data from Local or Remote Files

This article explains how to load data into Exasol from a file stored in a local or remote location.

The following procedure explains how to load data from CSV/FBV files that are stored on your local computer or on a remote server (FTP/FTPS, SFTP, HTTP/HTTPS) using the IMPORT statement. For explanations of how to load data from files that are stored on public cloud services, see the other corresponding articles in the Load Data from CSV/FBV Files section.

Create connection

When connecting to a remote service, we recommend that you use CREATE CONNECTION to create a connection object to store connection strings and credentials. 

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

Import data

  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

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

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

    Remote file

    IMPORT INTO <target_table> FROM CSV AT 'CONNECTION_NAME' FILE 'path/to/filename.csv' <file_opts>;

File options

You can specify a number of options for the file import, such as column separator/delimiter, row separator (line break character), and character encoding. You can also specify that a number of rows should be omitted in the import (for example, if the file contains a header), and whether to remove spaces before/after columns in a CSV file. For more details about the available file options, see file_opts.

Specify data format

If data in the file that you want to import 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.

For more details about formats, see Format Models.

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

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.

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;

Monitor the import process

To monitor the progress of the import process, 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.