NULL instead no value

[marbys]

Hi,

I'm trying to write dataframe into Snowflake table with function write_with_schema. Data comes from excel file, where we have some columns with no value and we want to keep it that way.  I used parameters with read_excel and also fillna('') and dataset looks ok (no nulls, only no values/empty), but after using wrIte_with_schema we are getting nulls  in those columns. Is there a way to insert data with no value/string lenght 0 instead of nulls?

[Turribeach]

Can you check the data type of the column in the data frame and the column in the Snowflake table? Are they the same type?

[marbys]

Object type for dataframe and varchar in Snowflake

[Turribeach]

What version of pandas are you using?

[Turribeach]

Are you sure you are applying fillna() correctly?

https://stackoverflow.com/a/67356585/10491951

Can you you do a count and group by that column to see all possible values after the fillna()?

[marbys]

Yes, dataframe looks ok. I just tried different option to have no value instead on null in dataframe, but it seems, that write_with_schema still converts that to null.

Even if there is '' as a value, it goes as null into Snowflake. 

[Turribeach]

Your Python code doesn't look right to me. This is how you usually write to a Dataset from a Python recipe:

# Write recipe outputs
customers_out = dataiku.Dataset("some_poutput_dataset")
customers_out.write_with_schema(df)

 I don't see where you got OUTPUT from in your code.

[marbys]

I have OUTPUT declared in a different cell.

OUTPUT = dataiku.Dataset("OUTPUT_DATAIKU")

Everything works, except no values(''), that somehow are converted to null when using write_with_schema/write_from_dataframe

[Turribeach]

Can I suggest you restart your kernel and run from top to bottom again? Or run the code in a recipe to make sure there are now left overs from you playing in the Notebook.

[marbys]

Done that, I even checked e.g. fillna('5') and it goes to Snowflake as 5. I don't think it's an issue with python/pandas/dataframe, but specifically with writing to Snowflake and using built-in (write_with_schema/writre_from_dataframe) function to do that. Maybe there is a parameter or different function to use?

[Turribeach]

Try write_with_schema(df, drop_and_create=True) and with write_dataframe(df, infer_schema=False)

[marbys]

That's not a solution,  everything goes to Snowflake as it should, except '' or "" or ' '- still as null

[marbys]

Raw is my expected output