parse csv file with string fields that contain double quotes and/or commas using Snowflake COPY INTO

Question

MY QUESTION:

How do I construct my copy into statement so that my file properly parses and loads? Thanks!

THE PROBLEM:

I have a csv file that I need to parse and copy to a table from a named stage in Snowflake.

The file looks similar to below:

ID, Name, Job Title,Company Name, Email Address, Phone Number
5244, Ted Jones, Manager, Quality Comms, [email protected],555-630-1277 
5246, Talim Jones,""P-Boss"" of the world, Quality Comms, [email protected],555-630-127 
5247, Jordy Jax,,"M & G Services.",[email protected], 616-268-1546

MY CODE:

COPY INTO DB.SCHEMA.TABLE_NAME
(
ID, 
FULL_NAME,
JOB_TITLE,
EMAIL_ADDRESS
)
FROM 
(
SELECT $1::NUMBER AS ID,
 $2 AS FULL_NAME, 
 $3 AS JOB_TITLE,
 $5 AS EMAIL_ADDRESS
FROM @STAGE_NAME) 
--SNOWFLAKE DOES NOT SUPPORT UTF 16 OR 32 SO HAVING REPLACE INVALID UTF 8 CHARACTERS
FILE_FORMAT = (TYPE = 'CSV', RECORD_DELIMITER = '\n', FIELD_DELIMITER = ',', SKIP_HEADER = 1,FIELD_OPTIONALLY_ENCLOSED_BY = '"',TRIM_SPACE = TRUE,REPLACE_INVALID_CHARACTERS = TRUE)
ON_ERROR = CONTINUE
--COPY A FILE INTO A TABLE EVEN IF IT HAS ALREADY BEEN LOADED INTO THE TABLE
FORCE = TRUE

MY ERROR MESSAGE:

Found character 'P' instead of field delimiter ','

WHAT I HAVE TRIED:

I have tried many things, most notably:

I have tried to escape the double quotes in my select statement for the Job Title.
I have tried removing the FIELD_OPTIONALLY_ENCLOSED_BY = '"' parameter and just using ESCAPE = '"' with no luck.

P-Boss would be the ideal, my main goal is to get the fields to properly parse into the right columns. — user6866797

Abhi Reddy Abhi Reddy · Accepted Answer · 2020-12-09T21:24:55

Try removing the option FIELD_OPTIONALLY_ENCLOSED_BY = '"' and also include a replace function in your inner query.

Example:

  SELECT 
     $1::NUMBER AS ID,
     $2 AS FULL_NAME, 
     replace($3,'"','') AS JOB_TITLE,
     $5 AS EMAIL_ADDRESS
  FROM @STAGE_NAME

parse csv file with string fields that contain double quotes and/or commas using Snowflake COPY INTO

1 Answers