topic Re: Date Column null validation in Talend Studio

Date Column null validation

Anonymous — Wed, 24 May 2017 11:29:52 GMT

Hi,

Can anyone say how to do validation for the below requirement?

Loading from excel to Oracle table.

In excel If date column is null or empty, I want to insert sys date into the oracle table.

Excel ---->tmap---> OracleOutput

in tmap source date column is Date type and Target date column is also Date type.

Anonymous — Wed, 24 May 2017 11:51:23 GMT

Hi All,

row1.CREATION_DATE == null? TalendDate.getCurrentDate(): row1.CREATION_DATE

this code is working fine for date type to date type.

But If I want to check empty it is not working:

row1.CREATION_DATE == " "? TalendDate.getCurrentDate(): row1.CREATION_DATE

Is it possible to check for empty?

Anonymous — Wed, 24 May 2017 11:54:57 GMT

This is the simplest way to handle nulls assuming you are getting dates in the proper format.

input_row.dateColumn==null||input_row.dateColumn.toString().trim().length()==0?TalendDate.getCurrentDate():input_row.dateColumn

brama — Wed, 24 May 2017 12:02:47 GMT

In tMap use like below. Try either in variables or in the output column derivation.

DateCol==null?TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", TalendDate.getDate("yyyy-MM-dd HH:mm:ss"))ateCol

DateCol==""?TalendDate.parseDate("yyyy-MM-dd HH:mm:ss", TalendDate.getDate("yyyy-MM-dd HH:mm:ss"))ateCol

Thanks

SachinD — Wed, 24 May 2017 12:14:15 GMT

Please add Variable in tMap

(row14.Date.equals("null") || row14.Date.equals(""))? TalendDate.getDate("yyyy-MM-dd") : row14.Date

Anonymous — Wed, 24 May 2017 12:29:43 GMT

Hi,

@ArvinRapt : Thank you! your method is working fine.

@rchinta and @SachinD as my data type for both source and target is DATE data type so your method is not working, maybe it works for string to string

Thanks to all.