Is there way to load csv with data consist with single quote, double quote and new line?

Question

I have .csv with data like below

1234|Player's| Team, be on time to collect the
prizes|2019:02:02
1235|Player's|<body ref="tag">|2019:02:03

I am trying to do copy command to load the data in Snowflake warehouse.

Record failed with the position [id] at line number 2.

To solve this provided the quote for new line data with double quotes but then it failed at 3rd row("tag").

Copy command below: copy into TEST_DB.TEST from s3://XXXXXXXXX/TEST.csv credentials = (aws_key_id = 'XXXXXX' aws_secret_key = 'XXXXXX') file_format = (type = csv field_delimiter = '|' field_optionally_enclosed_by="'") — kuldeep sharma
The only way to do this is to enclose the field that contains the newline inside quotes. You have said that you've done this but the sample data in the question show that you haven't - can you update the question with your new data? — Simon D

Vishnu Vishnu · Accepted Answer · 2019-06-05T18:26:09

@kuldeep - You can't load this data since new line characters are used as a row delimiter as well as a value in the data. You need to escape the new lines before exporting the data, and then mention the escape character in Snowflake's file format before loading them into the DW.

When you export this data from the source system into S3/blob, make sure you add quotes and as escape character. This would ensure (1) new line characters are escaped and quoted in row 1 (2) tag quotes are escaped and then quoted in row 2. If the data is specific to a particular table, you could just create a specific file format in Snowflake for the table and use it along with the copy statement. For instance, choose CSV file format with backslash as the escape character and optionally enclosed field set to double quote.

 CREATE FILE FORMAT CSV_ESC_DQ TYPE = 'CSV'  COMPRESSION = 'AUTO'
   FIELD_DELIMITER = ',' RECORD_DELIMITER = '\n' SKIP_HEADER = 0
   FIELD_OPTIONALLY_ENCLOSED_BY = '\042' TRIM_SPACE = FALSE
   ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE ESCAPE = '\134'
   ESCAPE_UNENCLOSED_FIELD = '\134' DATE_FORMAT = 'AUTO' 
   TIMESTAMP_FORMAT= 'AUTO' NULL_IF = ('\\N');

   copy into table_name from @stage/path_to_file/
   file_format=csv_esc_dq;

Is there way to load csv with data consist with single quote, double quote and new line?

1 Answers