US and Worldwide: +1 (866) 660-7555
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Convert String to Date

  1. #1
    Join Date
    Mar 2011
    Posts
    118

    Default Convert String to Date

    I have a simple Excel spreadsheet with a column with a date (MM/dd/yyyy format) that I am trying to import using PDI. I
    used the Excel import function and I configured the step to import the date field as a string because sometimes the
    Excel date column contains a invalid date string.

    I tried to use the Modified Java Script Value step to create a output that would be a valid date or an empty date when the
    date string is invalid. My attempts have failed and not produced a date output.

    Can someone assist me by offering a script that I can use to convert a string to a date (MM/dd/yyyy) when the string is a
    valid date and output no date when the input string is not a valid date. This is driving me nuts.

    Many thanks
    Ray

  2. #2
    Join Date
    Sep 2009
    Posts
    809

    Default

    Hey Ray,

    I'd suggest reading the field as string, and using the select values step to convert to date with the correct mask (metadata tab). Then configure error handling on the select values step, and divert error rows to a small branch which would set the date field to a constant.

    Cheers
    Slawo
    Do you want to know more? Check out Adventures with Open Source BI

  3. #3
    Join Date
    Mar 2011
    Posts
    118

    Default

    Slawo,

    That is a great idea. I got myself locked into using javascript that I forgot about using another control.

    Thank you
    Ray

  4. #4
    Join Date
    Mar 2011
    Posts
    118

    Default Convert String to Date

    I tried to create the transformation as you mentioned using the select value step. It seems that it is working however I am
    not getting the output that I expected. I get the error DateIn String : couldn't convert string [01/01/2011] to a date using format [MM-dd-yyyy]
    Unparseable date: ""01/01/2011"" for each valid date entries in Excel.

    I attached an image of the transformation as well as I am including a copy of the ktr and Excel files. The Excel date field is imported as
    a string. It seems that for some reason I have having an issue with converting the date. I am sending the error output of the Select Values
    step directly to a text file so I was able to determine what was happening.

    Help and insight is very much appreciated.

    Thanks
    Ray

    date_transform.jpg
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2009
    Posts
    809

    Default

    Oh well, what can I say.

    If your date format is "MM/dd/yyyy" you should specify it as such in "select values", no?

    Works for me after I changed to the correct format.

    Cheers
    Slawo
    Do you want to know more? Check out Adventures with Open Source BI

  6. #6
    Join Date
    Mar 2011
    Posts
    118

    Default

    Slawo,

    I was certain that I tried to get the date format in the step however when I added it to my transform, it worked.

    Thank you for your help. I will be glad when I get the "hang" of this.

    Ray

  7. #7
    Join Date
    Sep 2009
    Posts
    809

    Default

    No worries, It becomes second nature after a while
    Do you want to know more? Check out Adventures with Open Source BI

  8. #8
    Join Date
    Jan 2013
    Posts
    2

    Default

    Hello I have very similar question as I think.
    I have column with date (in csv file). It can be in format: dd.mm.yyyy or dd-mm-yyyy or other. I want get the date in format dd.mm.yyyy, everything other I will put as a constant. (may be its possible to use two or three date format). So I need to put dd-mm-yyyy and constant in one column. I tried to use 'Select values' --> write to file1.csv, "Set field value to a constant" --> write to the same file1.csv. so I get error message "Error writing field : java.lang.ClassCastException". I suppose that this error appear then "Set field value to a constant" try to write in the file.

  9. #9
    Join Date
    Jan 2013
    Posts
    2

    Default

    repeat post
    Last edited by Lanskoy; 01-23-2013 at 04:21 AM.

  10. #10
    Join Date
    Jan 2013
    Posts
    2

    Default

    Can somebody help converting string "40908" to date format using pentaho?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •