0

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 !

Madhankkumar S A
  • 47
  • 7

3 Answers3

0

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.

Amin Gheibi
  • 639
  • 1
  • 8
  • 15
  • Thanks ! But the tricky part is I cant make any changes to the input data. I somehow need to tackle this while loading the data into a table . – Madhankkumar S A Nov 06 '20 at 09:39
0

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

  1. 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"
Community
  • 1
  • 1
Simon D
  • 5,730
  • 2
  • 17
  • 31
  • Simon D makes a good point here. The example data provided by the poster doesn't follow the CSV/RFC4180 spec. Assuming it did, though, I found the question still valid. – Colorado Techie Dec 28 '21 at 18:39
0

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.

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