- Published on
Snowflake CSV COPY Into Examples
- Authors
- Name
- Danny Mican
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_text
1,hi
2,bye
SQL:
COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
)
on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
Defaults - Quoted
CSV:
first,test_text
1,"hi"
2,bye
SQL:
COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_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_text
1,"h\"i"
2,"b\"ye"
SQL:
COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
)
on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
Field Optionally Enclosed By '"' - Quoted
CSV:
first,test_text
1,"hi"
2,bye
SQL:
COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
)
on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
Field Optionally Enclosed by - Quoted Containing Quotes
CSV:
first,test_text
1,"h\"i"
2,"b\"ye"
SQL:
COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
)
on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOAD_FAILED" rowsParsed:2 rowsLoaded:0 errorsSeen:2
Field Optionally Enclosed By With ESCAPE - Quoted Containing Quotes
CSV:
first,test_text
1,"h\"i"
2,"b\"ye"
SQL:
COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
escape = '\\'
)
on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
Field Optionally Enclosed By With Escape - New Line Characters
CSV:
first,test_text
1,"h
i"
2,b
y
e
SQL:
COPY INTO csv_test
FROM @STAGE/data.csv
file_format = (
type = csv
skip_header = 1
fields_optionally_enclosed_by = '"'
escape = '\\'
)
on_error = 'continue';
OUTPUT:
INFO[0194] File:"s3://bucket/data.csv" status:"LOADED" rowsParsed:2 rowsLoaded:2 errorsSeen:0
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!