csvsnowflake-cloud-data-platformescapingfile-format

how to escape double quote backslash in snowflake file format


My csv file has fields which in enclosed with double quotes and the fields are separated with |. For some of the fields i have below value in csv file:

|"\"Bremen \""|

I want to remove \" so after copy into the output should be saved as :

|"Bremen "|

Below in the file format command i tried but it didn't worked:

CREATE OR REPLACE FILE FORMAT PO_PIPE
    type = 'CSV'    
    TIMESTAMP_FORMAT = 'YYYY-MM-DD hh24:mi:ss.ff' 
    skip_header = 1
    field_delimiter = '|'    
    FIELD_OPTIONALLY_ENCLOSED_BY ='"'
    TRIM_SPACE = TRUE
    null_if = ('\\N','NULL','Null','(null)','null','','\\n')
    EMPTY_FIELD_AS_NULL = TRUE
    ESCAPE = '\\'
    ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE
    COMPRESSION=GZIP;

Solution

  • The provided file format seems to work. For input.csv:

    col|col2
    "\"Bremen \""|1
    "\"Test\""|2
    

    Recreating:

    CREATE OR REPLACE TABLE tab(col TEXT, b INT);
    
    CREATE OR REPLACE FILE FORMAT PO_PIPE
        type = 'CSV'    
        TIMESTAMP_FORMAT = 'YYYY-MM-DD hh24:mi:ss.ff' 
        skip_header = 1
        field_delimiter = '|'    
        FIELD_OPTIONALLY_ENCLOSED_BY ='"'
        TRIM_SPACE = TRUE
        null_if = ('\\N','NULL','Null','(null)','null','','\\n')
        EMPTY_FIELD_AS_NULL = TRUE
        ESCAPE = '\\'
        ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE;
    
    -- place file into the stage
    LIST @TESTSTAGE;
    -- teststage/input.csv
    
    COPY INTO PUBLIC.TAB
     FROM @TESTSTAGE/input.csv
     FILE_FORMAT = (FORMAT_NAME = 'PO_PIPE')
     FORCE = TRUE;
    
    SELECT * FROM tab;
    

    Output:

    enter image description here