Go Back   Access World Forums > Microsoft Access Reference > Sample Databases

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-26-2017, 09:20 AM   #1
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,056
Thanks: 238
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
RegEx Validator

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...
Attached Files
File Type: accdb RegexGrabber.accdb (704.0 KB, 45 views)

__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
The Following 3 Users Say Thank You to NauticalGent For This Useful Post:
arnelgp (11-26-2017), sneuberg (11-28-2017), Uncle Gizmo (11-26-2017)
Old 11-26-2017, 01:49 PM   #2
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,907
Thanks: 52
Thanked 682 Times in 630 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: RegEx Validator

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 ...
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 11-26-2017, 04:21 PM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,868
Thanks: 41
Thanked 1,736 Times in 1,687 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: RegEx Validator

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:
Attached Images
File Type: png REgExTest_Access.png (28.3 KB, 60 views)
File Type: png REGEX101.png (66.6 KB, 33 views)

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by jdraw; 12-08-2017 at 11:49 AM.
jdraw is offline   Reply With Quote
Old 11-27-2017, 04:15 AM   #4
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,056
Thanks: 238
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
Re: RegEx Validator

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.
__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
Old 11-27-2017, 11:18 AM   #5
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,056
Thanks: 238
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
Re: RegEx Validator

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:

Code:
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:
Code:
CASUALTY/INITIAL-17243/PORT PELORUS OOC/EIC:L90H00/CAT:3//
The capture this line I would do so by calling it in this manner:

Code:
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:
Quote:
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:
Quote:
(SHIP[S]{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!
__________________
...see, what had happened was...

Last edited by NauticalGent; 11-27-2017 at 06:57 PM.
NauticalGent is offline   Reply With Quote
The Following User Says Thank You to NauticalGent For This Useful Post:
ridders (11-28-2017)
Old 11-27-2017, 04:06 PM   #6
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 10,868
Thanks: 41
Thanked 1,736 Times in 1,687 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: RegEx Validator

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.
Attached Images
File Type: png NGSampleatRubular.png (65.5 KB, 33 views)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
The Following User Says Thank You to jdraw For This Useful Post:
NauticalGent (11-27-2017)
Old 11-27-2017, 06:54 PM   #7
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,056
Thanks: 238
Thanked 135 Times in 123 Posts
NauticalGent will become famous soon enough
Re: RegEx Validator

Quote:
Originally Posted by jdraw View Post
I know it's a spelling error and the code works with the correction
The struggle is real...

__________________
...see, what had happened was...
NauticalGent is offline   Reply With Quote
Old 11-28-2017, 03:51 PM   #8
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,907
Thanks: 52
Thanked 682 Times in 630 Posts
ridders has a spectacular aura about ridders has a spectacular aura about
Re: RegEx Validator

Thanks John

Had a disastrous day today with a hard drive failure.
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.

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Reply

Tags
regex , regular expressions

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
RegEx in TSQL phinix SQL Server 1 04-28-2015 07:17 AM
Customer Textbox Validator Moore71 General 2 03-17-2014 02:20 PM
regex validation ozinm Code Repository 0 11-23-2005 09:11 AM
RegEx on TextField? A|ex General 5 08-17-2004 05:21 AM




All times are GMT -8. The time now is 10:15 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World