Load Data from Local or Remote Location

1. Create a table in Exasol with column names and types similar to the source.
2. Exasol recommends that you create a connection string.

   CREATE CONNECTION CONNECTION_NAME
   TO 'ftp://<your_server>/path_to_file/'
   USER '<username>' 
   IDENTIFIED BY '<somepass>';

3. Use one of the following methods depending on the source file location:
   • Run the following statement for the local files:

     IMPORT into <targettable> from local CSV file '/path/to/filename.csv' <options>;

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

   • Run the following statement for the remote files:

     IMPORT into <targettable> from CSV at 'CONNECTION_NAME' file 'path/to/filename.csv' <options>;

4. You can choose the following options to format the file:
   • Skip a header: If your files contain a header, use SKIP=1
   • Set the right line break:
     • Windows: ROW SEPARATOR='CRLF'
     • Linux(default): ROW SEPARATOR='LF'
     • Mac: ROW SEPARATOR='CR'
   • Set the separator between the fields (usually comma): COLUMN SEPARATOR='<your_character>'
   • Set the delimiter of the fields (common: double quotes): COLUMN DELIMITER='<your character>'
5. If your data (for example, dates / timestamps) doesn't match the default format, you can do one of the following:
   • Change the default format:

     alter session set NLS_DATE_FORMAT='DD.MM.YYYY'; --or your format
     alter session set NLS_TIMESTAMP_FORMAT='DD.MM.YYYY-HH24:MI.SS.FF3'; --or your format

   • Specify after the file the list of columns with the according format. For example, to specify the format for column 6:

     ... file 'myfile.csv' (1..5,6 format='DD.MM.YYYY',7..12) ...

6. If you want to monitor the progress of the import:
   1. Open a new session in your SQL client.
   2. Connect and then select the information from the corresponding system table.
   3. The number of rows that have been read till now are displayed in the column ACTIVITY:

      select * from EXA_DBA_SESSIONS;

Example

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;