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]

thanks for taking the time shong,
at first i though you were talking about a newer version of Talend then the one i have installed.
but maybe i have come across a bit of a bug on the UI
when i add my Excel connection, it does not show the 'Don't validate the cells' on the Advanced Tab.
it seems that when create an Excel input from the repository something does not work correctly.
*BUT* if i drag an excel input from the pallete it shows option on the advanced tab.
after i clicked every possible thing on this component - it seems that if you click on the 'use excel 2007' the un-click it.
it resets the Advanced Tab and it then shows all options as expenceted.
please see screenshot..
i will test the Excel conversion and report back
thanks,
Nicolas

[Anonymous]

Hi
I am using TIS/TOS402. You can try the latest version.
Best regards
Shong

[Anonymous]

hi Shong,
i have used:
TOS 4.0.2 r43696
TOS 4.1.0.M2 r45078
on windows 7 x64
>java -version
java version "1.6.0_20"
Java(TM) SE Runtime Environment (build 1.6.0_20-b02)
Java HotSpot(TM) 64-Bit Server VM (build 16.3-b01, mixed mode)
in both installations the problem appears.
however, if you close the installation, this problem disapears on the existing Excel connection - BUT any new connection wil have the same problem.
with regards,
Nicolas

[Anonymous]

problem persists

i have tried to go advanced setting and check box 'Don't validate the cells'
but it still gives me wrong dates.
is there anything that i can do?
run a VBA in VB.NET - it is either that or ditching the whole Talend project and restarting with SSIS.
sorry for keeping asking this but i am really stuck with this problem.
many thanks,
Nicolas


.--------------------------------------------+---------------------+---------.
| output |
|=-------------------------------------------+---------------------+--------=|
|filename |Arrival_Date_and_Time|myArrival|
|=-------------------------------------------+---------------------+--------=|
|Daily_Report_30_July_-_1_August_2010.xls|8/1/10 9:45 |null |
|Daily_Report_30_July_-_1_August_2010.xls|8/1/10 9:49 |null |
|Daily_Report_30_July_-_1_August_2010.xls|8/1/10 9:57 |null |
|Daily_Report_3_August_2010.xls |null |null |
|Daily_Report_3_August_2010.xls |03-Aug-2010 |null |
|Daily_Report_4_August_2010.xls |null |null |
|Daily_Report_4_August_2010.xls |04-Aug-2010 |null |

[Anonymous]

anyone??

[Anonymous]

Well, it must be said that Excel is not designed for consistency like a database, so if possible must be avoided as a data source for data processing because it usually a mess...
Anyway you can try accepting whatever Talend is giving to you and then parse it to a pure Date object with some custom code that guess the date string arrangement
....and.... if you want more help you should post the source excel file that you are using.

[jkrfs]

I'm having the same issue. It seems that when the excel file is saved in Open Office (Linux Ubuntu) it saves the date format as
dd MM yy hh:mm:ss
however, if the excel file is saved in Windows, it saves it as
M/dd/yy
or
MM/dd/yy
not being consistent at all.
Any suggestions on fixing this?

[Anonymous]

dont use excel to store or transport data. If you do, expect some pain. If possible, get your data in CSV format-- usually you just have to beg the accountants not to save in xls format

if you have no choice, start by poking yourself in the eye. it will make the pain of dealing with excel seem less by comparison.
That said, The best approach is probably to read the date fields as strings, make them more consistent with a tJavaRow and then convert to Date.
you'll probably have to write some ugly logic to make guesses about what the format should be-- such as if( theDate.indexOf("/") ) to see if you have the "linux format" or the windows one. wont be pretty-- but working with excel rarely is

[jkrfs]

Thank you for your reply, and everything you said pretty much hit the nail in the head in my situation, and how I decided to resolve it. Writing the logic in code as oppose to reading it in the schema.
Unfortunately, in the finance world they sure love their excel...
Cheers!

[_AnonymousUser]

thanks for taking the time shong,
at first i though you were talking about a newer version of Talend then the one i have installed.
but maybe i have come across a bit of a bug on the UI
when i add my Excel connection, it does not show the 'Don't validate the cells' on the Advanced Tab.
it seems that when create an Excel input from the repository something does not work correctly.
*BUT* if i drag an excel input from the pallete it shows option on the advanced tab.
after i clicked every possible thing on this component - it seems that if you click on the 'use excel 2007' the un-click it.
it resets the Advanced Tab and it then shows all options as expenceted.
please see screenshot..
i will test the Excel conversion and report back
thanks,
Nicolas

check this vba samples
http://vb.net-informations.com/excel-2007/vb.net_excel_2007_tutorials.htm
Hope it will help you.
wills.