[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2014-03-23
3
Medium Priority
?
503 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 57

Accepted Solution

by:
Bill Prew earned 2000 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 57

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Simple Linear Regression

649 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