Solved

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

Posted on 2014-03-23
3
490 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>
0
Comment
Question by:williamfl
  • 2
3 Comments
 
LVL 51

Accepted Solution

by:
Bill Prew earned 500 total points
Comment Utility
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
0
 

Author Closing Comment

by:williamfl
Comment Utility
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.
0
 
LVL 51

Expert Comment

by:Bill Prew
Comment Utility
Okay, glad that worked for you, let me know if you have any further questions.

~bp
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

771 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now