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

DATA:

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

DATA:

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

DATA:

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

DATA:

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

DATA:

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

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!

References:

• https://community.snowflake.com/s/question/0D50Z00008VhvlYSAR/how-to-escape-newlines-on-copy-into-external-stage
• https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#type-csv