0

Summary:

Original question from a year ago: How to escape double quotes within a data when it is already enclosed by double quotes

I have the same need as the original poster: I have a CSV file that matches the CSV RFC spec (my data has double quotes that are properly qualified, my data has commas in it, and my data also has line feeds in it. Excel is able to read it just fine because the file matches the spec and excel properly reads the spec).

Unfortunately I can't figure out how to import files that match the CSV RFC 4180 spec into snowflake. Any ideas?

Details:

We've been creating CSV files that match the RFC 4180 spec for years in order to maximize compatibility across applications and OSes.

Here is a sample of what my data looks like:

KEY,NAME,DESCRIPTION
1,AFRICA,This is a simple description
2,NORTH AMERICA,"This description has a comma, so I have to wrap the whole field in double quotes"
3,ASIA,"This description has ""double quotes"" in it, so I have to qualify the double quotes and wrap the field in double quotes"
4,EUROPE,"This field has a carriage 
return so it is wrapped in double quotes"
5,MIDDLE EAST,Simple descriptoin with single ' quote

When opening this file in Excel, Excel properly reads the rows/columns (because excel follows the RFC spec):

Picture showing same example data, but opened in excel

In order to import this file into Snowflake, I first try to create a file format and I set the following:

Name Value
Column Separator Comma
Row Separator New Line
Header lines to skip 1
Field optionally enclosed by Double Quote
Escape Character "
Escape Unenclosed Field None

But when go to save the file format, I get this error:

Unable to create file format "CSV_SPEC". SQL compilation error: value ["] for parameter 'FIELD_OPTIONALLY_ENCLOSED_BY' conflict with parameter 'ESCAPE'

It would appear that I'm missing something? I would think that I must be getting the snowflake configuration wrong. (

Colorado Techie
  • 1,302
  • 1
  • 13
  • 21

1 Answers1

0

While writing up this question and testing all the scenarios I could think of, I found a file format that seems to work:

Name Value
Column Separator Comma
Row Separator New Line
Header lines to skip 1
Field optionally enclosed by Double Quote
Escape Character None
Escape Unenclosed Field None

Same information, but for those that prefer screenshots:

enter image description here

Same information again, but in SQL form:

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');

I don't know why this works, but it does, so, there you.

Colorado Techie
  • 1,302
  • 1
  • 13
  • 21