VBScript Replace specific value with regex and modify text file

Question

I know there are a lot questions similar to this one but i couldn't find the right answer for me. I need to replace all phrases in xml file that starts and ends with % (e.g. %TEST% or %TEST-NEW% )

So far i have these tryouts: This was my test one that works in the console but has only 1 line of string

zone = "<test>%TEST%</test>"
MsgBox zone
'Setting the regex and cheking the matches
set regex = New RegExp
regex.IgnoreCase = True
regex.Global = True
regex.Pattern = "%.+%"
Set myMatches = regex.execute(zone)
For each myMatch in myMatches
Wscript.echo myMatch
result = Replace(zone,myMatch,"")
next
MsgBox result

but when i try to do the same from a file with this...

Dim objStream, strData, fields
Set objStream = CreateObject("ADODB.Stream")
objStream.CharSet = "utf-8"
objStream.Open
objStream.LoadFromFile("C:\test\test.xml")
strData = objStream.ReadText()
Wscript.echo strData

set regex = New RegExp
regex.IgnoreCase = True
regex.Global = True
regex.Pattern = "%.+%"
Set myMatches = regex.execute(strData)
For each myMatch in myMatches
Wscript.echo myMatch
result = Replace(strData,myMatch,"")
next
Wscript.echo result

...the first echo returns correctly the contains of the file and then the second echo in the loop echoes all the matches that i need to replace , but the last echo return the same result as the first (nothing is being replaced)

The xml looks like this (just for example):

<script>%TEST%</script>
<value>%VALUE%</value>
<test>%TEST%</test>

P.S. I need to loop through xml files in a specific folder and replace the phrase from above. Can anyone help?

The final script that works for me(big thanks to Tomalak):

Option Explicit

Dim path, doc, node, placeholder,srcFolder,FSO,FLD,fil

Set placeholder = New RegExp
placeholder.Pattern = "%[^%]+%"
placeholder.Global = True


srcFolder = "C:\test"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder(srcFolder)

For each fil In FLD.Files

    if LCase(FSO.GetExtensionName(fil.Name)) = "xml" Then

            path = "C:\test\" & fil.Name

            ' 1. parse the XML into a DOM
            Set doc = LoadXmlDoc(path)

            ' 2. select and modify DOM nodes
            For Each node In doc.selectNodes("//text()|//@*")
                node.nodeValue = SubstitutePlaceholders(node.nodeValue)
            Next

            ' 3. save modified DOM back to file
            doc.save path
    End If

Next            
' --------------------------------------------------------------------------

Function LoadXmlDoc(path)
    Set LoadXmlDoc = CreateObject("MSXML2.DomDocument.6.0")

    LoadXmlDoc.async = False
    LoadXmlDoc.load path
    If LoadXmlDoc.parseError.errorCode <> 0 Then
        WScript.Echo "Error in XML file."
        WScript.Echo LoadXmlDoc.parseError.reason
        WScript.Quit 1
    End If
End Function
' --------------------------------------------------------------------------

Function SubstitutePlaceholders(text)
    Dim match

    For Each match In placeholder.Execute(text)
        text = Replace(text, match, GetReplacement(match))
    Next

    SubstitutePlaceholders = text
End Function
' --------------------------------------------------------------------------

Function GetReplacement(placeholder)
    Select Case placeholder
        Case "%TEST%": GetReplacement = "new value"
        Case "%BLA%": GetReplacement = "other new value"
        Case Else: GetReplacement = placeholder
    End Select
End Function
' --------------------------------------------------------------------------

Tomalak · Accepted Answer · 2016-04-13 15:38:27Z

3

Never use regular expressions on XML files, period.

Use an XML parser. It will be simpler, the code will be easier to read, and most importantly: It will not break the XML.

Here is how to modify your XML document in the proper way.

Option Explicit

Dim path, doc, node, placeholder

Set placeholder = New RegExp
placeholder.Pattern = "%[^%]+%"
placeholder.Global = True

path = "C:\path\to\your.xml"

' 1. parse the XML into a DOM
Set doc = LoadXmlDoc(path)

' 2. select and modify DOM nodes
For Each node In doc.selectNodes("//text()|//@*")
    node.nodeValue = SubstitutePlaceholders(node.nodeValue)
Next

' 3. save modified DOM back to file
doc.save path
' --------------------------------------------------------------------------

Function LoadXmlDoc(path)
    Set LoadXmlDoc = CreateObject("MSXML2.DomDocument.6.0")

    LoadXmlDoc.async = False
    LoadXmlDoc.load path
    If LoadXmlDoc.parseError.errorCode <> 0 Then
        WScript.Echo "Error in XML file."
        WScript.Echo LoadXmlDoc.parseError.reason
        WScript.Quit 1
    End If
End Function
' --------------------------------------------------------------------------

Function SubstitutePlaceholders(text)
    Dim match

    For Each match In placeholder.Execute(text)
        text = Replace(text, match, GetReplacement(match))
    Next

    SubstitutePlaceholders = text
End Function
' --------------------------------------------------------------------------

Function GetReplacement(placeholder)
    Select Case placeholder
        Case "%TEST%": GetReplacement = "new value"
        Case "%BLA%": GetReplacement = "other new value"
        Case Else: GetReplacement = placeholder
    End Select
End Function
' --------------------------------------------------------------------------

The XPath expression //text()|//@* targets all text nodes and all attribute nodes. Use a different XPath expression if necessary. (I will not cover XPath basics here, there are plenty of resources for learning it.)

Of course this solution uses regular expressions, but it does that on the text values that the XML structure contains, not on the XML structure itself. That's a crucial difference.

edited Apr 13, 2016 at 15:38

answered Apr 13, 2016 at 15:17

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

changer6360 Over a year ago

It works great for one file ,but what is the best way to make it loop though all xml files in a folder?

Tomalak Over a year ago

Well. Make a function out of the part that modifies a single file, run that function in a For Each loop. The FileSystemObject will help you with file system operations.

changer6360 Over a year ago

I made it to loop through the files ( uglier that you would probably do but it works) and now i have the problem that if there are other files types in the folder, the script will crash. Any ideas how to process only the .xml files?

Tomalak Over a year ago

Check the file extension before you process a file. There are multiple ways of checking a file extension in VBScript, pick one that suits you.

Tomalak Over a year ago

@changer There you go! — I just saw the edit to your question. This is pretty much exactly what I would have done, too.

|

Collectives™ on Stack Overflow

VBScript Replace specific value with regex and modify text file

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related