Convert mutiple date/time formats

A discussion of how to use EasyPatterns, EasyPattern Helper and using the EasyPattern library.

Moderator: DataMystic Support

rooster_cogbern
Posts: 2
Joined: Sat Mar 05, 2011 3:04 pm

Convert mutiple date/time formats

Postby rooster_cogbern » Sat Mar 05, 2011 3:18 pm

I love this product, but still learning. I am having a date & time conversion issue.

I have a text file that has multiple formats for dates and times:

D-MMM-YY, as in 2-Feb-11
D/MM/YYYY 0:00, as in 1/01/2011 0:00 or 12/24/2010 7:20
D-MMM-YY

And would like to convert all to YYYY-MM-DD 00:00:00

For my first test run - I am able to capture with the Easy pattern :
[capture (Day) <-/> capture(Month) <-/> capture (Year)]

But how to replace in correct format?
$3-$2-$1 00:00:00
Gives me 2011-JAN-12 00:00:00, when I want 2011-01-12 00:00:00

Regards - Rooster

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Convert mutiple date/time formats

Postby DataMystic Support » Sun Mar 06, 2011 9:32 pm

Easy Rooster,

Using TextPipe, just follow this initial search/replace with a search/replace that swaps
Jan->01
Feb->02
etc.

Ideally - make it a subfilter of the replacement above, so that it only works on dates that have been matched, and not on Jan found anywhere in the text.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

rooster_cogbern
Posts: 2
Joined: Sat Mar 05, 2011 3:04 pm

Re: Convert mutiple date/time formats

Postby rooster_cogbern » Mon Mar 07, 2011 1:58 am

That makes sense, Thanks!

joscode
Posts: 2
Joined: Tue Mar 29, 2016 4:03 am

Re: Convert mutiple date/time formats

Postby joscode » Tue Mar 29, 2016 4:23 am

This tool continues to amaze me. However, I am stuck looking to format dates from one format to a single uniform format: I hope someone can help. Here's what I have:

Dates from a csv in the following format: "M/D/YYYY". This can include 8 numbers or 6 depending on the month and day.
Example of a 6 digit date: "1/1/2015"
Example of an 8 digit date: "10/13/2015"

What I am trying to accomplish is:"YYMMDD" for X12 HIPA compliance.
I get mixed results with: [capture(Day),<-/>capture(Month),<-/>capture(Year)]
Replace with: $3$2$1

Same with: [capture(2 digits), punctuation capture(2 digits), punctuation(4 digits)]
Replace with $3$2$1.

I can't get past this, so I can't even begin to try trimming the year to "YY".

Any suggestions?

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Convert mutiple date/time formats

Postby DataMystic Support » Tue Mar 29, 2016 8:25 am

Ok, X12 HIPA compliance is easy!

Method 1. Use TextPipe's upper left search box to search for 'date' and you should see 'add leading zeroes to dates'. Checkout the trial run of this filter (F5) to see it working,

Method 2.

Essentially, you need to precede your EasyPattern match:

Code: Select all

[capture(2 digits), punctuation capture(2 digits), punctuation(4 digits)]

Replace with

Code: Select all

$3$2$1


with two matches that look for 1-digit months and 1-digit days respectively, and makes them into 2 digits e.g.

Fix month:

Code: Select all

[capture(Day),<-/>, capture(1 digit),<-/>, capture(Year)]

In the Replace with field, choose Action 'Send variable 2 to subfilter' (ie the month part)
Next, add a 'Filters\Add\Left margin' filter as a subfilter, with text of 0 (ie a zero).
This will add a leading zero to 1 digit months.

A Similar approach for fixing the day, except we must ensure the day is not already 2-digit.

Code: Select all

[ mustNotStartWith( digit ), capture(1 digit),<-/>, capture(Month),<-/>, capture(Year)]

In the Replace with field, choose Action 'Send variable 1 to subfilter'.
Next, add a 'Filters\Add\Left margin' filter as a subfilter, with text of 0 (ie a zero).
This will add a leading zero to 1 digit days.
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments

joscode
Posts: 2
Joined: Tue Mar 29, 2016 4:03 am

Re: Convert mutiple date/time formats

Postby joscode » Thu Mar 31, 2016 3:26 am

Thank you Simon. I made the changes you suggested, and it works fine with the test data, but only on some dates in my actual file. I am using a straight comma delimited file using the quotes and commas. Does this require tweaking?

User avatar
DataMystic Support
Site Admin
Posts: 2138
Joined: Mon Jun 30, 2003 12:32 pm
Location: Melbourne, Australia
Contact:

Re: Convert mutiple date/time formats

Postby DataMystic Support » Thu Mar 31, 2016 1:30 pm

Did you check the options to restrict to the field, and remove quotes and delimiters?

Can you paste the data that is not working?
Regards,

Simon Carter, http://DataMystic.com/forums/index.php
http://PredictBGL.com - Insulin dose calculator for Type 1 diabetes
http://DownloadPipe.com - 250,000 free software downloads
http://DetachPipe.com - send huge email attachments


Return to “EasyPatterns Support”

Who is online

Users browsing this forum: No registered users and 2 guests