User to user discussion and support for UltraEdit, UEStudio, UltraCompare, and other IDM applications.

Help with writing and playing macros
2 posts Page 1 of 1
I have some files created by an ODBC source that puts out dates like 9/30/2011, 11/1/2003, 2/2/2002. The format is m/d/yyyy. I am trying to import this into a SQL database but the import routines wants all dates to be yyyymmdd format (20110930, 20031101, 20020202 from above).

The date occurs twice per row, variable row length.

Is there a standard for doing this?

Thanks.
You need to run a macro (or script) with several Perl regular expressions using backreferences to convert all date strings to required format.

Code: Select all
InsertMode
ColumnModeOff
HexOff
Top
PerlReOn
Find MatchCase RegExp "\b([1-9]/)([1-9]/[12][09][0-9][0-9])\b"
Replace All "0\10\2"
Find MatchCase RegExp "\b([1-9]/[0-3][0-9]/[12][09][0-9][0-9])\b"
Replace All "0\1"
Find MatchCase RegExp "\b([01][0-9]/)([1-9]/[12][09][0-9][0-9])\b"
Replace All "\10\2"
Find MatchCase RegExp "\b([01][0-9])/([0-3][0-9])/([12][09][0-9][0-9])\b"
Replace All "\3\1\2"

The first Perl regular expression Replace All finds date strings with a single digit month and a single digit day and inserts the two missing leading zeros.

The second Perl regular expression Replace All finds date strings with a single digit month and a two digit day and inserts the missing leading zero on month.

The third Perl regular expression Replace All finds date strings with a two digit month and a single digit day and inserts the missing leading zero on day.

And the last Perl regular expression Replace All finally changes the date string format for all date strings in file.

So this macro code reformats the date strings

Code: Select all
2/2/2002
9/30/2011
11/1/2003
10/20/2012

to

Code: Select all
20020202
20110930
20031101
20121020
Best regards from Austria
2 posts Page 1 of 1
cron