?
Solved

Trouble using Regex Inside VBscript

Posted on 2010-11-19
6
Medium Priority
?
592 Views
Last Modified: 2012-05-10
I am trying to identify and store a date/time value in a string. This string will be read from another file. I am trying to identify the date/time and then store it in a variable using regex and vbscript. I want to identify date/time in mm/dd/yy hh:mm:ss am|pm|AM|PM.

Here is the regex that does it:
^(([0]?[1-9]|1[0-2])/([0-2]?[0-9]|3[0-1])/[0-9]\d{1})? ?((([0-1]?\d)|(2[0-3])):[0-5]\d)?(:[0-5]\d)? ?(AM|am|PM|pm)?$

Open in new window


Here is an example of one line from the document where I want to identify the date/time from:
3400,CONTROLLER IN SLOT A,6531,97.8,84.6,30893.5,30893.5,1088.5,1088.5,Date/Time: 11/12/10 2:28:52 PM

Open in new window


Here is my test code:
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = "^(([0]?[1-9]|1[0-2])/([0-2]?[0-9]|3[0-1])/[0-9]\d{1})? ?((([0-1]?\d)|(2[0-3])):[0-5]\d)?(:[0-5]\d)? ?(AM|am|PM|pm)?$"
strToSearch = "3400,CONTROLLER IN SLOT A,6531,97.8,84.6,30893.5,30893.5,1088.5,1088.5,Date/Time: 11/12/10 2:28:52 PM"

Set myMatches = myRegExp.Execute(strToSearch)
For Each myMatch in myMatches
  msgbox myMatch.Value, 0, "Found Match"
Next

Open in new window

So ideally, I want to use this to look at each line in the txt document in question, and set a variable to the identified date/time value. This will then later be used for insert purposes into a sql table. I am struggling trying to get the date time value to be recognized correctly within the string. If you take off the explicit indicators (^ and $) on the regex every individual character comes up as a match but so does the exact string I am looking for. If I use the explicit indicators nothing in the string matches.

All help is welcome! Thanks in advance!
0
Comment
Question by:lffit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34175906
Try this modification:
Set myRegExp = New RegExp
myRegExp.IgnoreCase = True
myRegExp.Global = True
myRegExp.Pattern = "(1[012]|0?[1-9])/(3[01]|[012]\d)/\d{2} ?(1[012]|0?[1-9])(:([1-5]\d|0?\d)){2}"
strToSearch = "3400,CONTROLLER IN SLOT A,6531,97.8,84.6,30893.5,30893.5,1088.5,1088.5,Date/Time: 11/12/10 2:28:52 PM"

Set myMatches = myRegExp.Execute(strToSearch)
For Each myMatch in myMatches
  msgbox myMatch.Value, 0, "Found Match"
Next

Open in new window

0
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 total points
ID: 34175916
Perhaps that last zero shouldn't be optional  :)
(1[012]|0?[1-9])/(3[01]|[012]\d)/\d{2} ?(1[012]|0?[1-9])(:([1-5]\d|0\d)){2}

Open in new window

0
 

Author Comment

by:lffit
ID: 34176011
Haha, shoot. Is that what that means? I am new to regex. I was close though right? Thanks a bunch.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34176029
I learned from this site:  http://www.regular-expressions.info . They have a breakdown for several different languages.
0
 

Author Comment

by:lffit
ID: 34176064
In case anyone is interested the exact match looks like this:
(1[012]|0?[1-9])/(3[01]|[012]\d)/\d{2} ?(1[012]|0?[1-9])(:([1-5]\d|0\d)){2}?(:[0-5]\d)? ?(AM|am|PM|pm)?

Open in new window


That matches the following format:
mm/dd/yy hh:mm:ss AM|PM|am|pm

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34176144
I'm sorry... I completely forgot about the AM|PM part. I had it in my original attempt at your query, but then I rewrote it and forgot to put that in.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Progress

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question