Forum Discussion

amubeen's avatar
amubeen
New Contributor II
5 years ago

Loading Null values into Snowflake DB via Bulk Load Snap

Hey All,

So I’m trying to load certain columns from a csv file into snowflake, but facing some issues.

I have the file, properly parsed it via the csv parser, and I’ve also used a mapper to map the columns properly. However, I need snaplogic to be able to do tell snowflake, if the entry in this row says “(null)” or “(None)” to treat it as a NULL insert in the bulk load.

Is there a way to do this?

For example, say I have a snowflake DB with column that accepts only 1 char, a Y or N. The row from the dataset has a “(null)” as the entry for that column, which is causing an issue since it is > 1 char. Is there a way for snaplogic to understand “(null)” means NULL and insert a NULL into snowflake via the bulk load snap?

Thank you!

4 Replies

  • igormicev's avatar
    igormicev
    Contributor
    5 years ago

    Hi @amubeen,

    You’d better use multiple checks when it comes to nulls from csv file. It can be an empty string, or NULL or null or Null or (null), , etc.

    One way is this:
    ($NULLABLECHAR == ““ || $NULLABLECHAR.toLowerCase().indexOf(‘null’) > -1) ? null : $NULLABLECHAR

    However, settings can help you also, but if you fully trust the csv incoming data (e.g. always having null, but not (null) which is different)

    /Igor

  • skatpally's avatar
    skatpally
    Former Employee
    5 years ago

    Just curious If you have checked this option Load empty strings on the Snowflake Bulk load Snap.

  • smanoharan's avatar
    smanoharan
    Employee
    5 years ago

    @amubeen There are two ways that you can handle this use case

    1. Leave the fields you want to loaded as null as Empty in the csv file and un-select the property, “Load empty strings” in Snowflake Bulk Load. This will make sure Snowflake Bulk Load will load the empty values as null in the table

    2.The null values coming from a csv parser will be a string. A condition in the mapper to convert the null String values to null will work:
    $NULLABLECHAR == “null” ? null : $NULLABLECHAR

    cc: @robin