Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: couldn't convert string [2008/08/26 00:00:00.000] to a date using format [dd.MM.yyyy

  1. #1
    Join Date
    Sep 2008
    Posts
    6

    Default couldn't convert string [2008/08/26 00:00:00.000] to a date using format [dd.MM.yyyy

    Hi all,

    after updating to the latest version of spoon, I now have at least a more meaningfull errormessage.

    Erzeugungstag String : couldn't convert string [2008/08/26 00:00:00.000] to a date using format [dd.MM.yyyy]

    The variable is read from a csv file.

    What can I do to correct the error? I already played with precision and length, but I did not find a documentation on this fields.

    Best regards,

    Alex

  2. #2

    Default

    I have never used date conversion with CSV input, but did you try to give a format like yyyy/MM/dd ?
    Did you use the "Get Fields" button in the CSV input step? Which format and type were suggested?

    Christoph

  3. #3
    Join Date
    Sep 2008
    Posts
    6

    Default

    Hi Christoph,

    the row in the csv-file contains dates in the german form "dd.MM.yyyy", so I had to use this format in order to parse the text. This also seems to work because this step does not throw an error. But when I try to commit the data to the oracle-db, the above mentioned problem arises.

    Alltogether, my tests during the last days reveal, that handling dates is a big mess with spoon. For a good ETL tool it can't be that tricky to fetch simple dates from a file and write it to a database.

    Best regards,

    Alex

  4. #4

    Default

    Hi Alex,
    Quote Originally Posted by LosWochos View Post
    ... Erzeugungstag String : couldn't convert string [2008/08/26 00:00:00.000] to a date using format [dd.MM.yyyy] ...
    Quote Originally Posted by LosWochos View Post
    ... the row in the csv-file contains dates in the german form "dd.MM.yyyy" ...
    The error message tells me the string is not in dd.MM.yyyy form, but you say that. If you are right, the string is already converted into Date and re-converted back to String for the error message. (What locale do your Java/computer use?) Otherwise the error message should contain a german date, shouldn't it?

    Another way is to post a simplified example here. (Shortened input file and reduced transformation.)

    Christoph

  5. #5
    Join Date
    Sep 2008
    Posts
    6

    Default

    Hi Christopj,

    as I said: From my point of view, the data from the cs-file is converted to a date-type successfully in the csv-step already. Why spoon wants to convert the date back to a german format for inserting it into the oracle db is a complete miracle to me. I have a german Windows system running on my pc.

    Attached you can find my process which consist of a job and 2 embedded transformations. Extract it to c:\ and edit the db-settings in the db-commit-step in the csv-transformation. I hope this helps you to understand the problem.

    Best regards,

    Alex
    Attached Files Attached Files

  6. #6

    Default Insert/Update step and Date with Oracle Database

    Hi Alex,
    your problem is not CSV input. It seems to me the oracle driver cause the problem - or indeed Kettle.
    You use an "insert/update step". To detect whether Kettle has to do an insert or an update it has to read the record. If none is found it will insert else update that record. So there is a READ from your Oracle. But the conversion produces the error message.
    You have 3 possibilities:
    1. If possible use an insert only step instead the combined Insert/Update.
    2. Try to use latest nightly build and test if error still exists.
    3. Also try latest Oracle driver.
    If all that fails look for a JIRA and if necessary report a bug.
    (Maybe Matt knows if there is/was a bug with the "Insert/Update step".)

    <off-topic>
    Beside this: Your transformation is - let me say not really straight forward. From "Select value:Fix types" you copy all rows to steps for every facility. If you use a "Row Normalizer" you could do that on the fly and with only one path for all facilities. Doing so you have to maintain only one path instead of up to 35.
    But yeah, that's not the point here...
    </off-topic>

    Christoph

  7. #7
    Join Date
    Sep 2008
    Posts
    6

    Default

    Hi Christoph,

    1) It is not possible, to use a simple insert, because the job may run several times with the same data and I do not want to have it inthe database multiple times.
    2) I use the ocijdbc10.dll from the oracle 10.2 driver. I do not have access to a newer version.

    It seems to me, that this is a big bug in the kettle/spoon software. It's a pity, spoon really looked good for my company. Now I have to look for another ETL-tool.

    Best regards,

    Alex

  8. #8

    Default

    surely if it were a big bug, it would have been found before now ?
    This is a signature.... everyone gets it.

    Join the Unofficial Pentaho IRC channel on freenode.
    Server: chat.freenode.net Channel: ##pentaho

    Please try and make an effort and search the wiki and forums before posting!
    Checkout the Saiku, the future of Open Source Interactive OLAP(http://analytical-labs.com)

    http://mattlittle.files.wordpress.co...-bananaman.jpg

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.