Five Stars

[resolved] Different date formats in Excel Source

Hello Guys,
I am loading data from multiple excels into a database table.
The date format in excels is not consistent.
in some of the excels the date format is dd/MM/yyyy and below format works.
TalendDate.parseDate("yyyy-MM-dd",
TalendDate.formatDate("yyyy-MM-dd",
TalendDate.parseDate("dd/MM/yyyy",row2.Rec_Dt))) 
but there are some excels in which date format is dd/MM/yy and wrong date is inserted into the destination table.below is the example.
23/12/15 is inserted as 0015-12-23

Could you please help?
Thanks Bhagwati
1 ACCEPTED SOLUTION

Accepted Solutions
Community Manager

Re: [resolved] Different date formats in Excel Source

Hi 
Read the column as a string, populate the length of the string and then parse the string to a date using different expression based on the length, eg:
tFileinputExcel--main--tMap--out1....
on tMap:
row1.columnName.length==10?TalendDate.parseDate("dd/MM/yyyy",row1.columnName):TalendDate.parseDate("dd/MM/yy",row1.columnName)
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
2 REPLIES
Community Manager

Re: [resolved] Different date formats in Excel Source

Hi 
Read the column as a string, populate the length of the string and then parse the string to a date using different expression based on the length, eg:
tFileinputExcel--main--tMap--out1....
on tMap:
row1.columnName.length==10?TalendDate.parseDate("dd/MM/yyyy",row1.columnName):TalendDate.parseDate("dd/MM/yy",row1.columnName)
Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: [resolved] Different date formats in Excel Source

Thanks Shong!!!!