Handling empty string while Copy from S3 csv file to snowflake table

Question

I am trying to load csv file from S3 path. Am facing the below errors

1) Uncaught Error : Numeric value is not recognized. This error is due to NULL value trying to load as a string in numeric column. I found solution for this and implemented below:

create or replace file format test
type = 'CSV'
skip_header = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"'
NULL_IF=('');

Note : It's worked fine. Able to store the source data (NULL)

2) Uncaught Error : NULL result in a non-nullable column We have empty string in the source column which is a not null column. While loading the empty string to snowflake, it's converting to NULL and trying to load, since it's a not null column in snowflake it's throwing error. For this I have tried the below approach, which needs to handle both NULL and empty string.

create or replace file format test
type = 'CSV'
skip_header = 1 FIELD_OPTIONALLY_ENCLOSED_BY = '"'
NULL_IF=('') EMPTY_FIELD_AS_NULL=false;

But this approach is not working,it's working for NULL not working for empty string.

Please let me know how to handle both the empty string and NULL values in snowflake.

Answer 1

Try to include possibilities of null value format in NULL_IF field separated by comma like below:

type = csv 
RECORD_DELIMITER = '\n' 
FIELD_DELIMITER = ',' 
ERROR_ON_COLUMN_COUNT_MISMATCH = false 
FIELD_OPTIONALLY_ENCLOSED_BY = NONE 
skip_header = 0  
NULL_IF=('NULL','',' ','NULL','NULL','//N') 
empty_field_as_null = true

Also validate your input for not null fields which is not returning empty values.

Answer 2

FIELD_OPTIONALLY_ENCLOSED_BY = '\042' 
ESCAPE = 'NONE' 
ESCAPE_UNENCLOSED_FIELD = '\134' 
NULL_IF = ('NULL');