Date format change and substitutions for symbol

[mmcnown]

I use both UE ver 13.10 and UE3 13.10a+2 with the UE regex engine. I process very large log files and have to massage the data prior to importing into database. I use UE macros extensively in this process.

The log files are tab delimited files of email traffic for a large company. The first issue I am trying to solve is converting the date, which appears twice in each entry. It is in the format of: 2007-8-29

There are no padded zeros in months and dates of 1 thru 9 in the MM and DD fields. I want to convert all of these dates to MM/DD/YYYY.

The next issue is more complex. Email addresses appear twice in each line, and a domain also appears in the message ID. I want to split the email addresses from the domain (two fields), but I do not want to change the message ID which has the domain in it. I can get it to split the domain from all three by using a simple search and replace on the @; however, this goes too far. I only want it to split the domain from the user name in the email address in each line, not in the message ID. As you can see, there are three @ in each line. The one in the middle of the line that is identified as @mdm.gbl is the one I do not want changed. BTW, this @mdm.gbl changes and therefore is not the same in each message. In the examples below I want to divide Sender.Address@msn.com and john.doe@mycompany.biz


See the three sample extractions of the data below:

Example #1:

Code: Select all

2007-8-28   23:59:59 GMT   XXX.XXX.21.161   vlb-smtpin-08.ns.cs.theircompany.com   -   XBH-ENT-01   XX.XXX.14.47   john.doe@mycompany.biz   1019   BAY101-DAV5AA8D30@mdm.gbl   3   0   13036   1   2007-8-28 23:59:59 GMT   0   Version: 6.0.3790.3959   -    Fw: Message Subject - Mesa, AZ - 37/hr - Contract    Opening - APPLY NOW   Sender.Address@msn.com   -

Example #2:

Code: Select all

2007-8-28   23:59:59 GMT   XXX.XXX.21.161   vlb-smtpin-05.ns.cs.theircompany.com   -   XBH-ENT-01   XX.XXX.14.47   john.doe@mycompany.biz   1025   BAY101-DA90D5AA8D30@mdm.gbl   3   0   13036   1   2007-8-28 23:59:59 GMT   0   Version: 6.0.3790.3959   -    Fw: Message Subject - Mesa, AZ - 37/hr - Contract    Opening - APPLY NOW   Sender.Address@msn.com   -

Example #3:

Code: Select all

2007-8-29   0:0:0 GMT   XXX.XXX.21.161   vlb-smtpin-07.ns.cs.theircompany.com   -   XBH-ENT-01   XX.XXX.14.47   john.doe@mycompany.biz   1024   BAD5AA8D30@mdm.gbl   3   0   13036   1   2007-8-28 23:59:59 GMT   0   Version: 6.0.3790.3959   -    Fw: Message Subject - Mesa, AZ - 37/hr - Employment    Opening - APPLY NOW   Sender.Address@msn.com   -

[Bego]

Had to do some rework....

So the second part should work with this macro:

Code: Select all

InsertMode
ColumnModeOff
HexOff
PerlReOn
Top
Loop
Find RegExp "(\s[A-Z0-9._%+-]+)@([A-Z0-9.-]+\.[A-Z]{2,4}\s)"
Replace "$1 @ $2"
Key RIGHT ARROW
Find RegExp "(\s[A-Z0-9._%+-]+)@([A-Z0-9.-]+\.[A-Z]{2,4}\s)"
Key RIGHT ARROW
Find RegExp "(\s[A-Z0-9._%+-]+)@([A-Z0-9.-]+\.[A-Z]{2,4}\s)"
Replace "$1 @ $2"
IfNotFound
ExitLoop
EndIf
Key DOWN ARROW
Key HOME
EndLoop


Maybe there is a smarter way (Key Right arrow) to say: search after this found email-address but I don't know.

pls try out and come back

rds Bego

[Bego]

Hi Mike,

ok, it's turning to midnight soon, so here is a short Macro to do the date-conversion.
You can merge it to the macro above:

Code: Select all

InsertMode
ColumnModeOff
HexOff
PerlReOn
Top
Find RegExp "\-([1-9])([-])"
Replace All "-0\1\2"
Top
Find RegExp "\-([1-9])([ ])"
Replace All "-0\1\2"
Top
Find RegExp "([1-2][0-9][0-9][0-9])-([0-1][0-9])-([0-3][0-9])"
Replace All "\2/\3/\1"


it converts:

Code: Select all

the format of: 2007-1-29
the format of: 2007-8-2
the format of: 2007-11-30
the format of: 2007-8-2  bla

to

Code: Select all

the format of: 01/29/2007
the format of: 08/02/2007
the format of: 11/30/2007
the format of: 08/02/2007  bla

Greetz from Germany

Bego

[mmcnown]

Bego,

Works perfectly!! Thanks for all your help.

Mike