S3 to Snowflake ( loading csv data in S3 to Snowflake table throwing following error)

Question

I am trying to load .csv file data to Snowflake table and using following command

COPY INTO MYTABLE FROM @S3PATH PATTERN='.*TEST.csv' FILE_FORMAT = (type = csv skip_header = 1) ON_ERROR = CONTINUE PURGE=TRUE FORCE=TRUE;

Following scenario I am seeing

1) if even one column of the table is numeric it will throw error 
         Numeric value '""' is not recognized


2) if i change all the columns data type to varchar, then it will load the data but it will populate
   all the columns data with "" double quotes  ( instead of 15 , "15") 

Thanks in advance for your response!

Answer 1

You're likely missing FIELD_OPTIONALLY_ENCLOSED_BY = '\042' in your file_format. Add that in and try.

https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html#type-csv

https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html

Answer 2

Thanks CodeMonkey! One issue is solved

current scenario: One column is defines as " NUMBER" in SF table and if the csv file has a value populated for that columns then those were the only rows loaded in the table. basically if the numeric column in csv file is null (or blank) those record as not loaded.

also tried using EMPTY_FIELD_AS_NULL = TRUE still the same result as above.

"first_error" message: Numeric value '' is not recognized

Answer 3

here is what i did and it is working

FILE_FORMAT = (type = csv field_delimiter = ',' skip_header = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '\042' EMPTY_FIELD_AS_NULL = TRUE NULL_IF = ('NULL','null','')) ON_ERROR = CONTINUE PURGE=TRUE FORCE=TRUE;