topic Re: Capture Invalid Date type Values in tMap reject flow in Talend Studio

Capture Invalid Date type Values in tMap reject flow

Anonymous — Thu, 05 Sep 2019 09:53:52 GMT

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

Re: Capture Invalid Date type Values in tMap reject flow

David_Beaty — Thu, 05 Sep 2019 13:00:18 GMT

Hi,

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

Thanks

Re: Capture Invalid Date type Values in tMap reject flow

Anonymous — Thu, 05 Sep 2019 13:34:44 GMT

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

Re: Capture Invalid Date type Values in tMap reject flow

manodwhb — Thu, 05 Sep 2019 13:55:13 GMT

@Vikas ,check the below example.

sourcedata

Re: Capture Invalid Date type Values in tMap reject flow

Anonymous — Thu, 05 Sep 2019 14:21:03 GMT

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

Re: Capture Invalid Date type Values in tMap reject flow

manodwhb — Thu, 05 Sep 2019 14:28:28 GMT

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

Re: Capture Invalid Date type Values in tMap reject flow

manodwhb — Thu, 05 Sep 2019 14:31:06 GMT

@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" .

Re: Capture Invalid Date type Values in tMap reject flow

Anonymous — Thu, 05 Sep 2019 15:13:26 GMT

@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

Re: Capture Invalid Date type Values in tMap reject flow

manodwhb — Thu, 05 Sep 2019 15:17:57 GMT

@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.

Re: Capture Invalid Date type Values in tMap reject flow

Anonymous — Thu, 05 Sep 2019 17:40:06 GMT

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 ?

Re: Capture Invalid Date type Values in tMap reject flow

Anonymous — Sat, 07 Sep 2019 10:41:10 GMT

Finally made it work. Thanks @manodwhb For your input. But the issue was not with specifying the correct date format in Source Data but in Expressions and also handling of null value as Valid scenario.

Sharing the screenshot of updated settings so that it might help someone with a similar type of issue.

Expression in Var:

(Relational.ISNULL(row3.Payment_Date) || (!Relational.ISNULL(row3.Payment_Date)) && TalendDate.isDate(row3.Payment_Date,"M/d/yyyy",true) )

Expression in ValidDates:

Relational.ISNULL(row3.Payment_Date) ? null :TalendDate.parseDateLocale("M/d/yyyy", row3.Payment_Date, "en")

Job_OutputUpdated tMap