Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Converting date formats

  1. #1
    Join Date
    Nov 2013
    Posts
    6

    Default Converting date formats

    I have a file with a number of different date formats (all in string format), and I want to be able to convert them all to one format and convert the field to a date field.

    Some of the formats are as follows:
    23/03/1937
    September 8, 1985
    May 27, 2005
    10 August 1798
    1938 January 26

    Some of the dates are in quotes as well such as "May 26, 1987". I tried to remove the quotes using a Replace in String step, but this was unsuccessful.

    What's the best way to go about converting the dates?

    I've attached the ktr file and the data file (I've changed the format of the file to a txt file from csv).

    Any help would be greatly appreciated.
    Attached Files Attached Files

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

    Default

    You shouldn't have to worry about the quotes around a date string in a CSV file.
    That mixture of date formats should never arrive in a single file, but if it does you will have to test formats one by one.
    Even then, you can't be sure due to ambiguities.
    Here's a way without scripting.
    Attached Files Attached Files
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Nov 2013
    Posts
    6

    Default

    Thanks Marabu. I was getting stuck in a rut as I thought scripting was the only way I could do it.

    Is there a straight forward way for me to remove the day (i.e. Mon) and timestamp, so that Mon Apr 24 00:00:00 BST 1939 could be changed to Apr 24 1939, or 24/04/1939?

    I tried changing the format in the Canocical step, but it won't change it.

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

    Default

    I wasn't aware of weekdays or time portions in your sample data, but you should be able to parse a string like "Mon Apr 24 00:00:00 BST 1939" with the format "E MMM d HH:mm:ss z yyyy".
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Nov 2013
    Posts
    6

    Default

    I've actually converted it back to a string again and I have it in the format dd-mm-yyyy now which is what I need. Ultimately I'll be writing it back to a csv file, so it's fine like that.

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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

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