Copy command in Snowflake database fails when csv file has comma in a string

Question

I am copying a csv file from a stage table in snowflake into a normal snowflake table.

Some rows in the csv have commas in a string and the copy command fails because of it.

COPY INTO TEST_DB.TEST_SCHEMA.USER FROM @user_test/users.csv.gz 
file_format=(TYPE=csv field_delimiter=',' skip_header=0 ) on_error = 'abort_statement'

I get the following error

SQL Error [100016] [22000]: Field delimiter ',' found while expecting record delimiter '\n'
  File 'users.csv.gz', line 32, character 54

I also tried to use FIELD_OPTIONALLY_ENCLOSED_BY this gives me another error

COPY INTO TEST_DB.TEST_SCHEMA.USER FROM @user_test/users.csv.gz 
file_format=(TYPE=csv field_delimiter=',' skip_header=0 FIELD_OPTIONALLY_ENCLOSED_BY = '"') 
on_error = 'abort_statement'

I get the following error

SQL Error [100066] [22000]: Found character '\r' instead of record delimiter '\n'
  File 'users.csv.gz', line 32, character 78

The row in csv it is failing on looks like the following

100,True,0,2010-10-07 12:19:42,,400,8,467,"Authority, Mail Service"

I have noticed that only string with comma in it has double quotes around it.

Could that be the reason it still fails with FIELD_OPTIONALLY_ENCLOSED_BY ?

jmf · Accepted Answer · 2020-01-26 14:39:07Z

4

Finally found a solution.

The csv file I created with python had linespace between each row.

After getting rid of linespace in csv, FIELD_OPTIONALLY_ENCLOSED_BY ='"' worked and data was loaded.

answered Jan 26, 2020 at 14:39

jmf

3961 gold badge7 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Copy command in Snowflake database fails when csv file has comma in a string

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related