Writing null values into database instead of empty strings

Highlighted
Four Stars

Writing null values into database instead of empty strings

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

 

Schema.JPG

 

Screenshot.JPG


Accepted Solutions
Six Stars

Re: Writing null values into database instead of empty strings

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

View solution in original post


All Replies
Six Stars

Re: Writing null values into database instead of empty strings

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

View solution in original post

Ten Stars

Re: Writing null values into database instead of empty strings

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

Re: Writing null values into database instead of empty strings

Thank you for your quick answers.

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

 

But your solution will definitely work.

Six Stars

Re:how to replace null value by '.'

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

Seven Stars

Re: Writing null values into database instead of empty strings

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

 

JobOverview.PNGJob OverviewFlatFile.PNGFile extractDynamic.PNGFile SchemaSchema_DYN.PNGExtract Dynamic SchematLog.PNGtLog

 

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;

 

 

Four Stars

Re: Writing null values into database instead of empty strings

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog