RegEx Validator

[NauticalGent]

The "meat and potatoes" of my main client's application requires large formatted text files to be read, parsed and "stuffed" into appropriate tables.

The existing code used various methods to achieve this, Instr() being one of them. Although Instr() and InstrRev() are useful and can get the job done (for the most part), they are somewhat limited (IMHO)

Fortunately for me, there was a true programmer(Perl, Java, C++) on the staff who took pity on me and introduced me to Regular Expressions, or RegEx. RegEx is much more powerful and can do so much more, such as string/pattern matching, replacement and much, much more. He wrote this sub for me to use while I was getting my hands dirty.

Here is one of the best online sources I have found in regards to RegEx tutorials. Even I was able to understand (most) of it, and that takes some doing!

https://www.youtube.com/watch?v=7DG3kCDx53c

To use it, you must reference MS VBScript Regular Expressions 5.5 in the VBE.

There is only one form, frmInput, that has two unbound fields. Simply type or paste the text you want to evaluate in the first field, and the RegEx pattern you want to test for and click the Test button. If a match is found, it will show all matches in a MsgBox, if not it will alert you that there were no matches. Nothing to it!

Take it for a spin and kick the tires. Always open to suggestions on improvement...

 

[isladogs]

Hi John

The idea of this looks interesting & maybe it will be obvious if I watch the entire video. However, at the moment it isn't clear (to me) what I need to type in the test pattern box

I see that arnelgp & Uncle Gizmo have both thanked you so perhaps its just me being dumb, but I would appreciate you providing a couple of examples to get me started ...

 

[jdraw]

I agree that a few examples or instructions could be useful.

I watched the video suggested and a couple of follow on videos.
I'm not sure what is expected but I tried the following

Hello to you 888 as the test string

The _Access is from the sample database

The other is from https://regex101.com/ an online validator

Here's another online validator http://rubular.com/

Testing inline image:

 

[NauticalGent]

To JDraw's credit, he DID send me a PM about the lack of examples and error handling on my first attempt. I told him I would fix it and re-submit, but it appears I have fallen short of the mark.I hear you guys loud and clear and will write a long and detailed response that will make even Doc envious...Just cant so it now, for some reason my 'puter at work does not play nice-nice with AWF.

 

[NauticalGent]

Before I get started, allow me to say to Col (Ridders) and Jdraw that you both have impressed me with your responses.

Jdraw, to answer your question quickly, you need to enclose the pattern you want matched and returned in parenthesis. For example to return "888" from "Hello World 888", your pattern string would be (\d{3}).

Not sure why, but the VBScript version of RegEx requires it. Not that your whole string would require it, only the part you want returned.

Here is an example of what my input file looks like:

RTTCNGCN RUOIBBB2883 3281746-UUUU--RHMCSUU. ZNY UUUUU
R 2417272 NOV 17 
FM USS NAVY SHIP
TO COMUSFLTFORCOM NORFOLK VA 
COMSEVENTHFLT
CTF 70
CTF 73
CTF 74 
COMCARSTRKGRU NINE 
COMDESRON FIFTEEN COMDESRON TWO THREE
NAVSHIPREPFAC AND JAPAN RMC YOKOSUKA JA
AIG 438
AIG 13954
INFO COMNAVSURFOR SAN DIEGO CA 
COMNAVSURFGRU MIDPAC
COMLOG WESTPAC
PRIORITY MATOFF BREMERTON WA 
CENSURFCOMBATSYSU DAM NECK VA 
NAVSURFWARCENDIV DAHLGREN VA 
NAVSURFWARCENDIV PORT HUENEME CA 
NAVSURFWARCENDIV CRANE IN
NAVY THREE ONE ONE NORFOLK VA 
BT
MSGID/CASRE/PDDG 97 HALSEY/120// 
CASUALTY/INITIAL-17243/PORT PELORUS OOC/EIC:L90H00/CAT:3//
AMPN/-// ESTIMATE/161718ZDEC17/-// 
ASSIST/NONE//
PARTSID/APL:253160056/JCN:N23154-CE03-1088// 
TECHPUB/0324LP0460000//
lPARTS
/DL NATIONAL STOCK NO.	RQD COSAL ONBD CIRCUIT
AMPN/-//
1STRIP RMKS/
MISHAP REPORT NOT REQUIRED
1.	(U)	SUMMARY/IMPACT: A LOT OF BAD STUFF
2.	(U) TECHNICAL DESCRIPTION: STUFF BROKE
3.	(U) PENDING ACTION: S/ F WILL ORDER AND REPLACE FAULTY PARTS. 
SHIP SKED: 01NOV99-27NOV99	U\W C7F
27NOV99-27NOV99	CHOP C5F
27NOV17-26MAR17	U\W C5F//
BT
#2883
NNNN

This is a formatted message and one line in particular holds the bulk of information I am looking for:

CASUALTY/INITIAL-17243/PORT PELORUS OOC/EIC:L90H00/CAT:3//

The capture this line I would do so by calling it in this manner:

Dim testString as String
Dim testRegexStr as String

testString = me.txtMyTxtField ' this would be the entire message
testRegexStr = "(CASUALTY.*\/.*)\/\/"

StripPatternMatchingRegex(testString, testRegexStr)

Which would return:

CASUATLY/INITIAL-17243/PORT PELORUS OOC/EIC:L90H00/CAT:3

I take this and use it to stuff an Array that I delimit with "/"

As a quick reference:
() - define a group, usually used to define a group of text together to
match.
[] - define a character class. This is a specific character to find. For
example, all capital letters is [A-Z] all capital letters or a number would
be [A-Z][0-9]
* - recurring any number of times
. - any character (excluding new line)
^ - beginning of a line
$ - end of a line
/ - I think VBA uses this as a pattern delimeter, so you need to escape it.

To use any of the special characters explicitly, you need to escape them
with '\'

These are just a small example of what RegEx can do and the tools at your disposal. In particular that I found RegEx to be superior to InStr() is in this case - notice the section of the message that has "SHIP SKED". Some ships will have "SHIP SKED", "SHIPS SKED", "SHIP SCHED", "SHIP SCHEDULE" and the options are only limited by each unit's creativity eventuality:

(SHIP{0,1} S[KCH]{1,2}ED(ULE){0,1})

I could go on, but I think this should get you on your way.

If not, you know what to do!

 

[jdraw]

Thanks NG.

One small point, that you might edit.

Throughout your sample text you have CASUATLY, but in your code you use CASUALTY. I know it's a spelling error and the code works with the correction.
Thanks for the additional info.

 

[NauticalGent]

I know it's a spelling error and the code works with the correction

The struggle is real...

 

[isladogs]

Thanks John

Had a disastrous day today with a hard drive failure.:banghead:
As a result, I've not looked at this yet but hope to do so tomorrow

Just installed Windows onto new hard drive so tomorrow I start reinstalling all programs ....
Thankfully it appears I've not lost any of my own files but its taken 12 hours to get back to a working PC
...AND I'm once again going to resolve to further improve my backup routines.

 

[NauticalGent]

...AND I'm once again going to resolve to further improve my backup routines.

Funny how it takes near catastrophe to wake us up...glad you didn’t lose too much data (if any). I can only imagine how much of your life was spent on it!

 

[NauticalGent]

It seems I wasn’t the only one to think of posting something of this nature. I found this today while I was looking for something else.

Good stuff and a much better post than mine:

https://www.access-programmers.co.uk/forums/showthread.php?t=97666