Excel Dates - is there an auto-magic way of loading Excel files

[Anonymous]

Hi,
having some problems with Excel and its dates format on Talend 4 using java jobs.
how should i do to import Excel files with java jobs.
thaks
Nicolas

[Anonymous]

bumping !!
any suggestions at all?
really stuck here
this is a sample of the data i get when reading various Excel files that need to be processed into DB:
this is by setting the schema as String and loading into Talend only - not trying to convert as Dates

|excelDates |counting|
|12-Jul-2010 |376 |
|23/05/2010 12:03:00|3 |
|15/04/2010 10:30:00|3 |
|28/04/2010 11:35:00|3 |
|29/05/2010 10:55:00|3 |
|25/05/2010 04:38:00|3 |
|6/15/10 17:05 |3 |
|21/05/2010 09:30:00|3 |

[Anonymous]

with a Perl job, you would just need to include the Date::Manip module and then in a tmap output table put this code in to "normalize" the dates:
Date::Manip: ateCalc( $row1 , "+ 0 Day");
Date::Manip is smart enough to interpret the different formats and return the date and time in a consistent format.

I'm not sure if there's a Java equivalent to this solution, maybe one of the Java guru's here can chime in.

[Anonymous]

Hi Nicolas,
Try this one http://www.talendforge.org/forum/viewtopic.php?pid=42809#p42809
Hope you could get tips on that thread.

[Anonymous]

thanks guys,
but the problem is rather more suttle
if i open the Excel file and 'format' the column with a custom format 'dd/mm/yyyy hh:mm:ss'
Talend will read this columns correctly and output:
19/07/2010 16:07:00
19/07/2010 17:31:00
19/07/2010 12:46:00
19/07/2010 18:38:00
19/07/2010 14:34:00
19/07/2010 17:55:00
19/07/2010 17:58:00
19/07/2010 21:09:00
19/07/2010 23:06:00
19/07/2010 19:01:00
19/07/2010 06:49:00
19/07/2010 04:48:00
19/07/2010 09:46:00

HOWEVER, if i format this with 'dd/MM/yyyy' in Excel, Talend now reads
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
19/07/2010
any chance of having REAL datetime value of this column?
thanks

[Anonymous]

Hi
If you don't know the format, read it as string on tFileInputExcel component, and then convert it to date if needed on tMap based on different date pattern.
Best regards
Shong

[Anonymous]

thanks shong
i am reading it as a string without any convertion/casting in Talend.
the formatting that i mentioned above is done in Excel.
this is why i am puzzled with this problem.
Talend seems to be reading the dates FORMATTED instead of the values held in the cells.
am i right in my assumption?
thanks

[Anonymous]

viewing Excel sheets with Excel can be misleading. To simplify the problem, try exporting the file to csv and examining the date format in the resulting file.

[Anonymous]

thanks John,
but i have these files uploaded to our system, and i am tying to find a way of processing it.
however, i can not open large number of files daily.
that is why i need to find a solution for converting dates.

[Anonymous]

hi
Go to advanced setting and check box 'Don't validate the cells'.
Best regards
Shong