topic Re: [resolved] Different date formats in Excel Source in Talend Studio

[resolved] Different date formats in Excel Source

Bhagwati1 — Sun, 28 Aug 2016 13:46:55 GMT

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

Re: [resolved] Different date formats in Excel Source

Anonymous — Mon, 29 Aug 2016 02:17:40 GMT

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

Re: [resolved] Different date formats in Excel Source

Bhagwati1 — Tue, 30 Aug 2016 23:26:20 GMT

Thanks Shong!!!!

Re: [resolved] Different date formats in Excel Source

Anonymous — Tue, 23 Jul 2019 06:48:57 GMT

Hii,

i've tried the exact same job .but it didn't work.its giving me Error as length cannot be resolved or it is not field.please help me out.

Date_task.PNG