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.
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>';
Create a table in Exasol with column names and types that match the source.
Use one of the following methods depending on the source file location:
Importing from a local CSV file is only supported using the Exasol JDBC Driver.
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.
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.
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.
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
.