Snowflake CSV COPY Into Examples
January 22, 2021
This article lists a number snowflake configuration csv copy into parameters and the outputs they produce. I’m currently working on a snowflake migration which requires loading CSV files. The CSV options, as described in the snowflake documentation, were a bit confusing so I created a series of test cases that show the options, CSV input and the data as it was loaded into snowflake.
Defaults - Unquoted
CSV:
first,test_text1,hi2,bye
SQL:
COPY INTO csv_testFROM @STAGE/data.csvfile_format = (type = csvskip_header = 1)on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
DATA:
Defaults - Quoted
CSV:
first,test_text1,"hi"2,bye
SQL:
COPY INTO csv_testFROM @STAGE/data.csvfile_format = (type = csvskip_header = 1)on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
Defaults - Quoted Containing Quotes
CSV:
first,test_text1,"h\"i"2,"b\"ye"
SQL:
COPY INTO csv_testFROM @STAGE/data.csvfile_format = (type = csvskip_header = 1)on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
DATA:
Field Optionally Enclosed By ’”’ - Quoted
CSV:
first,test_text1,"hi"2,bye
SQL:
COPY INTO csv_testFROM @STAGE/data.csvfile_format = (type = csvskip_header = 1fields_optionally_enclosed_by = '"')on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
DATA:
Field Optionally Enclosed by - Quoted Containing Quotes
CSV:
first,test_text1,"h\"i"2,"b\"ye"
SQL:
COPY INTO csv_testFROM @STAGE/data.csvfile_format = (type = csvskip_header = 1fields_optionally_enclosed_by = '"')on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOAD_FAILED" rowsParsed:2 rowsLoaded:0 errorsSeen:2
DATA:
Field Optionally Enclosed By With ESCAPE - Quoted Containing Quotes
CSV:
first,test_text1,"h\"i"2,"b\"ye"
SQL:
COPY INTO csv_testFROM @STAGE/data.csvfile_format = (type = csvskip_header = 1fields_optionally_enclosed_by = '"'escape = '\\')on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
DATA:
Field Optionally Enclosed By With Escape - New Line Characters
CSV:
first,test_text1,"hi"2,bye
SQL:
COPY INTO csv_testFROM @STAGE/data.csvfile_format = (type = csvskip_header = 1fields_optionally_enclosed_by = '"'escape = '\\')on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
DATA:
Conclusion
I found it easy to explore the COPY INTO csv API through a series of small test cases. If you’d like to see another test case, please let me know and I’d be happy to add it!