Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: CSV File with date column and date in different formats

  1. #1

    Default CSV File with date column and date in different formats

    I've got aCSV File with date column and dates in different formats and due to this problem CSV File Input object is not able to parse this date, since
    I can only put one pattern mm/dd/yyy or yyyy-mm-dd

    any suggestion or hint to proccess this or in an ETL step later


    Look at this Example

    System_RMX_Date
    10/31/2007
    12/13/2006
    2007-02-04
    7/27/2008
    2007-02-04
    5/15/2008
    5/15/2008
    2007-02-04
    11/23/2008
    1/29/2008
    10/31/2007
    10/31/2007
    2008-10-01
    2007-02-04
    10/31/2007

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Read the date as text.
    Then try to use various masks in a "Select Values" step (with Error Handling shifting failures to another try).

    mixed-dates.zip

    Personally I think that in general you can't reliably convert dates that way but I guess that's your best option. If your masks are sufficiently different you might be in luck.

  3. #3
    Join Date
    Dec 2009
    Posts
    609

    Default

    ...or use a "Modified JavaScript" Step and try to parse any incoming date-formats

    Personally, I would reject to read/import files which have changing data-formats...

  4. #4

    Default

    Quote Originally Posted by TomS View Post
    ...or use a "Modified JavaScript" Step and try to parse any incoming date-formats

    Personally, I would reject to read/import files which have changing data-formats...
    could you help me more and send me this script

  5. #5
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi,

    I do not have such a script
    Basically it was about the possibility to use string comparisons in order to scan for different date-formats

    Best regards,

    Tom

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    My sample (see above) works fine, doesn't require coding and is a number of times faster than using JavaScript.
    If you still would love to do it, you are up for some major coding.

  7. #7

    Default

    Name:  kettle1.jpg
Views: 784
Size:  36.1 KB

    Name:  kettle2.jpg
Views: 779
Size:  29.4 KB

    2011/02/03 14:23:17 - Convert MM/dd/yyyy.0 - Finished processing (I=0, O=0, R=1647, W=1088, U=0, E=559)
    2011/02/03 14:23:17 - Convert yyyy-MM-dd.0 - Finished processing (I=0, O=0, R=458, W=458, U=0, E=0)
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Unexpected batch update error committing the database connection.
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseBatchException:
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Error updating batch
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Data truncation: Data too long for column 'systemBiosDate' at row 1
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1471)
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.trans.steps.tableoutput.TableOutput.dispose(TableOutput.java:641)
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :



    I'm grafeful with all your help but after parsing it from the CSV file I've got to load it into a database
    and it fails can you help me

    I tried directly from the convert Steps coying all rows to Load to database step and failed
    I also tried with an append stream step and failed

    Regards

  8. #8
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi,

    this error:
    Data truncation: Data too long for column 'systemBiosDate' at row 1
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1471)

    Means, that your output column "systemBiosDate" contains values which are longer than the column-definition of the target table.
    e.g. the data-stream contains a string of length 300 chars and the target column is a varchar2(200) column.

    Also: Beware of the database-charset. On Oracle, column-definition can be "char-context" or "byte-context" which may lead to problems if the instance is using a multi-byte charset (e.g. UTF-16).

    Best regards,

    Tom

  9. #9

    Default

    Toms the problem is that System Bios date is a datetime field so it shouldn't give this error the transf gets dates in two formats? is correct merging in this way? read the full post and help me please.
    I don't understand well I'm using mysql not oracle, I don't understand well the charset issues expand it please
    Quote Originally Posted by TomS View Post
    Hi,

    this error:
    Data truncation: Data too long for column 'systemBiosDate' at row 1
    2011/02/03 14:23:17 - Load into Computrace db.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : org.pentaho.di.core.database.Database.emptyAndCommit(Database.java:1471)

    Means, that your output column "systemBiosDate" contains values which are longer than the column-definition of the target table.
    e.g. the data-stream contains a string of length 300 chars and the target column is a varchar2(200) column.

    Also: Beware of the database-charset. On Oracle, column-definition can be "char-context" or "byte-context" which may lead to problems if the instance is using a multi-byte charset (e.g. UTF-16).

    Best regards,

    Tom

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.