Need help with a text editing script, find/replace from a list of variables

Hi everyone.  I'm a bit new to Visual Basic scripting, just wrote & ran my first script this weekend.  The script I wrote logged into all the routers on my company's network, did a "show interface description" command, and saved the results to a log file.  Of course the resulting file was huge - 891Kb - and I need to trim a lot of junk.  Basically, I need a script that will do this:
 
Open the file and read each line
If the line contains any of the following "x" strings, skip the line
Otherwise delete the line
Close the file
 
I'm not sure how many items will be included in "x", still compiling that ... basically I'm trying to trim out all connections to customer devices, and leave only the connections between our own equipment, and a few other specific connections I'll be adding to the "x" list.
 
Any and all help is appreciated :)

So what have you written thus far?

This is what I have so far.  If I understandthe code correctly it'll go through each line of text in the original file, then I need to put some code to determine what I'm going to with the line of text, then save it to a new file.  Just need some help with the part where I compare the text string to a list of items.  I'm guessing an IF/THEN routine, but as I said I don't know how long the list of things to check against is going to be.  I don't know if I should just list them all when I figure them out (in which case the script might end up looking like a router config with 200 ACLs), or create a subroutine that opens another file and checks the given line against each string in the 2nd file.  In effect, saying "If the line I just read matches any of the strings in this other file, append it to the new file ... otherwise dump it."
 
Sorry, the formatting seems messed up.  I tried to fix it, but it still comes out messed up. ??

 

 Sub Main 
      Const ForAppending = 8 
      Const DEVICE_FILE_PATH = "c:\scripts\originalfile.txt"     Dim fso 
       Set fso = CreateObject("Scripting.FileSystemObject")     Dim fil  
       Set fil = fso.OpenTextFile(DEVICE_FILE_PATH)     Dim originalfile   While Not fil.AtEndOfStream 
       originalfile = fil.ReadLine   
        Set objFSO = CreateObject("Scripting.FileSystemObject") 
        Set objStream = objFSO.OpenTextFile( _ 
        "C:\scripts\alteredfile", ForAppending, True) 
        objStream.WriteLine ************** 
        objStream.Close     Wend  
     
     End Sub 

Ok, so what are the strings you are searching like? Are they the whole line? Will they match the lines exactly? How many strings are we talking about?

Any line that contains a colon (:) needs to be kept because I'll need to run a script later to rename those interface descriptions to replace colons with dashes.  Any line that contains a list of hostnames that don't match our naming standard need to be kept because those interface descriptions need to be fixed too.  Right now I have a list of 102 hostnames that I know need to be changed, more may be added later.  The hostnames do not make up the entire line because the description might say "HostnameA - intermediate device - HostnameC".  Also these descriptions don't fit any standard right now, so I can't use anything like name = Split(line, ";")(0) because different delimiters are used, and multiple times within the same line.  So it'll have to be something that searches the line for any occurrence of characters matching the search terms.

Thanks again for any/all help.  Busy up to my ears here and just took a break to answer this to give my brain a break.

So the basic concept that you will use is to go through each line and either keep or discard the line depending on a State Flag. I discussed the concept in this thread:

http://www.visualbasicscript.com/tm.aspx?high=&m=80853&mpage=1#80949


Now you need to find a way to determine if the line is kept or discarded. The criteria as I understand them are keep if:

• The line has a : in it
• The name does not meet your naming convention

For the first requirement, a simple InStr() will work. The second is obviously more complex. It is possible to use a loop to check for any of a list of strings, but that would be really inefficient if there were any other possibility. Would it be possible to post some sample lines from the file. If the file is generated by a computer, I bet we can find a way to parse it that would be more efficient.

I was thinking about it while driving home today and realized two things:

• First is that I already know how to do the while not command, and unless someone can give me a good reason to put all 100+ strings in the script itself it just seems to make much more sense to put them in a separate data file ... in which case I would do nested while nots.  The outside loop would incrementally go through one file and the inside loop would compare that line to each line in the other file.
• Second, my original thought process is not going to work.  I was going to remove all unneeded lines to make a future find & replace script run faster.  But the log file I have with all the description names is separated by a few lines of text with the login text and the hostname of the device once logged in.  In the method described above I'd wind up erasing all that and leave myself with a list of interfaces and no idea what device they belong to.  So I'll have to combine the search & replace function and the delete if unneeded function into the same script.

So.  What I'll need to do is create a file that has the old string and the new string separated by a delimiter, like this:

:,-
_,-
badhostname1,goodhostname1
badhostname2,goodhostname2
IfIGetToThisString,DumpAndGoToNext

Using Line() I can pull out 0 and 1.  Then use an If/Then and that that InStr() to find the 0 and replace it with the 1.  The only problem I see is that the interface descriptions vary greatly, so it's really impossible to say that we'll find the required string in any particular location in the line, and I can't even say that it'll be delimited by any particular text.  Previous employees have used colons to delineate, some used underscores, some used hyphens, some just used empty spaces.  I don't want to post exact descriptions for security reasons, but I'll modify some and post them as examples.  I have to head out for a few hours, I'll either do it tonight when I get back or this weekend.  And I'll check out that link this weekend too.

Thanks again :)

Ok, I'm still not positive that I understand all the requirements, but here are my thoughts:

• For simply replacing an old name with a new name, The Replace() function should work. If you are worried about case sensitivity, then the .Replace() method of the RegExp object should work.
• I'm not sure about that last bit ("IfIGetToThisString,DumpAndGoToNext"), but I think using State Flags should work for it.

Ok, here's an altered example of some interface descriptions:
.
ge10-u0/0 description REGIABC-VAR1 TE7/3 via DWDM-2/5
ge10-u0/2 description REGI40-SER1(Te0/2):REGI40-EAR1(Te1/49)
ge10-u0/2.98 description REGIWAMTM-HED-D205-SWT1(E1)(CARD-READER)
ge10-u0/2.99 description REGI40:ENG:MANAGEMENT
ge10-u0/2.626 description DAC:DA0123:CUST-NAME-SOLUTIONS
ge-u9/33 description DAC-REGI40-MTRO-NID1(SFP-C)::REGI40-2550-SAL1(2.2)
ge-u9/7.0 description GQAM BA INTERFACE
.
The important variables in those lines are:
REGIABC-VAR1
REGI40-SER1
REGI40-EAR1
REGIWAMTM-HED-D205-SWT1
REGI40:ENG:MANAGEMENT
DAC:DA0123:CUST-NAME-SOLUTIONS
DAC-REGI40-MTRO-NID1(SFP-C)
REGI40-2550-SAL1
.
You'll notice that in the first line the word 'via' is used to denote a connection, in the last line '::' is used.  Also there are port numbers referenced alongside hostnames.  I want the script to be able to go through the file and (a) replace the bad hostnames with new hostnames and leave the other parts of the description alone, and (b) replace colons with hyphens.  Once I have a new file with the corrected hostnames I'll create a script to log into each device and correct the descriptions.
.
In the process of creating the file with these new interfaces & descriptions I want to delete from the new file any interface description that didn't need to be changed - because there's no point in having my next script go in and update an interface description that doesn't need to be updated.  Since these are production devices I don't want to load the CPUs any more than neccessary.  The last description I listed ("ge-u9/7.0 description GQAM BA INTERFACE") would be an example of one that doesn't need to be fixed, so it can be left out.
.
(PS ... I prefer to write my thread in Notepad because it's a bigger window, but when I copy & paste into the reply box it removes all my line breaks - even if I go back in and edit it after.  Not sure what's up with that.  Is there any way around this??)

Ok, so here is how I would do it. This is off the cuff so I'm sure some of it is wrong and I've forgotten some but it should give you the idea:

Make a file (replacements.txt). In the file would be something like:

badhostname1,goodhostname1
badhostname2,goodhostname2

Then your script would go something like:

Option Explicit
Dim dicReplacements : Set dicReplacements = CreateObject("Scripting.Dictionary")
Dim oFSO : Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oOutFile : Set oOutFile = oFSO.CreateTextFile("C:\Path\To\Some\New\File.txt")
Dim strLine
Dim strNewLine
Dim strNewName
Dim strNewLine

For Each strLine in Split(oFSO.OpenTextFile("C:\Path\To\Replacements.txt").ReadAll(), vbCrLf)
    dicReplacements.Add Split(strLine, ",")(0), Split(strLine, ",")(1)
Next

For Each strLine in Split(oFSO.OpenTextFile.ReadAll(), vbCrLf)
    For Each strNewName in dicReplacements.Keys()
        strNewLine = Replace(strLine, dicReplacements(strNewName), strNewName)
        If strNewLine <> strLine Then
            oOutFile.WriteLine strNewLine
            Exit For
        End If
    Next
Next
oOutFile.Close()

       

Oh Yeah, I forgot about the colons:

Change this line:

For Each strLine in Split(oFSO.OpenTextFile.ReadAll(), vbCrLf)

To this:

For Each strLine in Split(Replace(oFSO.OpenTextFile.ReadAll(), ":", "-"), vbCrLf)

Ha.  Got that new kid at X-Mas feeling ... new toys (commands) to check out.  I'll spend a few hours tomorrow researching them tomorrow, won't do it tonight or I'll stay up working instead of sleeping.  I did notice you have Dim strNewLine in there twice.  Did you forget you'd already put it in or was it supposed to be something different the second time?

Nope...just me not thinking.

Ok, time to do a little homework.  I looked up Scripting.Dictionary, never seen that before.  I like how you used that to do the same as thing my nested loop would've done - but in half the space lol ... I still have a few questions about what you wrote.
 
In the second section you have: "For Each strLine in Split(oFSO.OpenTextFile.ReadAll(), vbCrLf)" ... was there supposed to be a file name called in that line too?  I see in the first one you called "C:\Path\To\Replacements.txt" ... I'm guessing the second one would be something like "C:\Path\To\IntDescriptions.txt".
 
"For Each strNewName in dicReplacements.Keys()" .... I tried to Google terms involving scripting and keys but couldn't find anything that explained the function of having ".Keys()" at the end of the dictionary variable there.  Can you please explain?  Or link to a page that I can read more about that?
 
"strNewLine = Replace(strLine, dicReplacements(strNewName), strNewName) " ... this confuses me a bit.  Why is strNewName being used twice there?  I would expect something more like strOldName and strNewName.
 
Oh, and about the colon replacement thing ... I decided to leave that out of this for now.  I don't want to take any chances with the colong/hyphen replacement process interfering with the renaming of the hostnames.  I run the script as you wrote it the first time and once I'm comfortable with the hostname replacements being done correctly I can just use Notepad's Replace All function for the colons and underscores.

Ok, time to do a little homework.  I looked up Scripting.Dictionary, never seen that before.  I like how you used that to do the same as thing my nested loop would've done - but in half the space lol ... I still have a few questions about what you wrote.
 
In the second section you have: "For Each strLine in Split(oFSO.OpenTextFile.ReadAll(), vbCrLf)" ... was there supposed to be a file name called in that line too?  I see in the first one you called "C:\Path\To\Replacements.txt" ... I'm guessing the second one would be something like "C:\Path\To\IntDescriptions.txt".

Yep, just another instance of me typing without paying attention.
 
"For Each strNewName in dicReplacements.Keys()" .... I tried to Google terms involving scripting and keys but couldn't find anything that explained the function of having ".Keys()" at the end of the dictionary variable there.  Can you please explain?  Or link to a page that I can read more about that?

Your best place is the Windows Script Host documentation. You can find a link for that in the "Read me first" post that is pinned at the top of this section. Basically a dictionary is a list of Key-Value pairs. In our case the Key for each pair is the new serve name and the value is the matching old server name. The .Keys() property of a dictionary is just a collection of all the keys. It is the most common way to iterate through the contents of a dictionary.
 
"strNewLine = Replace(strLine, dicReplacements(strNewName), strNewName) " ... this confuses me a bit.  Why is strNewName being used twice there?  I would expect something more like strOldName and strNewName.

This goes back to my last answer. As I said, we are iterating through all of the keys in the dictionary and one at a time assigning each of them to the strNewName variable. That part I'm sure was clear to you. The reason that we use the variable twice has to do with the way that you access the value in a dictionary's Key-Value pair. One way to think of a dictionary is as an array where you get to use names instead of numbers for the index (as a matter of fact in PHP all arrays actually work this way, even the ones with numeric indexes). So for instance if I wanted to find the old name that matched a new name of "FOO", I would do so like this: dicReplacements("FOO"). So in the line of code that you are asking about, when we use strNewName by itself we are getting "FOO". When we do this: dicReplacements(strNewName) we are getting the old name that matches "FOO".
 
Oh, and about the colon replacement thing ... I decided to leave that out of this for now.  I don't want to take any chances with the colong/hyphen replacement process interfering with the renaming of the hostnames.  I run the script as you wrote it the first time and once I'm comfortable with the hostname replacements being done correctly I can just use Notepad's Replace All function for the colons and underscores.

Hmm.  You say that dicReplacements("FOO") would find what matches the new name of FOO ... ie, dicReplacements("FOO") would look up NEWFOO and respond with OLDFOO.  I find that counter-intuitive based on what I read about the way the dictionary works.  The only thing that I can think of is that you were assuming the dictionary info would be entered into the array like this:

NewName1,OldName1
NewName2,OldName2

... which is the opposite of what I had been thinking.  I have my data formatted like this:

OldName1, NewName1
OldName2, NewName2

In this format I would think that dicReplacements("FOO") would find the old name and give the new name as the result, no?

...re: errors in your posts ... I think that's just a sneaky way to make sure I'm (a) paying attention and (b) not relying on you to do all the work for me. :P
 
Lastly, as a note to anyone who may find this thread years from now ... I was talking with a coworker from another department who knows a bit about scripting and told him about the array method versus the looped while method.  He said with what I'm doing there wouldn't be a noticeable difference, but for scripts dealing with larger amounts of data he was taught that the while loops are better because they use less memory - only keeping 1 line in memory at a time, as opposed to an array which would put everything into memory.  Your thoughts on this?

I think you are right that I was assuming backwards name order in the names file. Your coworker is also right that for very large files, the array method is more memory intensive.

Ok, I've compiled everything above and run it on a test file and I've been having some problems.  Here's the script I have:

Option Explicit
Dim dicReplacements : Set dicReplacements = CreateObject("Scripting.Dictionary")
Dim oFSO : Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oOutFile : Set oOutFile = oFSO.CreateTextFile("C:\scripts\TestOutput.txt")
Dim strLine
Dim strNewLine
Dim strNewName

For Each strLine in Split(oFSO.OpenTextFile("C:\scripts\Data-Hostnames.txt").ReadAll(), vbCrLf)
    dicReplacements.Add Split(strLine, ",")(0), Split(strLine, ",")(1)
Next

For Each strLine in Split(oFSO.OpenTextFile("C:\scripts\Data-ShIntDescTest.txt").ReadAll(), vbCrLf)
    For Each strNewName in dicReplacements.Keys()
        strNewLine = Replace(strLine, dicReplacements(strNewName), strNewName)
        If strNewLine <> strLine Then
            oOutFile.WriteLine strNewLine
            Exit For
        End If
    Next
Next
oOutFile.Close()

When I run the file one thing is as expected - it only saves the lines that have been changed to the output file.  But I've tried to implement something that keeps the lines that the hostnames are on, and lines with colons.  I've added these two lines to the Data-Hostnames.txt file:

hostnamekeep,hostnamekept
:,:keep:

I then used Notepad's search & replace feature to change part of my login prompt to "hostnamekeep" because that line has the hostname in it.  The idea being, that when the script above runs through and makes these replacements it'll change the line and thus be caught by the If strNewLine <> strLine statement.  Unfortunately this isn't working and I can't figure out why.  I've tried a couple variations, no luck.  Any ideas?

So you want to keep any line that has a colon on it? Also there is one line that you want to keep even if there is no change?

Yes, I want to keep lines with colons in them, and the line between the interface descriptions that tells me which device the lines that follow are from.  As I said, I used Find&Replace to put "hostnamekeep" as part of the line that has the hostname, and I was telling the script to change that to "hostnamekept" so the new line fits the strNewLine <> strLine statement.