Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

[Anonymous]

Hi all
I´ve update Talend Open Studio for Data Integration from 4.1 to 5.3.1 version and
unfortunately I'm finding big changes reading Date fields in Excel files with the tFileInputExcel component.
I´m reading with the tFileList component different Excel files from different countries (with different date format -> ex: MM-dd-YYYY or dd-MM-YYYY or dd/MM/YY),
with the 4.1 version the Date fields was correctly parsed using sometime "isDate" and "parseDate" function but now I found a lot of issues.
It seem that the date content is read in a different way and for this reason the checks I use in the tMap now are not enough.

Do you know why this happen if the source files and the job are not changed?
Do you have any suggestion?
Thanks in advance
mary

[Anonymous]

Hi,

with the 4.1 version the Date fields was correctly parsed using sometime "isDate" and "parseDate" function but now I found a lot of issues.
It seem that the date content is read in a different way and for this reason the checks I use in the tMap now are not enough.

Is there any error in your 5.3.1 studio for your existed job? Is the actual result in 5.3.1 not same as the one in 4.1 version?
Could you please give us more information about your situation. Screenshot will be preferred.
Best regards
Sabrina

[Anonymous]

Hi Sabrina,
thanks for your quick response,
yes in the 4,1 version these excel files were ok but now I have different issues.
The server where now I´m running TOs 5,3 is a new one, do you think that can be some server settings related to formats etc..?
This is an exception that now we have.

Warning: External sheet record for Biff 7 not supported
Warning: Usage of a local non-builtin name
Exception in component tMap_1
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "12/1/11"
at routines.TalendDate.parseDateLocale(TalendDate.java:868)
at budget_comparison.imp_usa_0_1.IMP_USA.tFileInputExcel_2Process(IMP_USA.java:2348)
at budget_comparison.imp_usa_0_1.IMP_USA.tFileList_1Process(IMP_USA.java:5019)
at budget_comparison.imp_usa_0_1.IMP_USA.tMSSqlRow_1Process(IMP_USA.java:5468)
at budget_comparison.imp_usa_0_1.IMP_USA.runJobInTOS(IMP_USA.java:5658)
at budget_comparison.imp_usa_0_1.IMP_USA.main(IMP_USA.java:5524)
Caused by: java.text.ParseException: Unparseable date: "12/1/11"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDateLocale(TalendDate.java:866)
... 5 more
Thanks a lot
Mary

[Anonymous]

Hi,

The server where now I´m running TOs 5,3 is a new one, do you think that can be some server settings related to formats etc..?

The server setting don't related to format.
I suspect due to product update, there are more change and improvement in studio and component.
Could you please upload your tMap editor screenshot into forum so that we will try to fix this issue on your new studio Talend open studio 5.3.
Best regards
Sabrina

[Anonymous]

Hi,
please, see the image attached.
The job and the excel are always the same.
Thank you
Mary

[Anonymous]

hi,
do you have any update?
Thank you
Mary

[Anonymous]

Hi,

java.lang.RuntimeException: java.text.ParseException: Unparseable date: "java.lang.RuntimeException: java.text.ParseException: Unparseable date: "12/1/11""

Sorry for the delay.
Maybe it is a new data(12/1/11) for your input.(It is OK in 4.1 but not 5.3.1)
From your screenshot, the data type is dd/MM/yy which should be 12/01/11. You didn't set an appropriate one for your input (12/1/11).
That should be dd/M/yy.
Best regards
Sabrina

[Anonymous]

Hi, i have a source in excel like date format is MM-dd-yyyy and dd-MM-yyyy, while loading the source i got this error.
the cell format is not date in ...........?
How to solve this...............please help me.

[Anonymous]

Hi mahadevank,
What your input value looks like? And your expected result? Please elaborate your case with an example with input and expected output values.
Best regards
Sabrina

[Anonymous]

Hi, Thanks for your response,
I have a source in excel like this in different date format
id name date_of Birth
1 dsh 12/25/1994
3 hgsd 24/02/1998
2 dfjj 02/04/1990 Now i want to sort the data based on id column and load all the rows of data into a target table with asc or desc order. But here all the rows of date_ofBirth date format is different.

here i got the error the cell format is not in date .............. How to solve this.