Hitachi Vantara Pentaho Community Forums
Results 1 to 14 of 14

Thread: Acces input Error, have no idea what's the problem

  1. #1
    Join Date
    Sep 2007
    Posts
    19

    Default Acces input Error, have no idea what's the problem

    I have the following error appearing...

    2007/09/26 13:50:08 - Access Input.0 - Opening file: file:///D:/db1.mdb
    2007/09/26 13:50:09 - Access Input.0 - ERROR (version 2.5.1, build 2 from 2007/08/24 16:29:34) : Couldn't open file #0 : file:///D:/db1.mdb --> be.ibridge.kettle.core.exception.KettleValueException:
    2007/09/26 13:50:09 - Access Input.0 - ERROR (version 2.5.1, build 2 from 2007/08/24 16:29:34) : Couldn't convert string to number java.text.ParseException: Unparseable number: "Z43107"


    Anybody knows what's the problem there?

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Are you reading strings as integers?

    Regards,
    Sven

  3. #3
    Join Date
    Sep 2007
    Posts
    19

    Default

    NO.
    I created a transformation from Access to Postgres.
    Then, extract DDL and execute the DDL.
    Next, run the transformation.
    The following error happens.

    This information appears for my previous question (only 495 rows can be written).

    Thanks a lot.
    Hannah

    Quote Originally Posted by sboden View Post
    Are you reading strings as integers?

    Regards,
    Sven

  4. #4

    Default

    Hi,
    It seems that you defined the column that contain "Z43107" as integer.
    PDI raise an exception because this value is visibly not a number.
    Try to convert the column into string.

    Rgds

    Samatar

  5. #5

    Default

    When you click on "Get fields" in Access Input step, PDI get columns and try to set the type by evaluating the first value for each column.
    If the first value is for ex 124 and the next one abc, PDI will set Type as Integer...
    and you will get trouble at line 2 !

    Keep in mind that you can change Column type in access input step (see Fields Tab)

    Rgds

    Samatar

  6. #6
    Join Date
    Sep 2007
    Posts
    19

    Default

    Hi, Samatar,

    You saved me!!!
    Thanks a lot.
    I would have a try soon.

    Hannah
    Quote Originally Posted by shassan2 View Post
    Hi,
    It seems that you defined the column that contain "Z43107" as integer.
    PDI raise an exception because this value is visibly not a number.
    Try to convert the column into string.

    Rgds

    Samatar

  7. #7
    Join Date
    Sep 2007
    Posts
    19

    Default

    It works when I changed two column type to String (from Number)

    But I am puzzled on one thing.
    Jackess could retrieve the schema information of Access file, why does Kettle set the type by evaluating the first value of each column?
    It seems not very reasonable.....

    are the field types in Excel input determined using this way?
    If yes, it seems not very stable....

    Any comments?

    Hannah

    Quote Originally Posted by shassan2 View Post
    When you click on "Get fields" in Access Input step, PDI get columns and try to set the type by evaluating the first value for each column.
    If the first value is for ex 124 and the next one abc, PDI will set Type as Integer...
    and you will get trouble at line 2 !

    Keep in mind that you can change Column type in access input step (see Fields Tab)

    Rgds

    Samatar

  8. #8
    Join Date
    May 2006
    Posts
    4,882

    Default

    Access Input is an "odd duck" ... The database steps normally take the metadata directly of the database. For text input there's no meta-data so Kettle will try to discover it (if you have an empty column for all lines it's currently interpreted as a date e.g. by default). Excel use the ones it knows about... strings, dates, boolean, numbers.

    Regards,
    Sven

  9. #9
    Join Date
    Sep 2007
    Posts
    19

    Default

    Thanks a lot, Sven!

    Quote Originally Posted by sboden View Post
    For text input there's no meta-data so Kettle will try to discover it (if you have an empty column for all lines it's currently interpreted as a date e.g. by default).
    But Access input is not text input....

    Quote Originally Posted by sboden View Post
    Excel use the ones it knows about... strings, dates, boolean, numbers.
    I mean does Excel determines the field type according to its first row's values?

  10. #10
    Join Date
    May 2006
    Posts
    4,882

    Default

    But Access input is not text input....
    Access input is an odd duck.

    I mean does Excel determines the field type according to its first row's values?
    No, text input selects e.g. a number of rows to determine the type (which you can entery yourself).

    Regards,
    Sven

  11. #11
    Join Date
    Sep 2007
    Posts
    19

    Default

    Many thanks to you, Sven!
    Hannah

  12. #12

    Default

    I posted the following CRQ :

    http://jira.pentaho.org/browse/PDI-239

    In fact, it's very easy to retrieve schema information from mdb file.

    Thanks

    Samatar

  13. #13
    Join Date
    Sep 2007
    Posts
    19

    Default

    Hi, Samatar,

    Thanks a lot.
    Yes, we wrote some codes to retrieve schema information from mdb file.

    I just think that Kettle would be better if it provides this functionality.
    Surely, it has been very good already/
    What's more, the reply in the forum is very useful.

    Thanks.

    Hannah

    Quote Originally Posted by shassan2 View Post
    I posted the following CRQ :

    http://jira.pentaho.org/browse/PDI-239

    In fact, it's very easy to retrieve schema information from mdb file.

    Thanks

    Samatar

  14. #14

    Default

    I closed the CRQ.
    Changes are the next release 2.5.2 and 3.0

    Rgds

    Samatar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.