Regexp to reformat dates

[DiamondEagle]

I'm very new to the UE Regexp language so I need a bit of help.

I have a text file with various lines that contain a date in the following format:

D21/6/99

The first character is always a 'D'

I need to convert the date from:

D21/6/99
D15/4/06
D1/9/01

to:

D06/21/99
D04/15/06
D09/01/01

I'm using the UltraEdit 13.00+5 regexp engine.

It's probably really simple, but I've been battling for hours - even with the UE Help file!

[Mofi]

First, install hotfix 7 for UE v13.00.

Then you can use the following macro. What you want cannot be done with a single regular expression replace.

The macro property Continue if a Find with Replace not found must be checked for this macro (default since UE v13.00).

It would be good to know which character is following the year number to avoid wrong replaces. Is there always a space or the line termination or any other character?

However, the first regular expression find all dates with a single digit day and inserts the leading 0.

The second regex finds all dates with single digit month and inserts the missing leading 0.

The third regex finds all dates with single digit year and inserts the missing leading 0. For this regex I was forced to use the negative character range specification because you have not posted which character is following the year number.

And the last regex exchanges month and day in all the now well formatted dates.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Find MatchCase RegExp "D^([1-9]/[0-9]+/[0-9]+^)"
Replace All "D0^1"
Find MatchCase RegExp "^(D[0-3][0-9]/^)^([1-9]/[0-9]+^)"
Replace All "^10^2"
Find MatchCase RegExp "^(D[0-3][0-9]/[01][0-9]/^)^([0-9][~0-9]^)"
Replace All "^10^2"
Find MatchCase RegExp "D^([0-3][0-9]/^)^([01][0-9]/^)^([0-9][0-9]^)"
Replace All "D^2^1^3"

[DiamondEagle]

Thanks! That looks like it's perfect! I will continue to test. Again THANKS!

The character after the date is always a line termination. Here's a sample of one of the records in the source file:

STARTS HERE with the date:

D04/19/97
U70.39
T70.39
C*
NDEP
PXYZ Bank
LInterest Income:Bank Interest
^

ENDS with the ^ character. The next record begins on the line immediately after the ^ character. There are no blank lines.

The file format is a Quicken .QIF file - the UK version of Quicken. I'm trying to convert a UK QIF file into a format that will work with the US version of Quicken and the date format has to be converted from the UK format to the US format.

How would your sample code change now that I've confirmed that the character after the date is always a line termination?

One again THANKS!

[Mofi]

Well, now I would move the cursor always to top of the file, trim all trailing spaces, delete the negative character set definition in third regex and would use % (= start of line) and $ (= end of line) to avoid wrong replaces as much as possible.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Top
TrimTrailingSpaces
Find MatchCase RegExp "%D^([1-9]/[0-9]+/[0-9]+^)$"
Replace All "D0^1"
Find MatchCase RegExp "%^(D[0-3][0-9]/^)^([1-9]/[0-9]+^)$"
Replace All "^10^2"
Find MatchCase RegExp "%^(D[0-3][0-9]/[01][0-9]/^)^([0-9]^)$"
Replace All "^10^2"
Find MatchCase RegExp "%D^([0-3][0-9]/^)^([01][0-9]/^)^([0-9][0-9]^)$"
Replace All "D^2^1^3"

[DiamondEagle]

EXCELLENT!

Once again, THANKS.

[DiamondEagle]

One more question - this has really got my attention! A totally new part of UltraEdit to me that I didn't know existed!

How would we change the regexp so that it outputs the full 4-character YEAR, ie 1998 or 2007, etc? It would only have to evaluate for the years from 1990 to today.

So, my original before and after sample data would be:

Before:

D21/6/99
D15/4/06
D1/9/01

After:

D06/21/1999
D04/15/2006
D09/01/2001

Thanks...

[Mofi]

Add the following 2 regular expression replaces:

Find MatchCase RegExp "%^(D[01][0-9]/[0-3][0-9]/^)^(9[0-9]^)$"
Replace All ^119^2"
Find MatchCase RegExp "%^(D[01][0-9]/[0-3][0-9]/^)^([0-9][0-9]^)$"
Replace All ^120^2"

[DiamondEagle]

Another perfect solution! THANKS. I really appreciate it...