bulk load into snowflake breaks when field has a double-quote in it

1
votes

I am loading data from file in S3 using copy into command and have following parameters:

file_format = (
 type = 'csv'
 field_delimiter = '~'
 FIELD_OPTIONALLY_ENCLOSED_BY= '"'
 EMPTY_FIELD_AS_NULL = TRUE
 NULL_IF=""
 ESCAPE_UNENCLOSED_FIELD = None)

My load is breaking every time I have double-quote inside a field. I tried to replace it by escape \" and adding escape = '\' to the file format parameters, but nothing seems to work. Can someone offer a solution?

Thank you,

1
snowflake-cloud-data-platformbulk-load
Did you try ESCAPE='\\' ? (With double slashes.) – waldente
If your fields have double quotes, you will need to escape them with another set of double quotes. For example, if your field looks like "John replied "Yes" to our survey", the data will need to be escaped like this: "John replied ""Yes"" to our survey". This is usually the most straightforward solution. – Suzy Lockwood
If you have a double or single quote within the text field, you can not use FIELD_OPTIONALLY_ENCLOSED_BY= '"' , it has to be set as None and once it is loaded into staged table, you need to either apply replace or any UDF to load to final table. – Data Engineering Simplified
I tried using ESCAPE='\\' - didn't work – mayolika

1 Answers

0
votes
Create or replace file format name
 file_format = (type = 'csv')
 field_delimiter = '~' 
 FIELD_OPTIONALLY_ENCLOSED_BY= '"' 
 EMPTY_FIELD_AS_NULL = TRUE 
 NULL_IF="" 
 ESCAPE_UNENCLOSED_FIELD = '\\'

Can you please share a data sample? Is the staged file on S3 compressed?

COMPRESSION = AUTO