Load Data from Local or Remote Location
- Create a table in Exasol with column names and types similar to the source.
- Exasol recommends that you create a connection string.
- Use one of the following methods depending on the source file location:
- Run the following statement for the local files:
Importing from a local CSV file is only supported using the Exasol JDBC Driver.
- Run the following statement for the remote files:
- Run the following statement for the local files:
- 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>'
- If your data (for example, dates / timestamps) doesn't match the default format, you can do one of the following:
- Change the default format:
- Specify after the file the list of columns with the according format. For example, to specify the format for column 6:
- If you want to monitor the progress of the import:
- Open a new session in your SQL client.
- Connect and 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:
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;