Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Cannot parse date in string to date format

  1. #1

    Default Cannot parse date in string to date format

    Hi,

    My transformation is reading data from an Apache Tomcat logfile and process it with a regex step so that I get a string column with a value like "02/Mar/2014:10:10:53 +0100"
    This value is then processed by a select value step to change the meta-data to Date format with the format "dd/MMM/yyyy:HH:mm:ss"

    This has worked pretty well for a few month but since a few weeks I cannot process it anymore. It now always returns

    Code:
    2014/03/24 18:37:13 - Select values.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
    2014/03/24 18:37:13 - Select values.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 
    2014/03/24 18:37:13 - Select values.0 - DateTime String : couldn't convert string [02/Mar/2014:10:10:53 +0100] to a date using format [dd/MMM/yyyy:HH:mm:ss] on offset location 3
    2014/03/24 18:37:13 - Select values.0 - Unparseable date: "02/Mar/2014:10:10:53 +0100"
    2014/03/24 18:37:13 - Select values.0 - 
    2014/03/24 18:37:13 - Select values.0 - 	at org.pentaho.di.core.row.value.ValueMetaBase.convertStringToDate(ValueMetaBase.java:675)
    2014/03/24 18:37:13 - Select values.0 - 	at org.pentaho.di.core.row.value.ValueMetaBase.getDate(ValueMetaBase.java:1866)
    2014/03/24 18:37:13 - Select values.0 - 	at org.pentaho.di.core.row.value.ValueMetaBase.convertData(ValueMetaBase.java:3470)
    2014/03/24 18:37:13 - Select values.0 - 	at org.pentaho.di.trans.steps.selectvalues.SelectValues.metadataValues(SelectValues.java:342)
    2014/03/24 18:37:13 - Select values.0 - 	at org.pentaho.di.trans.steps.selectvalues.SelectValues.processRow(SelectValues.java:386)
    2014/03/24 18:37:13 - Select values.0 - 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    2014/03/24 18:37:13 - Select values.0 - 	at java.lang.Thread.run(Unknown Source)
    2014/03/24 18:37:13 - Select values.0 - Caused by: java.text.ParseException: Unparseable date: "02/Mar/2014:10:10:53 +0100"
    2014/03/24 18:37:13 - Select values.0 - 	at java.text.DateFormat.parse(Unknown Source)
    2014/03/24 18:37:13 - Select values.0 - 	at org.pentaho.di.core.row.value.ValueMetaBase.convertStringToDate(ValueMetaBase.java:672)
    2014/03/24 18:37:13 - Select values.0 - 	... 6 more
    Is there anything I have to change in order make it parseable again?
    Thanks for any hint!

    Bobse

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    You need to account for the time zone offset that's included in your input data. Change your date format string to "dd/MMM/yyyy:HH:mm:ss Z".
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  3. #3

    Default

    Hi Darrell,

    Thanks for the help, but that resulted also in an error:

    Code:
    2014/03/25 10:17:46 - Select values.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
    2014/03/25 10:17:46 - Select values.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 
    2014/03/25 10:17:46 - Select values.0 - DateTime String : couldn't convert string [02/Mar/2014:10:10:53 +0100] to a date using format [dd/MMM/yyyy:HH:mm:ss Z] on offset location 3
    2014/03/25 10:17:46 - Select values.0 - Unparseable date: "02/Mar/2014:10:10:53 +0100"
    2014/03/25 10:17:46 - Select values.0 - 
    2014/03/25 10:17:46 - Select values.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.convertStringToDate(ValueMetaBase.java:675)
    2014/03/25 10:17:46 - Select values.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.getDate(ValueMetaBase.java:1866)
    2014/03/25 10:17:46 - Select values.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.convertData(ValueMetaBase.java:3470)
    2014/03/25 10:17:46 - Select values.0 -     at org.pentaho.di.trans.steps.selectvalues.SelectValues.metadataValues(SelectValues.java:342)
    2014/03/25 10:17:46 - Select values.0 -     at org.pentaho.di.trans.steps.selectvalues.SelectValues.processRow(SelectValues.java:386)
    2014/03/25 10:17:46 - Select values.0 -     at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
    2014/03/25 10:17:46 - Select values.0 -     at java.lang.Thread.run(Unknown Source)
    2014/03/25 10:17:46 - Select values.0 - Caused by: java.text.ParseException: Unparseable date: "02/Mar/2014:10:10:53 +0100"
    2014/03/25 10:17:46 - Select values.0 -     at java.text.DateFormat.parse(Unknown Source)
    2014/03/25 10:17:46 - Select values.0 -     at org.pentaho.di.core.row.value.ValueMetaBase.convertStringToDate(ValueMetaBase.java:672)
    2014/03/25 10:17:46 - Select values.0 -     ... 6 more
    What exactly does 'on offset location 3' mean here? That there is something wrong with the '/'?

    Bobse

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Isn't offset zero-based?
    I would suspect Mar to be an invalid short form of March in your locale.
    So long, and thanks for all the fish.

  5. #5

    Default

    Hi Marabu,

    Thanks, that finally did the trick! All I had to do was to set the locale of the date field in the Select values step to en_US and then it worked

    Bobse
    PDI 7
    Postgres
    Windows 7

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.