I am looking at creating a vb script to replace a value in multiple xml files quickly.

Posted on 2014-03-23

1 solution

498 Views

Last Modified: 2014-03-24

I am looking at creating a vb script to replace a value in multiple xml files quickly. It is a single xml file called dbid.xml and it is in each project folder. There are thousands of projects under different file folders under one main directory. Also would like to create a log of change to help validate change.

I have all the location in a variable file so just need code to run.

I need to replace the value in the parameters <PHYSICAL_DIRECTORY> </PHYSICAL_DIRECTORY>

Example
From
<PHYSICAL_DIRECTORY>\\server21\qc\Test_Target_Project1\</PHYSICAL_DIRECTORY>
To
<PHYSICAL_DIRECTORY>\\server311\qc11\Test_Target_Project1\</PHYSICAL_DIRECTORY>

Sample dbid.xml file

<?xml version="1.0" encoding="UTF-8"?>
<ProjectDescription>
      <PROJECT_NAME>Test_Target_Project1</PROJECT_NAME>
      <DB_TYPE>2</DB_TYPE>
      <DESCRIPTION>Created on 2013-08-02 09:36:37</DESCRIPTION>
      <DB_CONNSTR_FORMAT>jdbc:mercury:sqlserver://testqlserver:1433</DB_CONNSTR_FORMAT>
      <DB_NATIVE_AUTHENTICATION>Y</DB_NATIVE_AUTHENTICATION>
      <DB_NAME>project1_db</DB_NAME>
      <DBSERVER_NAME>testdbserver</DBSERVER_NAME>
      <DB_USER_PASS>qwertyuio</DB_USER_PASS>
      <PR_HAS_VCSDB>N</PR_HAS_VCSDB>
      <PHYSICAL_DIRECTORY>\\server21\qc\Test_Target_Project1\</PHYSICAL_DIRECTORY>
      <USERS_QUOTA>-1</USERS_QUOTA>
      <PR_IS_ACTIVE>Y</PR_IS_ACTIVE>
      <SAQ_IS_ACTIVE>N</SAQ_IS_ACTIVE>
      <VM_REPOSITORY></VM_REPOSITORY>
      <PR_LANGUAGE>English</PR_LANGUAGE>
      <PROJECT_TYPE>Standard</PROJECT_TYPE>
      <IS_TEMPLATE>N</IS_TEMPLATE>
      <PROJECT_UID>lkjhgfdsa11234</PROJECT_UID>
      <PR_SMART_REPOSITORY_ENABLED>Y</PR_SMART_REPOSITORY_ENABLED>
      <PR_IS_QPM_AUTO_CALC_ENABLED>Y</PR_IS_QPM_AUTO_CALC_ENABLED>
</ProjectDescription>

Give this a test, after saving as a VBS and adjusting the strings near the top as needed. It will search the specified base folder for all files with the specified name, and replace the existing file path on the element you described with the one you specify. Since your XML file was pretty simple I just processed it as a text file, which should work fine for you.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2

' Define the folder to scan, and the age of files to locate
strBaseDir = "B:\EE\EE28395531\Files"
strFindFile = "dbid.xml"
strReplaceString = "\\server311\qc11\Test_Target_Project1\"

' Set up filesystem object for usage
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Look for files
FindFiles objFSO.GetFolder(strBaseDir)

' Subroutine (recursive) to search for files to delete
Sub FindFiles(objFolder)
   On Error Resume Next

   ' Look at all files in this folder
   For Each objFile In objFolder.Files
      ' Make sure it's the filename
      If LCase(objFile.Name) = LCase(strFindFile) Then
         UpdateFile objFile.Path
      End If
   Next

   ' See if we were able to access this folder, if not don't recurse into it
   If Err.Number = 0 Then
       ' Recursively drill down into subfolder
       For Each objSubFolder In objFolder.SubFolders
           FindFiles objSubFolder, intLevel
       Next
   End If

End Sub

' Subroutine to update a file
Sub UpdateFile(strFile)
   ' Read entire input file into a variable and close it
   Set objFile = objFSO.OpenTextFile(strFile, ForReading, False, TriStateUseDefault)
   strData = objFile.ReadAll
   objFile.Close
   Set objFile = Nothing

   ' Replace desired strings
   Set objRegExp = New RegExp
   objRegExp.Global = True
   objRegExp.IgnoreCase = False
   objRegExp.Pattern = "(\<PHYSICAL_DIRECTORY\>).*(\</PHYSICAL_DIRECTORY\>)"
   strData = objRegExp.Replace(strData, "$1" & strReplaceString & "$2")

   ' Write file with any changes made
   Set objFile = objFSO.OpenTextFile(strFile, ForWriting, True)
   objFile.Write(strData)
   objFile.Close
   Set objFile = Nothing
End Sub

Open in new window

~bp

3 Comments

LVL 55

Accepted Solution

by:Bill Prew

Bill Prew earned 500 total points

ID: 399500032014-03-24

Author Closing Comment

by:williamfl

ID: 399515112014-03-24

It worked great, just need to get it to loop and read the parameters from a text file now for strBaseDir and the strReplaceString. This way we can run updates in a batch mode. I have done loops before just need to find code used before.

Expert Comment

ID: 399520102014-03-24

Okay, glad that worked for you, let me know if you have any further questions.

~bp

Course Of The Month

I am looking at creating a vb script to replace a value in multiple xml files quickly.

Welcome to Experts Exchange

Featured Post

Suggested Courses

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

Enjoyed your answer?

Keep in touch with Experts Exchange

Live Consultants