topic Re: Writing null values into database instead of empty strings in Talend Studio

Writing null values into database instead of empty strings

HeinBloed — Sat, 16 Nov 2024 09:14:10 GMT

Hi,

I try inserting data from a csv file into a postgres database. In the csv file several cells are empty. In my schema the columns are all nullable.

Nevertheless, talend writes empty strings '' into the database instead of null values.

Do you have any idea how I can insert null values?

Thanks

Hein

Re: Writing null values into database instead of empty strings

brama — Fri, 29 Sep 2017 17:12:11 GMT

Hi,

Please try adding one more component tmap in between both the connectors and for that column alone check for empty string if so then set null, something like
Row1. Field==''? null:row1.field.

Let me know if above works.
Thanks,
Ram

Re: Writing null values into database instead of empty strings

cterenzi — Mon, 02 Oct 2017 16:30:14 GMT

Don't use == to compare Strings. Use .equals() or in this case .isempty() instead.

Re: Writing null values into database instead of empty strings

HeinBloed — Wed, 04 Oct 2017 11:11:16 GMT

Thank you for your quick answers.

I had hoped there was a checkbox or something I missed.

But your solution will definitely work.

Re: Writing null values into database instead of empty strings

Anonymous — Wed, 04 Oct 2017 11:32:08 GMT

hello ,

i have one column for monile number and some user not inserted value in that column ..

so i wand to replace that value by dot '.'

Thank you

Re: Writing null values into database instead of empty strings

Anonymous — Fri, 08 Jun 2018 05:42:43 GMT

If you are using the suscription version, you can leverage Dynamic schema and do something like that:

Job OverviewFile extractFile SchemaExtract Dynamic SchematLog

The code in the tJavaRow is

Dynamic columns = input_row.dyn; //extract Dynamic
 
//looping on each columns of the row
for (int i = 0; i < columns.getColumnCount(); i++) {  
    DynamicMetadata columnMetadata = columns.getColumnMetadata(i); //extracting metadata 
   
//if empty then replace by null
    if(columnMetadata.getType().equals("id_String") && ((String)columns.getColumnValue(i)).isEmpty())
    {
    	columns.setColumnValue(i, null);
    }
}

//replacing the stream row by the new updated one
output_row.dyn = columns;

Re: Writing null values into database instead of empty strings

oballet — Thu, 09 Aug 2018 09:57:29 GMT

Hi,

it is also possible to use the "tConvertType" component with the "Set empty values to Null before converting" option between your csv file component and your database component.

It saves a lot of time to me, I hope it will be the same for you !

Regards