escape double quotes in snowflake

Question

I'm trying to load the data using copy into command. Field has special character as value \", but FIELD_OPTIONALLY_ENCLOSED_BY its escaping \ and getting error while loading

Found character '0' instead of field delimiter ';'

DATA:

"TOL";"AANVR. 1E K ZIE RF.\";"011188"

After escaping second column value its considering and escaping delimiter AANVR. 1E K ZIE RF.\"; but actually it should be AANVR. 1E K ZIE RF.\.

File format

 CREATE OR REPLACE FILE FORMAT TEST                  
       FIELD_DELIMITER = ';'                                                                                                                                         
       SKIP_HEADER = 1                                                                                                                                         
       TIMESTAMP_FORMAT = 'MM/DD/YYYYHH24:MI:SS'                                                                                                                                         
       escape = "\\"                                                                                                                                '
       TRIM_SPACE = TRUE                                                                                                                                         
       FIELD_OPTIONALLY_ENCLOSED_BY = '\"'                                                                                                                                         
       NULL_IF = ('')                                                                                                                                         
       ENCODING = "iso-8859-1"                                                                                                                                         
 ; 

Answer 1

If you need to replace double quotes in an existing table, you can use '\"' syntax in replace function. Example provided below.

select replace(column_name,'\"','') as column_name from table_name

Answer 2

Rough example, but the below works for me. Let me know if you're looking for a different output.

CREATE OR REPLACE table DOUBLE_TEST_DATA (
   string1 string
 , varchar1 varchar
 , string2 string
 );                                                                                                                               

COPY INTO DOUBLE_TEST_DATA FROM @TEST/doublequotesforum.csv.gz
FILE_FORMAT = (
  TYPE=CSV
, FIELD_DELIMITER = ';' 
, FIELD_OPTIONALLY_ENCLOSED_BY='"'
);

select * from DOUBLE_TEST_DATA;

Output: