How to escape double quotes within a data when it is already enclosed by double quotes

Question

I have CSV data separated by comma like below which has to be imported into snowflake table using copy command .

"1","2","3","2"In stick"

Since I am already passing the parameter OPTIONALLY_ENCLOSED_BY = '"' to copy command I couldn't escape the " (double quotes) within the data ("2"In stick") .

The imported data that I want to see in the table is like below

1,2,3,2"In stick

Can someone please help here ? Thanks !

Answer 1

If you are on Windows, I have a funny solution for that. Open this CSV file in MS Excel. Excel consumes correct double quotes to show data in the cellular format and leaves the extra in the middle of a cell (if each cell is separated properly by commas). Then choose 'replace' and replace double quotes with something else (like two single quotes or replace by nothing to remove them). Then save it again as a CSV. I assume other spreadsheet programs should do the same.

Answer 2

If you have an un-escaped quote inside a field which is surrounded by quotes that isn't really valid CSV. For example, here is an excerpt from the RFC4180 spec

7. If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

For example:

"aaa","b""bb","ccc"

I think that whatever is generating the CSV file is doing it incorrectly and needs to be fixed before you will be able to load it into Snowflake. I don't think any file_format option will be able to solve this for you since it's not valid CSV.

The CSV row should either look like this:

"1","2","3","2""In stick"

or this:

"1","2","3","2\"In stick"

Answer 3

I had this same problem, and while writing up the question, I found an answer:

Import RFC4180 files (CSV spec) into snowflake? (Unable to create file format that matches CSV RFC spec)

Essentially, set:

Name	Value
Column Separator	Comma
Row Separator	New Line
Header lines to skip	{you have to decide what to put here}
Field optionally enclosed by	Double Quote
Escape Character	None
Escape Unenclosed Field	None

Here is my ALTER statement:

ALTER FILE FORMAT "DB_NAME"."SCHEMA_NAME"."CSV_SPEC3" SET COMPRESSION = 'NONE' FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '\042' TRIM_SPACE = FALSE ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = 'NONE' ESCAPE_UNENCLOSED_FIELD = 'NONE' DATE_FORMAT = 'AUTO' TIMESTAMP_FORMAT = 'AUTO' NULL_IF = ('\\N');

As I mention in the answer, I don't know why the above works, but it is working for me. Go figure.