CODEWITHSUNDEEP
amazon-s3delimitersnowflake-cloud-data-platform
zzzzzzzch
zzzzzzzch

Reputation: 69

Trouble copying CSV data using the field_optionally_enclosed_by param

I have a two line CSV file below:

id, name, value1, alias, user, description, start_time, end_time
24,"quick brown fox, lazy dog",73.0,xyz,username,None,2020-03-16 12:00:00,2020-03-17 19:07:10

The file is already in S3 and I've copied other files successfully using the below command. The problem is that on copy to snowflake, the command breaks I think due to the second field containing a comma. I'm looking for assistance in troubleshooting this.

The table is defined as:

id:int, 
name:text, 
value1:numeric, 
alias:text, 
user:text, 
description:text, 
start_time:timestamp, 
end_time:timestamp

And the copy to snowflake command is defined as:

COPY INTO schema.table FROM s3://bucket1/
files = ('filepath/filename.csv')
credentials =(aws_key_id='XXXXXXX' aws_secret_key='XXXXXXX')
file_format = (type = csv field_delimiter = ',' skip_header = 1
field_optionally_enclosed_by = '"' null_if = ('\N', 'None', 'nan'));

Running it yields the following error:

Error: Number of columns in file (1) does not match that of the corresponding table (9), use file format option error_on_column_count_mistmatch=false to ignore this error

I look forward to any advice on moving forward. Thank you!

Upvotes: 0

Views: 5469

Answers (2)

Swapnil Gharat
Swapnil Gharat

Reputation: 11

This should solve your issue:

file_format = (type='CSV', SKIP_HEADER=1, ESCAPE_UNENCLOSED_FIELD ='None', FIELD_OPTIONALLY_ENCLOSED_BY='"', SKIP_BLANK_LINES=True)

Upvotes: 1

Greg Pavlik
Greg Pavlik

Reputation: 11086

Your test CSV has a slight anomaly from how apps would write out standard CSV. Snowflake is being told that the fields are optionally enclosed by double quotes, but that's not precisely right.

On the right side your second column is enclosed by a double quote, but on the left side it's enclosed by a space and double quote.

If you try with these lines with the spaces removed (since any app writing out a CSV would format it without spaces), it should work for you.

id,name,value1,alias,user,description,start_time,end_time
24,"quick brown fox, lazy dog",73.0,xyz,username,None,2020-03-16 12:00:00,2020-03-17 19:07:10

Upvotes: 1

Related Questions