Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

One Star

Date format issue in tFileInputExcel from to TOS 4.1 to 5.3.1

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
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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

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

hi,
do you have any update?
Thank you
Mary
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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?
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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
One Star

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

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................
One Star

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

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 .
One Star

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

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

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Moderator

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

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

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

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

Hi mahadevank,
You'd better open a new topic for your current context issue.
Best regardsa
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

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

My expectation is i want to increase the context value for incremental load at each run. Here i attached my job process for understanding , i hope you understand my process.
In context variable i have assigned this value(Initial value)= 01/01/2000
In first main job iam filtering the data Whose input value is greater than context variable value.
In subjob i am getting the max (Date) from source to increase the context value at each run.
In tjavarow i am assigning this max (Date)value to context variable like Context variable=out.MaxDate

But the context variable is not getting the maxDate value from taggrow component
One Star

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

Hi any one can help to solve this problem..................
Here i creating a mapping for incremental load logic.
Here i created one context variable with some initial value is called Contex.Last_Date= 01/01/2000
MY Problem is...................
1. For First time load i used insert option in tmssqloutput for full load , here i got full rows from source correctly .
2.But in second load again it reads all the rows from source and its not filter the rows greater than contex.Last_Date, because the value of context variable is not changed in tfilter .

Please any one knows the solution, than kindly help me to solve this.
One Star

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

Hi any one can say for this............
I know how to use job compare and with the help of job compare we will compare two two different jobs or two version of the same job is possible.
But i can't understand what is the use of job compare in talend enterprise edition (what is the use of comparison)
One Star

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

Hi any one can say for this............
I know how to use job compare and with the help of job compare we will compare two two different jobs or two version of the same job is possible.
But i can't understand what is the use of job compare in talend enterprise edition (what is the use of comparison)
Community Manager

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

Hi mahadevank
2.But in second load again it reads all the rows from source and its not filter the rows greater than contex.Last_Date, because the value of context variable is not changed in tfilter .

The code expression used to access the input row on tJavaRow is:
context.last_date=input_row.inputColumnName;

Note that the new value of context variable is not permanent if it is stored in memory. As a workaround, you can store the new max date to a property file or database, read it and assign it to context variable at each run.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business