{"id":1358,"date":"2009-04-24T07:20:40","date_gmt":"2009-04-24T15:20:40","guid":{"rendered":"http:\/\/www.sapien.com\/blog\/2009\/04\/24\/vbscript-complex-regex-replace\/"},"modified":"2009-04-24T07:20:40","modified_gmt":"2009-04-24T15:20:40","slug":"vbscript-complex-regex-replace","status":"publish","type":"post","link":"https:\/\/dev.sapien.com\/blog\/2009\/04\/24\/vbscript-complex-regex-replace\/","title":{"rendered":"VBScript Complex Regex Replace"},"content":{"rendered":"

A few days ago I posted a  blog entry<\/a> on simple regular expression replacements in VBScript. Let me show you a more complex example. It helps to have a purpose, even for demonstration so my need is to convert an html table to CSV output using regular expressions. We’re going to need the functions I’ve written about before but I’ll post them again so you don’t have to go looking for them.<\/p>\n

\n
\n
Function<\/span> RegExReplace(strString,strPattern,strReplace)<\/pre>\n
On<\/span> Error<\/span> Resume<\/span> Next<\/span><\/pre>\n
    Dim<\/span> RegEx<\/pre>\n
    Set<\/span> RegEx = New<\/span> RegExp              ' Create regular expression.<\/span><\/pre>\n
    RegEx.IgnoreCase = True<\/span>             ' Make case insensitive.<\/span><\/pre>\n
    RegEx.Global<\/span>=True<\/span>                   'Search the entire String<\/span><\/pre>\n
    RegEx.Pattern=strPattern<\/pre>\n
        <\/pre>\n
    If<\/span> RegEx.Test(strString) Then<\/span>       'Test if match is made<\/span><\/pre>\n
        RegExReplace = regEx.Replace(strString, strReplace) ' Make replacement.<\/span><\/pre>\n
     Else<\/span><\/pre>\n
         'return original string   <\/span><\/pre>\n
         RegExReplace=strString<\/pre>\n
    End<\/span> If<\/span><\/pre>\n
End<\/span> Function<\/span><\/pre>\n
 <\/pre>\n
Function<\/span> RegExMatch(strString,strPattern)<\/pre>\n
    Dim<\/span> RegEx<\/pre>\n
    RegExMatch=False<\/span><\/pre>\n
    <\/pre>\n
    Set<\/span> RegEx = New<\/span> RegExp              <\/pre>\n
    RegEx.IgnoreCase = True<\/span>             <\/pre>\n
    RegEx.Global<\/span>=True<\/span>                   <\/pre>\n
    RegEx.Pattern=strPattern<\/pre>\n
    <\/pre>\n
    If<\/span> RegEx.Test(strString) Then<\/span> RegExMatch=True<\/span><\/pre>\n
 <\/pre>\n
End<\/span> Function<\/span> <\/pre>\n
 <\/pre>\n
Function<\/span> GetMatch(strString,strPattern)<\/pre>\n
    Dim<\/span> RegEx,arrMatches<\/pre>\n
    Set<\/span> RegEx = New<\/span> RegExp              <\/pre>\n
    RegEx.IgnoreCase = True<\/span>             <\/pre>\n
    RegEx.Global<\/span>=True<\/span>                   <\/pre>\n
    RegEx.Pattern=strPattern<\/pre>\n
    Set<\/span> colMatches=RegEx.Execute(strString)<\/pre>\n
    Set<\/span> GetMatch=colMatches<\/pre>\n
End<\/span> Function<\/pre>\n<\/div>\n<\/div>\n
Let’s dig in. Here’s the table I want to parse.<\/p>\n\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n
DeviceID<\/th>\nSize<\/th>\nFreeSpace<\/th>\nVolumename<\/th>\nSystemName<\/th>\n<\/tr>\n
C:<\/td>\n80024170496<\/td>\n8556191744<\/td>\n <\/td>\nCHAOS<\/td>\n<\/tr>\n
F:<\/td>\n80023715840<\/td>\n71938871296<\/td>\nEDGE_DISKGO<\/td>\nCHAOS<\/td>\n<\/tr>\n
L:<\/td>\n80024170496<\/td>\n8556191744<\/td>\n <\/td>\nCHAOS<\/td>\n<\/tr>\n
Q:<\/td>\n250057060352<\/td>\n136200368128<\/td>\nNew Volume<\/td>\nCHAOS<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
I’ll begin by reading in the contents of the html file and saving it to a variable.<\/p>\n
\n
\n
Set<\/span> objfso=CreateObject(\"Scripting.FileSystemObject\"<\/span>)<\/pre>\n
Set<\/span> objFile=objfso.OpenTextFile(\"c:\\test\\drives.html\"<\/span>)<\/pre>\n
 <\/pre>\n
Do<\/span> While<\/span> objFile.AtEndOfStream <> True<\/span><\/pre>\n
 html=objFile.ReadAll()<\/pre>\n
Loop<\/span><\/pre>\n
 <\/pre>\n
objFile.Close<\/pre>\n<\/div>\n<\/div>\n
The next step is to strip out just the table. Using a regular expression, I find the text that matches everything between and including the table tags.<\/p>\n
\n
\n
'get just the table<\/span><\/pre>\n
If<\/span> RegExMatch(html,\"<table[^>]*>([\\S\\s]*?)<\/table>\"<\/span>) Then<\/span><\/pre>\n
    Set<\/span> matches=GetMatch(html,\"<table[^>]*>([\\S\\s]*?)<\/table>\"<\/span>)<\/pre>\n
    For<\/span> Each<\/span> match In<\/span> matches<\/pre>\n
        tableText=Trim(match.value)<\/pre>\n
    Next<\/span><\/pre>\n
End<\/span> If<\/pre>\n<\/div>\n<\/div>\n
All that’s left at this point is to get rid of unnecessary tags like TH and convert TH and\/or TD tags. If the pattern is matched in the string, then for every match I call my RegexReplace function. <\/p>\n
\n
\n
'strip off <tr> tags<\/span><\/pre>\n
tableText = RegexReplace(tableText,\"<\/?tr[^>]*>\"<\/span>,\"\"<\/span>)<\/pre>\n
'convert <th><\/th> to \",\"<\/span><\/pre>\n
tableText = RegexReplace(tableText,\"<\/th><th>\"<\/span>,CHR(34) & \",\"<\/span> & CHR(34))<\/pre>\n
'convert <th> or <\/th> to \"<\/span><\/pre>\n
tableText = RegexReplace(tableText,\"<th>|<\/th>\"<\/span>,CHR(34))<\/pre>\n
'convert <\/td><td> to \",\"<\/span><\/pre>\n
tableText = RegexReplace(tableText,\"<\/td><td>\"<\/span>,CHR(34) & \",\"<\/span> & CHR(34))<\/pre>\n
'convert <td> or <\/td> to \"<\/span><\/pre>\n
tableText = RegexReplace(tableText,\"<td>|<\/td>\"<\/span>,CHR(34))<\/pre>\n<\/div>\n<\/div>\n
It’s possible there might be some tags still in my tableText variable so I’ll process it one more time looking for any HTML tag and replace it with a blank (“”).<\/p>\n
\n
\n
'strip off any remaining tags<\/span><\/pre>\n
tableText = RegexReplace(tableText,\"<(?![!\/]?[ABIU][>\\s])[^>]*>\"<\/span>,\"\"<\/span>)<\/pre>\n<\/div>\n<\/div>\n
Now the tricky part. If I look at tableText there will be blank lines for any tags I replaced at the end. Plus if I wanted to save the output to a text file I need some way to parse this variable. My solution was to turn it into an array and enumerate it, only displaying lines with a length greater than 0.<\/p>\n
\n
\n
'turn remaining text into an array<\/span><\/pre>\n
arrText=Split(tabletext,VbCrLf)<\/pre>\n
 <\/pre>\n
'strip out blank lines<\/span><\/pre>\n
For<\/span> i=0 To<\/span> UBound(arrText) -1<\/pre>\n
    if<\/span> Len(arrText(i)) >0 Then<\/span><\/pre>\n
        'or send output to a text file<\/span><\/pre>\n
        WScript.Echo arrText(i)<\/pre>\n
    End<\/span> if<\/span><\/pre>\n
Next<\/span>    <\/pre>\n<\/div>\n<\/div>\n
When I run my script I get output like this:<\/p>\n
“DeviceID”,”Size”,”FreeSpace”,”Volumename”,”SystemName”
“C:”,”80024170496″,”8556191744″,””,”CHAOS”
“F:”,”80023715840″,”71938871296″,”EDGE_DISKGO”,”CHAOS”
“L:”,”80024170496″,”8556191744″,””,”CHAOS”
“Q:”,”250057060352″,”136200368128″,”New Volume”,”CHAOS” <\/p>\n
Now before you think I’m some Regex guru (not by any means), I didn’t come up with any of the more complex regular expression patterns. Instead I went to my favorite site for this sort of thing, RegexLib.com<\/a>. Fortunately many people have already done the hard work of developing regular expression patterns for all sorts of things.  A little search and copy\/paste and I’m in business. Because regular expressions work the same just about everywhere you can use these expressions in VBScript, PowerShell, PHP, Perl or probably anything you happen to be working in.<\/p>\n<\/p>\n
\n
Download a text file with code from this entry  here.<\/a><\/p>\n<\/div>\n
As always, if you need help with regular expression scripts or any other scripting problem please join me in the forums at ScriptingAnswers.com<\/a>.  Oh…don’t forget there is an entire chapter on using the REGEX object in VBScript in WSH and VBScript Core: TFM<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
A few days ago I posted a  blog entry on simple regular expression replacements in VBScript. Let me show you a more complex example. It helps to have a purpose, even for demonstration so my need is to convert an html table to CSV output using regular expressions.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[24],"tags":[103,463,464,57,996],"class_list":["post-1358","post","type-post","status-publish","format-standard","hentry","category-vbscript","tag-html","tag-regex","tag-regular-expressions","tag-scripting","tag-vbscript"],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/posts\/1358","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/comments?post=1358"}],"version-history":[{"count":0,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/posts\/1358\/revisions"}],"wp:attachment":[{"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/media?parent=1358"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/categories?post=1358"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dev.sapien.com\/blog\/wp-json\/wp\/v2\/tags?post=1358"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}