topic Re: Unparseable date in Talend Studio

Unparseable date

JMArtin1594762559 — Fri, 15 Nov 2024 21:24:27 GMT

I have seen several similar threads but not quite the same as my issue.

My job was working successfully until the source file string date format changed from yyyy-MM-dd to MM/dd/yyyy.

Previously, this tMap Var Expression was working:

TalendDate.parseDate("yyyy-MM-dd",row1.EffectiveDate )

Now, if I change it to

TalendDate.parseDate("MM/dd/yyyy",row1.EffectiveDate )

I receive error:

Exception in component tMap_1 (TEST_FILE)

java.lang.NullPointerException

at java.text.SimpleDateFormat.parse(SimpleDateFormat.java:1439)

at java.text.DateFormat.parse(DateFormat.java:364)

at routines.TalendDate.parseDate(TalendDate.java:929)

at routines.TalendDate.parseDate(TalendDate.java:887)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.tFileList_1Process(TEST_FILE.java:1535)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.runJobInTOS(TEST_FILE.java:3480)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.main(TEST_FILE.java:3281)

[statistics] disconnected

But if I change it to

TalendDate.parseDate("MM -dd-yyyy",row1.EffectiveDate )

I receive error:

Exception in component tMap_1 (TEST_FILE)

java.lang.RuntimeException: java.text.ParseException: Unparseable date: "9/14/23"

at routines.TalendDate.parseDate(TalendDate.java:943)

at routines.TalendDate.parseDate(TalendDate.java:887)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.tFileList_1Process(TEST_FILE.java:1766)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.runJobInTOS(TEST_FILE.java:3356)

at tn_wi.t555_tn_fmap_attest_file_0_1.TEST_FILE.main(TEST_FILE.java:3106)

Caused by: java.text.ParseException: Unparseable date: "9/14/23"

at java.text.DateFormat.parse(DateFormat.java:366)

at routines.TalendDate.parseDate(TalendDate.java:929)

It seems Talend does not recognize the forward slash(/) but it does recognize the hypen (-).

Re: Unparseable date

Anonymous — Fri, 15 Sep 2023 02:59:00 GMT

Hello

"9/14/23" has the date pattern "MM/dd/yy", so change the expression to be:

TalendDate.parseDate("MM/dd/yy",row1.EffectiveDate )

About NPE, go to check if there exists null value in the column EffectiveDate.

if you want the output to be null when the input data is null, change the expression to be:

row1.EffectiveDate==null?null:TalendDate.parseDate("MM/dd/yy",row1.EffectiveDate )

Regards

Shong

Re: Unparseable date

RutMitchell — Fri, 15 Sep 2023 05:03:04 GMT

HY there i can see your post and i must say

Locale Considerations: Be aware that date formats can vary by locale. If your date string includes month or day names, ensure that the locale settings are appropriate.
Date Parsing Library: Use a dedicated Pay Military Star Card date parsing library or function provided by your programming language or framework. These libraries are designed to handle various date formats and edge cases.
Input Data Validation: Validate the input data before parsing to ensure it conforms to the expected format. This can help prevent errors caused by invalid or unexpected date strings.

Thanks and regards

TroyGuillen

Re: Unparseable date

JMArtin1594762559 — Fri, 15 Sep 2023 12:16:56 GMT

@Shicong Hong The string format in the Excel file is not "9/14/23". It was actually "9/14/2023".

I had manually tried changing the format inside of Excel from General to Date. That is what caused the year to truncate. Without altering the Excel file, the error message is:

xception in component tMap_1 (T555_TN_FMAP_ATTEST_FILE)

java.lang.RuntimeException: java.text.ParseException: Unparseable date: "7/1/2022"

at routines.TalendDate.parseDate(TalendDate.java:943)

at routines.TalendDate.parseDate(TalendDate.java:887)

at tn_wi.t555_tn_fmap_attest_file_0_1.T555_TN_FMAP_ATTEST_FILE.tFileList_1Process(T555_TN_FMAP_ATTEST_FILE.java:1516)

at tn_wi.t555_tn_fmap_attest_file_0_1.T555_TN_FMAP_ATTEST_FILE.runJobInTOS(T555_TN_FMAP_ATTEST_FILE.java:3213)

at tn_wi.t555_tn_fmap_attest_file_0_1.T555_TN_FMAP_ATTEST_FILE.main(T555_TN_FMAP_ATTEST_FILE.java:3014)

Caused by: java.text.ParseException: Unparseable date: "7/1/2022"

at java.text.DateFormat.parse(DateFormat.java:366)

at routines.TalendDate.parseDate(TalendDate.java:929)

Re: Unparseable date

JMArtin1594762559 — Fri, 15 Sep 2023 12:30:26 GMT

@Troy Guillen Are you referring to my Windows date and region settings? The short date does appear to be the same as the input data.

The expression was working before when the input date included hyphens instead of forward slashes. It appears to match the syntax given inside of Talend. Is this what you mean by date parsing library?

There error message appears to be failing on the "7/1/2022" value in the first row of the Excel file.

Re: Unparseable date

David_Beaty — Sun, 17 Sep 2023 11:28:58 GMT

Hi, to resolve problems where dates can change or a given column date format can be different per row, I’ve previously created a Java helper routine that you can pass in the textual value and a delimited string of date formats to try, in order.

something like:

ConvertDate(row1.EffectiveDate,”dd/mm/yyyy;yyyy/mm/dd”)

Re: Unparseable date

Anonymous — Mon, 18 Sep 2023 05:36:14 GMT

@Jason MArtin print the data on the console to see what's the data format is read from Excel file before tMap, and use the right format to parse the string date.

Re: Unparseable date

ThWabi — Mon, 18 Sep 2023 06:27:34 GMT

Hello JMArtin,

if your date data does not contain leading zeros for month (and/or day), you might try "M/d/yyyy".

Best regards,

Thomas