topic Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1 in Talend Studio

Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Thu, 11 Jul 2013 14:00:23 GMT

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

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Fri, 12 Jul 2013 04:27:14 GMT

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

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Tue, 16 Jul 2013 14:05:02 GMT

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

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Wed, 17 Jul 2013 04:46:30 GMT

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

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Thu, 25 Jul 2013 13:28:02 GMT

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

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Thu, 22 Aug 2013 15:57:56 GMT

hi,
do you have any update?
Thank you
Mary

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Fri, 23 Aug 2013 08:07:30 GMT

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

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Fri, 10 Jan 2014 07:46:48 GMT

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.

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Fri, 10 Jan 2014 07:59:56 GMT

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

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Fri, 10 Jan 2014 11:47:55 GMT

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.

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Mon, 13 Jan 2014 03:34:29 GMT

Hi,
If I understand you very well, you need parse "String" to "Date". If so, use the expression in tMap

TalendDate.parseDate("dd/MM/yyyy HH:mm:ss",row1.Value)

.
Best regards
Sabrina

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Fri, 17 Jan 2014 05:57:35 GMT

Hi any one can help me

Can you tell me how can I convert the date data of "14/09/2001" from MS Access to mssqlserver date format ("yyyy-mm-dd") as "2001-09-14" by using tMap function in Talend Open Studio, please?

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Fri, 17 Jan 2014 06:42:04 GMT

Hi,
If you input data type is "Date", you can just change schema setting using tMap.
See my screenshot.
Feel free let me know if I misunderstanding your current situation.
Best regards
Sabrina

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Mon, 27 Jan 2014 12:35:35 GMT

Hi can any one help me to solve this problem..........Hi , i have a source in excel with the following data
id name dateofbirth salary hiredate
1 xays 24/01/1994 100 05/07/2012
2 xyz 22/10/1991 200 04/02/2012
3 kjsdj 04/02/1990 300 14/01/2008
4 dfdkl 06/06/1989 400 16/02/2010
here rowno1 24/01/1994,dateofbirth is displaying in excel as a general format
and rowno2 22/10/1994 dateofbirth is displaying in excel as a general format
and rowno 4 16/02/2010 hiredate is displaying in excel as a general format. so while iam loading this from excel to target iam getting the following error
The cell format is not date in row1 date of birth
The cell format is not date in row2 date of birth
The cell format is not date in row4 hire date Because the above mentioned rows are in general format in excel sheet.
my expetation is i want to get all the rows from source to target.
But if i used parsedate in tmap the complete date value has been changed like.....
row1 date is 24/01/1994 as changed to like this 01/12/95 and
row2. 22/10/1991 is changed to 10/10/19992
can you show me the screen shot please for this solution

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Thu, 30 Jan 2014 06:41:10 GMT

Hi sabrina,

can you provide me the steps to install the talend enterprise data integration part on windows 7, because now iam going to use talend enterprise data integration.
Iam confusing with talend server and designer tool................

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Wed, 05 Feb 2014 07:04:42 GMT

Hi anyone can help me how to solve this problem...........

I have a date in my Excel file is 01-JAN-06. 01 represents the day of the week, JAN represents the month and 06 represents the year. The Excel file also contains date values of 00-XXX-00 which represent no date. For example a column containing last purchase date data would look like this:"DateOfLastOrder"
"01-JAN-06"
"02-JAN-06"
"00-XXX-00"
"03-DEC-05"The value of 00-XXX-00 means that there is no purchase date.I want to bring these columns into my table and replace the 00-XXX-00 values with a NULL.
The table of tmssqloutput Data Type is datetime.
This is the error iam getting: only the date between january 1 1753 and december 31 9999 are accepted .

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Thu, 13 Feb 2014 05:46:12 GMT

Hi, any one can provide me one snapshot for joblet with example, because i am confusing with schema while creating joblet in talend enterprise edition.
here i am doing concatinating fname+lastname=full name in variable tmap
Here i have used schema for emp table for joblet and how can i use this for another job .............
In another job i have customer table.............

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Thu, 13 Feb 2014 07:04:04 GMT

Hi,

can you provide me the steps to install the talend enterprise data integration part on windows 7, because now iam going to use talend enterprise data integration.
Iam confusing with talend server and designer tool................

Sorry for delay.
Here is an installation document for talend enterprise data integration, TalendHelpCenter:Talend Enterprise Data Integration:Installation and Upgrade Guide, could you please take a look at it?
Feel free let me know your difficulty and issue on it.
Best regards
Sabrina

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Thu, 13 Feb 2014 07:14:52 GMT

Hi,

Hi can any one help me to solve this problem..........Hi , i have a source in excel with the following data
id name dateofbirth salary hiredate
1 xays 24/01/1994 100 05/07/2012
2 xyz 22/10/1991 200 04/02/2012
3 kjsdj 04/02/1990 300 14/01/2008
4 dfdkl 06/06/1989 400 16/02/2010
here rowno1 24/01/1994,dateofbirth is displaying in excel as a general format
and rowno2 22/10/1994 dateofbirth is displaying in excel as a general format
and rowno 4 16/02/2010 hiredate is displaying in excel as a general format. so while iam loading this from excel to target iam getting the following error
The cell format is not date in row1 date of birth
The cell format is not date in row2 date of birth
The cell format is not date in row4 hire date Because the above mentioned rows are in general format in excel sheet.
my expetation is i want to get all the rows from source to target.
But if i used parsedate in tmap the complete date value has been changed like.....
row1 date is 24/01/1994 as changed to like this 01/12/95 and
row2. 22/10/1991 is changed to 10/10/19992
can you show me the screen shot please for this solution

Is the data type in excel "Date" or "String"? If later, do you want to parse "String" to "Date" into target DB?(The method: TalendDate.parseDate("dd/MM/yyyy ",row.dateofbirth)).
Would you mind sharing your tMap editor screenshot into forum so that we can get your situation more precisely.
Best regards
Sabrina

Re: Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

Anonymous — Tue, 11 Mar 2014 06:18:25 GMT

Hi any one know how to set max value to the context variable for incremental load at each run..................and how to increase this value for each run.
here i am using incremental load based on date field, so i used some date value for context variable(initially) for the first run. Based on this value iam filtering but i dont know how to increase this value for the next run .....
If any one knows ,than kindly provide me the solution.