Capture Invalid Date type Values in tMap reject flow

[Anonymous]

I have a Talend Job which reads data from Excel File (.xlsx) via tFileInputExcel component.

There is a column Payment_Date which I have defined as type String in the above component. Reason Excel has 3 rows and for all 3 rows value in Payment_Date column are different as shown below

Payment_Date
7/1/2019
NULL
4568

First row is a valid Date

In the second row string "NULL" is not equivalent to null so I am using tReplace component for replacing string "NULL" to null

Post that I am using tJavaRow to capture input data and pass it via output_row as below

Below code says if the value from Input is null then pass null in output else if value is a valid date then pass the valid date to the output

output_row.Payment_Date = Relational.ISNULL(input_row.Payment_Date) ? null : TalendDate.parseDateLocale("EEE MMM d HH:mm:ss zzz yyyy", input_row.Payment_Date, "en");

And that in tMap, row6.Payment_Date  (In out-flow of tMap, Payment_Date column is of Date type)

Above approach is working for Valid Date and NULL value but for Invalid Date i.e. 4568 I want that row to be captured in reject flow. But instead, my job fails on tJavaRow with error java.text.ParseException: Unparseable date: "4568"

Job flow:

tFileInputExcel  ----main--->tReplace --main---->tJavaRow --main-->tMap---main-->tLogRow

[David_Beaty]

Hi,

You'll probably want to use the TalendDate.isDate() function. Have a look at this link.

Thanks

[Anonymous]

I have changed my job design now I am not using tJavaRow. Instead, I am directly connecting tReplace --main--tMap 

@dsoulalioux I am already using isDate function to validate if the date from Input flow is a valid date but somehow it is considering all my input dates to be invalid, even for the valid ones like 7/1/2019 and hence now all my Payment_Dates which I had mentioned in my previous post are going to Reject flow.

Expression in Var (To validate if the date is a valid date or not):

TalendDate.isDate(row6.Payment_Date,"dd/MM/yyyy",true)

OR

TalendDate.isDate(row6.Payment_Date,"E MMM dd HH:mm:ss Z yyyy",true)

Where, row6.Payment_Date from Input is String type as isDate requires 3 parameters (String,String,Boolean)

Tried both, but still same output all rows in reject flow.

Expression in out3 flow, if Var (is_Date is true)

Relational.ISNULL(row6.Payment_Date) ? null :TalendDate.parseDateLocale("EEE MMM d HH:mm:ss zzz yyyy", row6.Payment_Date, "en") 

In the above flow, only vaid and null dates should pass

Expression in reject flow if Var (is_Date is false)

row6.Payment_Date

In the above flow, invalid dates should be as it is passed to reject flow 

tMap

Job

[manodwhb]

@Vikas ,check the below example.

sourcedata

[Anonymous]

@manodwhb , I made tMap changes in my job similar to your job but still, all 3 rows are going in reject flow.

[manodwhb]

@Vikas ,can you just correct 7/1/2019 to 07/01/2019 ,so you will get one record .

[manodwhb]

@Vikas ,either you need to correct the source data or you need to change the format of TalendDate.isDate(row2.newColumn,"d/M/yyyy") from "dd/MM/yyyy" to "d/M/yyyy" .

[Anonymous]

@manodwhb , Thank you so much for your help. But I cannot change/correct the value in Source File and after changing isDate format to TalendDate.isDate(row2.newColumn,"d/M/yyyy")
still all rows in reject flow

[manodwhb]

@Vikas ,I am sure your source data may be other than 7/9/2019 because of that it is going to reject only,you need to identify the source data. I have tested the above scenarios and working. 

[Anonymous]

I m pretty sure Source Data is in 3 forms

7/1/2019 (valid)
null (valid)
4567 (invalid)

One more thing I wanted to point out I created new job same as ur's it worked partially. The reason I am saying partially is because I wanted row which has null value to in Valid scenario not in reject. Any idea how can I get that null value in Valid flow ?