How Can I Modify a Text File and Then Extract Selected Lines From the Modified Contents?

ScriptingGuy1

Hey, Scripting Guy! How can I write a script that modifies the contents of a text file and then copies specified lines from the modified contents to a new file?

— EH

Hey, EH. There’s no way to do that. Now go away and leave us alone.

Wait, sorry; we didn’t mean it. As it turns out, the Scripting Guy who writes this column is in a terrible mood this morning. Admittedly, he’s in a terrible mood pretty much every morning, but at least today he has good reason to be grumpy. He’s writing this column on March 1, and when he awoke this morning his house and everything around it was covered with a good two inches of snow. Snow, mind you, and on March 1^st! The Scripting Son has already started high school baseball practice and there are two inches of snow on the ground! The Scripting Guy who writes this column fully intends to sue someone over this. He just has to figure out who he can sue over this.

And yes, we know: people in, say, New York, who recently dealt with several feet of snow, don’t feel too sorry for Seattleites who have to deal with a few piddling inches of snow, snow that’s already beginning to melt even as we speak. But that’s OK. After all, we don’t need anyone’s pity; we have more than enough self-pity to go around. In the past few months we’ve had ice storms; power failures (leaving the Scripting Guy who writes this column without electricity for 6 long, cold, and very dark days); more ice storms; and now snow on March 1^st. What did we ever do to deserve all that?

OK, we can’t argue with you there. But surely the entire Seattle area shouldn’t have to pay for the sins of one Scripting Guy.

Oh, well. At any rate, we apologize for starting this column out on such a rude note. Tell you what, EH: is there some way we can make this up to you? What’s that? Show you how to write a script that modifies the contents of a text file and then copies specified lines from the modified contents to a new file? Hmmm; we never would of have thought of that. OK, EH; here you go:

Const ForReading = 1

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)
strContents = objFile.ReadAll
objFile.Close
strContents = Replace(strContents, “~”, “~” & vbCrLf)
arrLines =Split(strContents, vbCrLf)
For Each strLine in arrLines
    If Left(strLine, 2) = “N9” or Left(strLine, 2) = “B4” Then
        strNewFile = strNewFile & strLine & vbCrLf
    End If
Next
Set objFile = objFSO.CreateTextFile(“C:\Scripts\Test2.txt”)
objFile.Write strNewFile
objFile.Close

And now can we explain how the script works? How in the world are we supposed to know how the script works?!? Now go away and leave us alone.

Sorry; guess we aren’t quite back to our usual jolly and carefree selves here. Sure, we can explain how this script works. As you can see, we start off by creating a constant named ForReading and setting the value to 1; we’ll use this constant when we open our text file. In fact, we’ll use this constant in the following block of code, code that creates an instance of the Scripting.FileSystemObject and then uses the OpenTextFile method to open the file C:\Scripts\Test.txt:

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFile = objFSO.OpenTextFile(“C:\Scripts\Test.txt”, ForReading)

As you noted in your email, EH, you have a crazy text file that looks something like this, with everything on one big long line:

*070219*1347*U*00401*000000852*0*P*>~GS*QO*CMACGM*USALTLANP*20070219*1347*85
2*X*004010~ST*315*0001~B4***CT*20070219*0840*1703*CAXU*983290*L*4510~N9*BM*SZ2
711711~N9*BN*SHZ438713~N9*SN*C4025491~Q2*9227027*****166***PX311E***L*CSCL
DALIAN~R4*L*K*57078*YANTIAN~DTM*140*20070119*1200~R4*D*D*1703*SAVANNAH, GA~

You’d like to read in this text file and do two things. First, you’d like to put a carriage return-linefeed after each tilde character (~); that will give you lines that look like this:

*070219*1347*U*00401*000000852*0*P*>~
GS*QO*CMACGM*USALTLANP*20070219*1347*852*X*004010~
ST*315*0001~
B4***CT*20070219*0840*1703*CAXU*983290*L*4510~
N9*BM*SZ2711711~
N9*BN*SHZ438713~
N9*SN*C4025491~
Q2*9227027*****166***PX311E***L*CSCL DALIAN~
R4*L*K*57078*YANTIAN~
DTM*140*20070119*1200~
R4*D*D*1703*SAVANNAH, GA~

After that you’d then like to identify any lines that begin with N9 or B4 and copy those lines to a separate text file. Sounds crazy, doesn’t it? But if it’s crazy you want, well, you came to the right place.

To begin with, we use the ReadAll method to read the entire file into memory and store the contents in a variable named strContents:

strContents = objFile.ReadAll

We then immediately close the file C:\Scripts\Test.txt. Why? Because we don’t need it anymore; we’ll do all our work with the variable strContents instead.

For starters, let’s put a carriage return-linefeed after each tilde in the file. How are we going to do that? Like this:

strContents = Replace(strContents, “~”, “~” & vbCrLf)

As you can see, we simply use the Replace function to replace each tilde character with two characters: a tilde and a carriage return-linefeed (as indicated by the VBScript constant vbCrLf). The net effect: the tildes remain exactly where they are, only there’s now a carriage return-linefeed after each one.

Of course, something else has happened here as well: our text file (or at least the virtual copy of that file) now has multiple lines thanks to the carriage return-linefeed. And because it does have multiple lines we can go ahead and extract all the lines that begin with N9 or B4.

In order to do that we first use the Split function to split the variable strContents on the carriage return-linefeed character:

arrLines =Split(strContents, vbCrLf)

That’s going to result in an array (arrLines) in which each element represents a line in the text file. In turn, that means we can now “read” each line in the file simply by setting up a For Each loop that walks through all the items in the array:

For Each strLine in arrLines

So what are we going to do inside that loop? Well, for each array item we’re going to check to see if the first two characters are either N9 or B4; that’s what this line of code, and the Left function, are for:

If Left(strLine, 2) = “N9” or Left(strLine, 2) = “B4” Then

Let’s suppose we find one of the target lines. In that case, we simply take that value and append it (plus a carriage return-linefeed character) to a variable named strNewFile:

strNewFile = strNewFile & strLine & vbCrLf

And then we loop around and repeat this process with the next item in the array.

Once we’ve finished looping through the array we then execute this block of code:

Set objFile = objFSO.CreateTextFile(“C:\Scripts\Test2.txt”)
objFile.Write strNewFile
objFile.Close

All we’re doing here is creating a new file named C:\Scripts\Test2.txt; that’s what we do with the CreateTextFile method. We use the Write method to write the value of strNewFile to Test2.txt, then close the file and call it good. If we then open Test2.txt we should see this:

B4***CT*20070219*0840*1703*CAXU*983290*L*4510~
N9*BM*SZ2711711~
N9*BN*SHZ438713~
N9*SN*C4025491~

Which, crazy as it might seem, is exactly what we wanted to see.

Now, if you’ll excuse us, we’re going to go back to being grumpy and feeling sorry for ourselves. Admittedly, right now the sun is peeking through the clouds, and the snow is rapidly melting. But we’re not going to fall for that old trick; the sun coming out simply means that we’ll have a nice clear view of the next disaster to strike the Seattle area. We’re betting on frogs or locusts; it’s been awhile since we’ve had a plague of frogs or locusts around here. We’ll let you know.

ScriptingGuy1

Follow