Remove String from Text File

[jmedlin6]

My script works for the most part, however I just think it's really messy and could be cleaned up better. I replaced the actual string values with generic ones as they contained some data pertaining to my organization. Basically I need it to go through a txt files (students.txt) and remove the strings and delete the empty lines. Also at the end of the file there is a string that shows how many rows were affected (i.e. 95 rows affected). I need to remove this as well. The number isn't always the same or I would just set it to remove the specific string. Any ideas would be greatly appreciated.

 

 

 

 

Dim st1, st2, st3, st4, st5, st7, st8, st9, st10, st11, st12, st13, st14, st15, st16, st17, st18, st19, st20, st21

 

st1 = "This is string 1"

st2 = "This is string 2"

st3 = "This is string 3"

st4 = "This is string 4"

st5 = "This is string 5"

st6 = "This is string 6"

st7 = "This is string 7"

st8 = "This is string 8"

st9 = "This is string 9"

st10 = "This is string 10"

st11 = "This is string 11"

st12 = "This is string 12"

st13 = "This is string 13"

st14 = "This is string 14"

st15 = "This is string 15"

st16 = "This is string 16"

st17 = "This is string 17"

st18 = "This is string 18"

st19 = "This is string 19"

st20 = "This is string 20"

st21 = "This is string 21"

 

Const ForReading = 1

Const ForWriting = 2

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st1, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st2, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st3, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st4, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st5, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st6, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st7, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st8, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st9, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st10, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st11, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st12, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st13, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st14, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st15, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st16, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st17, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st18, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st19, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st20, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, st21, "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForReading)

 

strText = objFile.ReadAll

objFile.Close

strNewText = Replace(strText, "-", "")

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\sql\students.txt", ForWriting)

objFile.WriteLine strNewText

objFile.Close

 

'----------------------------------------------------------------------------------------------

'Deletes Blank Lines

 

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set objFile = objFSO.OpenTextFile("C:\Scripts\SQL\students.txt", ForReading)

 

Do Until objFile.AtEndOfStream

strLine = objFile.Readline

strLine = Trim(strLine)

If Len(strLine) > 0 Then

strNewContents = strNewContents & strLine & vbCrLf

End If

Loop

 

objFile.Close

 

Set objFile = objFSO.OpenTextFile("C:\Scripts\SQL\students.txt", ForWriting)

objFile.Write strNewContents

objFile.Close

[tom_newton]

The easiest "tidyup" here is to put all your strings into an array, and then iterate over that array with the text deletion. You could actually link it in with your line deletion loop as a second level of "tidy" - an inner loop at "If Len(strLine) > 0" checking to see if any of the strings were present and skipping the addition of the line to your output if so would be teh way forward there.

 

Now, you're not going to want to hear this probably but you needn't actually script this sort of thing because in the unix world there are some great tools for this, and you can actually get them for windows too - grep, awk and sed. Grep is the easiest one to master, and it is easy enough to get it to print all lines in a file which don't match a pattern - so that would be one way to accomplish this without extra scripting work. OTOH, if part of your aim is to improve your scripting abilities, crack on, sir, and we will do our best to help you debug as you tidy

[CyberNerd]

+1 to what Tom said.

sed/grep/awk are a fantastic set of tools which will run on Windows, Unix, Mac, Linux. Once you master them it will open up a world of possibilities and increase your skillsets on multiple platforms simultaneously. a simple replace string works like this in sed:

 

sed -i 's/string1/string2/g' textfile.txt

 

Cygwin

[tom_newton]

Don't think you even need to go as far as cygwin - grep can be had standalone here Grep for Windows

[jmedlin6]

Thanks everyone for pointing me in the right direction.