IMPORT DATA FROM A LOCAL FILE

To import data from local files:

1.     Create the destination table.

2.     Use the PUT command to copy the local file(s) into the Snowflake staging area for the table.

3.     Use the COPY command to copy data from the data source into the Snowflake table.

Example:

1.     Create a Snowflake table:

CREATE OR REPLACE TABLE mytable (

  name string,

  id string,

  amount number

)

 

STAGE_FILE_FORMAT = (TYPE = 'csv' FIELD_DELIMITER= '\t' );

2.     Using the PUT command, upload the local file ‘mydatafile.csv’ to the table’s data stage (the staging area in S3):

put file://tmp/mydatafile.csv @%mytable

 

3.     Copy data from the data source into the table:

COPY INTO mytable;

4.     To see the files that have been loaded for the table: 

LIST @%mytable;

LOAD NON-DETERMINISTIC NUMBER OF COLUMNS FROM SOURCE DATA SET INTO TARGET TABLE

Example:      

COPY INTO CSV_TEST FROM @MYSTAGE

FILE_FORMAT = (

COMPRESSION = 'AUTO'

FIELD_DELIMITER = '~' )

ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE

ON_ERROR = SKIP_FILE;

Note:

Specifies whether to generate an error when the columns in the source file do not match that of the destination table. When set to FALSE, an error is not thrown and the load continues. The fields are loaded in the order of the occurrence in the source file.