Can't import Microsoft Access date field using tAccessInput

One Star

Can't import Microsoft Access date field using tAccessInput

I am very new to TOS.
I have a very simple table in Access 2003 with several fields. One of the fields is a Date field. This field has no special format. However, when I tried to use tAccessInput to import the data from the table, I received an exception caused by that date field:
xception in component tAccessInput_1
java.sql.SQLException: No data found
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLGetDataStringTimestamp(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getDataStringTimestamp(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcResultSet.getTimestamp(Unknown Source)
at talenddemosjava.testing_0_1.testing.tAccessInput_1Process(testing.java:645)
at talenddemosjava.testing_0_1.testing.runJobInTOS(testing.java:966)
at talenddemosjava.testing_0_1.testing.main(testing.java:860)
I have no idea why this is happening. I tried to change the data type of the date field in the schema and I got something like: 1984-12-05 00:00:00
I tried many date pattern possible such as "yyyy-MM-dd'T'HH:mm:ss". I constantly received the same exception.
Would you please help me to solve this problem?
Thank you very much.
Toan.
Community Manager

Re: Can't import Microsoft Access date field using tAccessInput

Hello
What's your data looks like in access table? Can you show me the schema of tAccessInput_1?
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Can't import Microsoft Access date field using tAccessInput

Thank you very much for replying. The table structure and schema are posted.
Community Manager

Re: Can't import Microsoft Access date field using tAccessInput

Hello
If the date looks like :1984-12-05 00:00:00, the date pattern should be set as: "yyyy-MM-dd HH:mm:ss"
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Can't import Microsoft Access date field using tAccessInput

I tried that already and same thing happned.
Community Manager

Re: Can't import Microsoft Access date field using tAccessInput

Hello
First, make sure you set the correct data source and table name.
Please show me a screenshot of your data.
Best rega
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Can't import Microsoft Access date field using tAccessInput

The data source and table name should be correct because when I got rid of that date field, everything runs just fine.
Community Manager

Re: Can't import Microsoft Access date field using tAccessInput

Hello
From your image, I see the date pattern looks like
"dd/MM/yyyy"
or
"dd/MM/yyyy HH:mm:ss"
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Can't import Microsoft Access date field using tAccessInput

I've tried everything
"dd/MM/yyyy HH:mm:ss" Length = 19
"MM/dd/yyyy HH:mm:ss" Length = 19
"dd/MM/yyyy" Length = 10
"MM/dd/yyyy" Length = 10
Nothing works. I don't know why a simple task is so hard.
Community Manager

Re: Can't import Microsoft Access date field using tAccessInput

Hello
Which version of TOS do you use?
Don't worry! As your said, it is just a simple job. Please send your data source to me via email. I will try..
Best regards
shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Can't import Microsoft Access date field using tAccessInput

My TOS is 4.0.0M1
One Star

Re: Can't import Microsoft Access date field using tAccessInput

Email sent. Did you have it? Please post your reply here. Thank you so much.
Community Manager

Re: Can't import Microsoft Access date field using tAccessInput

Hello
I test it on TOS3.2.3 and it works fine.
query:
"select Table1.SalesUnits,Table1.SalesDollar,Table1.SalesCost,Table1.CustID,Table1.StoreID,
Table1.ItemID,Table1.myDate
from Table1"

Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Can't import Microsoft Access date field using tAccessInput

I did exactly what you did and had no luck. Same exception appeared. Is there any chance you can test it on TOS 4.0.0 because it's the version that I can download.
One Star

Re: Can't import Microsoft Access date field using tAccessInput

The dates displayed on my computer is in "MM/dd/yyyy" format. So I use "MM/dd/yyyy" date pattern in Talend. But I still can't get it to work. Is there any chance this problem is caused by the 64bit operating system that I'm using. Moreover, the "Date pattern" in TOS should only define what date data should look like in TOS only. Is that correct? If I have to define the explicit "date pattern" to match each format of all the date fields from all the data sources, I wonder how many different jobs I have to create to handle just date fields.
One Star

Re: Can't import Microsoft Access date field using tAccessInput

I am having the same issues in TOS 4.0.0M3. I have several date fields in an Access table, some of which are formatted as "mm/dd/yyyy" and some that are formatted as short time ("hh:mm"). If I leave all the date fields out, my import runs fine. As soon as I include any of the date fields in the import process, it fails with the same error messages shown above. I am using Windows XP, so it not limited to just 64-bit systems.
One Star

Re: Can't import Microsoft Access date field using tAccessInput

Update: my import runs fine in TOS 3.2. There must be a bug in TOS 4.0 that causes dates in Access to not work.