Solved

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

Posted on 2014-03-23
3
497 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 54

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 39950003
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
ID: 39951511
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 54

Expert Comment

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

~bp
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Bot application - advice 3 63
Formula in  Excel or vba to transfer values from Column X to Y 5 44
$_GET call between URL 3 45
Survey branching tutorial 11 44
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
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 …
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 …

726 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