none
Need help creating a vbs script to modify a text documents contents. RRS feed

  • Question

  • Hi there, first of all I would just like to say I am a complete novice at scripting so hoping someone with a bit more knowledge than myself can help me out with what I wish to acompish.

    Rght what it is I have a text file with details of lets say 100 items for each item theres 100 lines of data which starts with the data title then a variable what I am wanting to achieve out of those 100 lines I only need say 10 lines per item also I need to replace the text of the titles in a couple of the lines I am wanting to keep

    example of what im wanting to do

    object 1
    a=1
    b=2
    c=3
    d=4
    e=5
    f=6
    g=7
    h=8
    i=9
    j=10

    object 2
    a=1
    b=2
    c=3
    d=4
    e=5
    f=6
    g=7
    h=8
    i=9
    j=10

    from that I want a way to end up with

    object 1
    a=1
    b=2
    f=6
    something different=7
    something different2=8

    object 2
    a=1
    b=2
    f=6
    something different=7
    something different2=8


    I found the below script from a very old post on this site which will enable me to change either g or h

     
    Const ForReading = 1
    
    Const ForWriting = 2
    
    
    
    strFileName = Wscript.Arguments(0)
    
    strOldText = Wscript.Arguments(1)
    
    strNewText = Wscript.Arguments(2)
    
    
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    Set objFile = objFSO.OpenTextFile(file for editing, ForReading)
    
    
    
    strText = objFile.ReadAll
    
    objFile.Close
    
    strNewText = Replace(strText, g, something different)
    
    
    
    Set objFile = objFSO.OpenTextFile(file for editing, ForWriting)
    
    objFile.WriteLine strNewText
    
    objFile.Close
    
    


    but I dont even know how to modify it to do multiple text find and replace never mind picking and choosing which data I am wanting to keep please can someone help.
    thanks, James.

    running windows 7 by the way.
    Sunday, March 7, 2010 10:40 PM

Answers

  • A quick fix is to replace this line:

    strOutput = strOutput & vbCrLf & objFields(strField)

    with this:

    strOutput = strOutput & vbCrLf & strField & "=" & objFields(strField)

    As I said, there are different ways to approach this. Another idea is to hard code the strings to test for. This would be instead of the dictionary object. The replacement for g and h cannot be a simple replacement, we must detect the "=" and replace everything after that. An alternative follows:

    Option Explicit
    
    Dim objFSO, objFile, strFile, strLine
    
    Const ForReading = 1
    
    ' Specify the file.
    strFile = "c:\Scripts\Test.txt"
    
    ' Open the file.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)
    
    ' Read each line of the file.
    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
        ' Check if this is a new object.
        If (InStr(strLine, "object") > 1) Then
            ' Echo the line for a new object.
            Wscript.Echo strLine
        Else
            ' Check for every relevant field name.
            If (InStr(strLine, "a=") = 1) Then
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "b=") = 1) Then
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "f=") = 1) Then
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "g=") = 1) Then
                strLine = Left(strLine, 2) & "Something different"
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "h=") = 1) Then
                strLine = Left(strLine, 2) & "Something different2"
                Wscript.Echo strLine
            End If
        End If
    Loop
    
    ' Clean up
    objFile.Close
    


    Note this code is not case insensitive, so it assumes lower case. Also, it assumes no spaces before the "=" characters.

    Richard Mueller


    MVP ADSI
    • Marked as answer by IamMred Tuesday, March 23, 2010 7:53 PM
    Monday, March 8, 2010 2:54 AM
    Moderator

All replies

  • There are probably many ways to deal with this, and a lot depends on the specifics, but here is a quick solution that worked for me. I use a dictionary object, which is an associative array of pairs of values.

    Option Explicit
    
    Dim objFSO, objFile, strFile, strLine
    Dim strObject, objFields, strField
    Dim strOutput, k
    
    Const ForReading = 1
    
    ' Specify the file.
    strFile = "c:\Scripts\Test.txt"
    
    ' Specify string that indicates a new object.
    strObject = "object"
    
    ' Specify dictionary object of relevant of field names.
    Set objFields = CreateObject("Scripting.Dictionary")
    objFields.CompareMode = vbTextCompare
    objFields.Add "a", "a"
    objFields.Add "b", "b"
    objFields.Add "f", "f"
    objFields.Add "g", "Something different"
    objFields.Add "h", "Something different2"
    
    ' Open the file.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)
    
    ' Read each line of the file.
    strOutput = ""
    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
        ' Check if this is a new object.
        If (InStr(strLine, strObject) > 0) Then
            ' Output information for previous object.
            If (strOutput <> "") Then
                Wscript.Echo strOutput
            End If
            ' Header line for new object
            strOutput = strLine
        Else
            ' Check for a field name.
            k = InStr(strLine, "=")
            If (k > 1) Then
                strField = Trim(Left(strLine, InStr(strLine, "=") - 1))
                ' Check if the field is relevant.
                If objFields.Exists(strField) Then
                    ' Add the field to the output.
                    strOutput = strOutput & vbCrLf & objFields(strField)
                End If
            End If
        End If
    Loop
    
    ' Output for last object.
    If (strOutput <> "") Then
        Wscript.Echo strOutput
    End If
    
    ' Clean up
    objFile.Close

    Richard Mueller
    MVP ADSI
    Sunday, March 7, 2010 11:25 PM
    Moderator
  • thanks for your super quick reply and your help, created a text file with my example list in and ran the script you provided I get a windows script host box opening which reads
    object1
    a
    b
    f
    something different
    something different2
    and an ok prompt I click ok and the same is repeated for object2 I go to my example list and nothing has changed am I missing something?
    thanks, James.

    Sunday, March 7, 2010 11:52 PM
  • I didn't catch that you wanted the script to modify the file, or create a new one. As with most administrative scripts, this one is designed to be run at a command prompt using the cscript host program. You ran the script, probably by double-clicking the file, with the wscript host, possibly because this is the default on your computer. That means that each line of output shows up in its own message box. If you used cscript at a command prompt, the lines would have been written to the screen. For example, if the VBScript program is saved in the file ParseFile.vbs, you could run it at a command prompt with the statement:

    cscript ParseFile.vbs

    This assumes the file ParseFile.vbs is in the current directory. If not, you must specify the full path to the file. A big advantage of running scripts at a command prompt is that you can easily redirect the output to a text file. For example:

    cscript //nologo ParseFile.vbs > report.txt

    The //nologo optional parameter suppresses logo information. This creates the file report.txt, with the information you want.

    Alternatively, you could modify the script to write to a file. You cannot write to the file you are reading; you would need to write to a different file. You would first open a new file for write access with statements similar to below:

    ' New variables.
    Dim strOutput, objOutput Const ForWriting = 2 Const OpenAsASCII = 0 Const CreateIfNotExist = True ' Specify output file. strOutput = "c:\Scripts\report.txt" ' Open output file for write access.
    ' The object reference objFSO has already been established
    ' with a previous Set statement. Set objOutput = objFSO.OpenTextFile(strOutput, _ ForWriting, CreateIfNotExist, OpenAsASCII)

    Then, you would replace all Wscript.Echo statements with objOutput.WriteLine statements, so the lines are written to the file. For example, replace this:

    Wscript.Echo strOutput

    With this:

    objOutput.WriteLine strOutput

    I hope this helps.

    Richard Mueller
    MVP ADSI
    Monday, March 8, 2010 1:44 AM
    Moderator
  • brilliant just added the line cscript //nologo ParseFile.vbs > report.txt to a batch file as I like being able to double click and appears to work exactly as desired thankyou so much for your help if you dont mind will leave this post open for a few days just incase need any further development.
    thanks, James.
    Monday, March 8, 2010 1:52 AM
  • oh hang on a minute just realised the output was not quite as desired the script doesnt copy accross the variable is there anything that can be added to accomplish this at moment i'm getting

    object 1
    a
    b
    f
    Something different
    Something different2
    object 2
    a
    b
    f
    Something different
    Something different2

    when what I want is

    object 1
    a=1
    b=2
    f=6
    something different=7
    something different2=8

    object 2
    a=1
    b=2
    f=6
    something different=7
    something different2=8

    baring in mind the =* is variable.  * in my propper data file can be in text or digits of varying lengths of characters. although the values after the fields that have been renamed are constant so dont need to worry about those ones.
    thanks, James.

    Monday, March 8, 2010 2:03 AM
  • to be more specific the information for each field does still occupy only 1 line each so is there a way of copying any lines thats headed by

    object
    a
    b
    f

    thanks.                                        
    Monday, March 8, 2010 2:12 AM
  • A quick fix is to replace this line:

    strOutput = strOutput & vbCrLf & objFields(strField)

    with this:

    strOutput = strOutput & vbCrLf & strField & "=" & objFields(strField)

    As I said, there are different ways to approach this. Another idea is to hard code the strings to test for. This would be instead of the dictionary object. The replacement for g and h cannot be a simple replacement, we must detect the "=" and replace everything after that. An alternative follows:

    Option Explicit
    
    Dim objFSO, objFile, strFile, strLine
    
    Const ForReading = 1
    
    ' Specify the file.
    strFile = "c:\Scripts\Test.txt"
    
    ' Open the file.
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(strFile, ForReading)
    
    ' Read each line of the file.
    Do Until objFile.AtEndOfStream
        strLine = objFile.ReadLine
        ' Check if this is a new object.
        If (InStr(strLine, "object") > 1) Then
            ' Echo the line for a new object.
            Wscript.Echo strLine
        Else
            ' Check for every relevant field name.
            If (InStr(strLine, "a=") = 1) Then
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "b=") = 1) Then
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "f=") = 1) Then
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "g=") = 1) Then
                strLine = Left(strLine, 2) & "Something different"
                Wscript.Echo strLine
            ElseIf (InStr(strLine, "h=") = 1) Then
                strLine = Left(strLine, 2) & "Something different2"
                Wscript.Echo strLine
            End If
        End If
    Loop
    
    ' Clean up
    objFile.Close
    


    Note this code is not case insensitive, so it assumes lower case. Also, it assumes no spaces before the "=" characters.

    Richard Mueller


    MVP ADSI
    • Marked as answer by IamMred Tuesday, March 23, 2010 7:53 PM
    Monday, March 8, 2010 2:54 AM
    Moderator
  • thanks again Richard we are going in the right direction with this i'm sure most people would be able to iron out the last little bits for them self but as I say I am a complete noob.

    altering that 1 line works a treat on my sample list but not having so much luck with my real list I thought I would be able to adjust it accordingly when I got one working but am struggling a little to work out all the bits in the script that are specific for the sample I supplied.

    I really didnt want to be cheeky and just try and get someone to right my whole script but think I may end up having to ask for that.

    as far as g and h not being able to be simple replacements they actually can I think the best thing is for me to show you exactly what it is i'm tring to do. The file i'm trying to modify is a movie database .xml file i'm going to supply the details of a portion of just 1 movie and I will try and highlight what I want to do with it.

    <Titles>want to keep this line
      <Title>want to keep this line
        <ID>1</ID>want to keep this line
        <WebServiceID>22250094-2a3c-4512-ae0f-f534b3126e25</WebServiceID>
        <CollectionNumber>1</CollectionNumber>want to keep this line Variable changes per title
        <Type>DVD</Type>want to keep this line Variable changes per title
        <Barcode>5039036042086</Barcode>
        <Country>United Kingdom</Country>
        <LocalTitle>12 Rounds</LocalTitle>
        <LocalTitleDisplay>12 Rounds</LocalTitleDisplay>
        <OriginalTitle>12 Rounds</OriginalTitle>want to keep this line Variable changes per title
        <SortTitle>12 Rounds</SortTitle>
        <SortTitleDisplay>12 Rounds</SortTitleDisplay>
        <Edition>
        </Edition>
        <IMDB>tt1160368</IMDB>
        <Rating>5</Rating>
        <AspectRatio>2.35:1</AspectRatio>
        <VideoStandard>PAL</VideoStandard>
        <ProductionYear>2009</ProductionYear>want to keep this line Variable changes per title
        <ReleaseDate>10/12/2009</ReleaseDate>
        <RunningTime>105</RunningTime>
        <Categories>want to keep this line
          <Category>Data Type Movie</Category> want to change all occurances of this exact line to
     <DataType>Movie</DataType>

          <Category>File Type DVD</Category> want to change all occurances of this exact line to
     <FileType>DVD</FileType>
          <Category>Location Hard Drive</Category> want to change all occurances of this exact line to
     <Location>Hard
    Drive</Location>
        </Categories>want to keep this line
        <Genres>
          <Genre>Action</Genre>
        </Genres>
        <AudioTracks />
        <Subtitles NotPresent="False">
          <Subtitle Language="English" />
        </Subtitles>
        <Studios />
        <Discs>
          <Disc TitleMode="False" HideSideA="False" HideSideB="False">
            <DiscIdStored>
            </DiscIdStored>
            <DoubleSided>False</DoubleSided>
            <Name>Disc 1</Name>
            <DiscIdSideA>20E99B63-176013F7</DiscIdSideA>
            <DiscIdSideB>
            </DiscIdSideB>
            <LocationSideA>F:\DVDs\12 Rounds</LocationSideA>
            <LocationTypeSideA>1</LocationTypeSideA>
            <LocationSideB>
            </LocationSideB>
            <LocationTypeSideB>-1</LocationTypeSideB>
            <ChangerSlot>
            </ChangerSlot>
        <CheckSum>81ac5eb9c866b824c2134b86f0431e35</CheckSum>
      </Title>want to keep this line
    </Titles>want to keep this line

    thanks again, sorry for being a pain.
    James.

    Monday, March 8, 2010 6:35 PM
  • going to take a look at this when I get home

    Since this is an XML file, this almost looks like a Candidate for Powershell.

    Powershell. It's so Easy and it's FREE! Dive in and use it now, It'll take no time. :) http://www.energizedtech.com http://www.itprotoronto.ca
    Tuesday, March 9, 2010 2:03 AM
    Moderator
  • Not sure if this helps but from the sample data you provided if you use this Powershell script (*or at least use it to compare how to start in Powershell*) It will build a new file called C:\FINALFILE.XML assuming the source file is called C:\DVDLIST.XML

    -----------------

    $sourcefile=C:\DVDLIST.XML
    $destination=C:\FINALDVD.XML

    $list=GET-CONTENT $sourcefile

    foreach ($line in $list) {
       
        $item=$line
       
        if ($item.Contains("<Category>Data Type")) {
            $item=$item.Replace("<Category>Data Type ","<DataType>")
            $item=$item.Replace("</Category>","</DataType>")
        }
        elseif ($item.Contains("<Category>File Type")) {
            $item=$item.Replace("<Category>File Type ","<FileType>")
            $item=$item.Replace("</Category>","</FileType>")
            }
        elseif ($item.Contains("<Category>Location")) {
            $item=$item.Replace("<Category>Location ","<Location>")
            $item=$item.Replace("</Category>","</Location>")
        }
    add-content $destination -Value $item -force
    }

    --------------------------------------------

    There are many better ways to do this with Powershell but it might give you a view as to what is involved.  This process leaves the original file intact and creates a brand new XML file with the data.  The only fields that are changed are the ones you specified.

    Let me know how it all works out, I'm curious myself

    Sean
    The Energized Tech


    Powershell. It's so Easy and it's FREE! Dive in and use it now, It'll take no time. :) http://www.energizedtech.com http://www.itprotoronto.ca
    Tuesday, March 9, 2010 5:16 AM
    Moderator
  • Hi Sean big thanks for your input, powershell certainly does look a lot easier to get to grips with only thing is, is that when i get this script completed I would like to give it to a friend of mine to use also and would like to just be able to give it him and for it to work without having to install say powershell so would like to stick with vbs and batch files. I dont really need any more help on the replacement lines have managed to get a script working for those doubt its the best way but it works, the way ive done it i'm having to create a few temporary files as only way I seem to be able to get it to work is in a 3 step process the code i used is

    Const ForReading = 1
    Const ForWriting = 2

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\Scripts\input.xml", ForReading)

    strText = objFile.ReadAll
    objFile.Close
    strNewText = Replace(strText, "<Category>Data Type Movie</Category>", "<DataType>Movie</DataType>")

    Set objFile = objFSO.OpenTextFile("C:\Scripts\temp.xml", ForWriting)
    objFile.WriteLine strNewText
    objFile.Close

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\Scripts\temp.xml", ForReading)

    strText = objFile.ReadAll
    objFile.Close
    strNewText = Replace(strText, "<Category>File Type DVD</Category>", "<FileType>DVD</FileType>")

    Set objFile = objFSO.OpenTextFile("C:\Scripts\temp2.xml", ForWriting)
    objFile.WriteLine strNewText
    objFile.Close

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile("C:\Scripts\temp2.xml", ForReading)

    strText = objFile.ReadAll
    objFile.Close
    strNewText = Replace(strText, "<Category>Location Hard Drive</Category>", "<Location>Hard Drive</Location>")

    Set objFile = objFSO.OpenTextFile("C:\Scripts\output.xml", ForWriting)
    objFile.WriteLine strNewText
    objFile.Close

    so with that sorted I just need to find a way of selecting which lines of text i want to keep,
    thanks, James.

    Tuesday, March 9, 2010 10:14 AM