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;
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: