Hey, Scripting Guy! How Can I Replace a Series of Variables Found in a Text File with Values Taken From That File?

ScriptingGuy1

Hey, Scripting Guy! I have a text file containing TSQL syntax. In this file are a number of variables: @P1, @P2, @p3, and so on, up through @P58. The bottom line of the text file contains replacement values for these variables; for example, I need to take the variable @P1 and replace it with the first value in the last line; I need to replace @P2 with the second value in the last line; etc. How can I do that?

— AG

Hey, AG. You know what you need here? You need some “concentrated water.” That’s right: concentrated water. Apparently concentrated water has been around for a few years now although, as usual, the Scripting Guy who writes this column is a bit late to the party. (Actually, the Scripting Guy who writes this column never even gets invited to the party, but that’s another story.) Concentrated water: because who would drink water that hasn’t been restructured through a system of centrifugal vortex and magnetic resonance?

Good question: what exactly is concentrated water? To tell you the truth, we weren’t sure. Therefore, we did a Goo – um, an Internet search and managed to find the following explanation. It turns out that concentrated water:

“… contains the energetic properties needed to optimize the body’s hydration and detoxification capabilities. Its proprietary nature-based technology is a unique energetic process that duplicates the flow dynamics, polarity, vibrational strength and subtle energetic qualities produced by nature under ideal spring water conditions. It contains the full range of frequencies found in the human energy field, along with a naturally strong polarity, which combine to tune and raise the vibrations of the body’s energy field every time you drink Hydra water.”

Needless to say, that cleared things up.

Anyway, the cool thing about concentrated water is this: although you can drink it straight from the bottle ($9.95 for a 14-ounce bottle), the recommended way to use concentrated water is to dilute it. And how do you dilute it? Why, by adding water to it, of course. In other words, concentrated water is water that you need to add water to.

Good point: why didn’t’ the Scripting Guys think of that?

But that’s neither here nor there; the important question is whether or not concentrated water would be of any use in replacing values in a text file? To try and answer that question, let’s first take a look at the text file (which we’ve simplified a bit). The file looks something like this:

select pilot.dbo.CUSTOMERS.CUSTNAME , case when ( ( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) = 
rtrim(ltrim(reverse( @P1 ))) ) ) then ( pilot.dbo.CUSTOMERS.CUSTDES ) else 
( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) ) end , pilot.dbo.ORDERS.CURDATE , pilot.dbo.ORDERS.ORDNAME , 
pilot.dbo.ORDERS.BOOKNUM , pilot.dbo.DOCUMENTS.DOCNO , pilot.dbo.ORDSTATUS.ORDSTATUSDES , 
pilot.dbo.ORDSTATUS.XVR_GRADING , case when ( ( pilot.dbo.ORDERS.CLOSED = @P2 ) ) then ( @P3 ) else ( @P4 ) 
end , case when ( ( pilot.dbo.ORDERS.PCLOSED = @P5 ) ) then (@P6 ) else ( @P7 ) end , pilot.dbo.CPROF.CPROFNUM , 
pilot.dbo.DEAL.ORDNAME 

'','C','Y',' ','C','Y',' '

What AG needs to do is take the comma-separated values on the last line and use those as replacements for the variable values (anything starting with @P) in the main body of his text file. For example, part of AG’s TSQL text looks like this:

rtrim(ltrim(reverse( @P1 )

What he wants to do is replace @P1 with the first value in the last line of the text file (‘ ‘). In other words, his new-and-improved TSQL syntax needs to look like this:

rtrim(ltrim(reverse( '' )

See how that works?

Now, it may very well be the case that AG could solve his problem by dunking his computer in a bucket of concentrated water; needless to say, a lot of life’s problems could be solved by dunking your computer in a bucket of concentrated water (or by throwing that computer out the window). However, we can’t be sure about that, and we don’t really recommend you try it. (At the very least, don’t dunk your own computer in a bucket of water; try using someone else’s computer first.) Because of that, we recommend that you use the following script insteadinstead:

Const ForReading = 1
Const ForWriting = 2

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

strContents = objFile.ReadAll
objFile.Close

arrContents = Split(strContents, vbCrlf)
intUpper = Ubound(arrContents)
arrReplacements = Split(arrContents(intUpper), ",")

i = 1

For Each strReplacement in arrReplacements
    strTarget = "@P" & i
    strContents = Replace(strContents, strTarget, strReplacement)
    i = i + 1
Next

Set objFile = objFSO.OpenTextFile("C:\Scripts\Test.txt", ForWriting)
objFile.WriteLine strContents
objFile.Close

OK, let’s see what we have here. To begin with, we define a pair of constants (ForReading and ForWriting), constants that we’ll need to open our text file. (Why do we need two constants? Because we’re going to have to open the text file two times: once to read from it, and once to write to it.) After defining the constants we create an instance of the Scripting.FileSystemObject, then use 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)

Just a second here; our vibrational frequencies seem to be a little out-of-whack.

There, that’s better. So what are we going to do with this file now that it’s open? That’s easy: we’re going to use the ReadAll method to read the entire contents of the file into a variable named strContents, then we’re going to immediately close the file.

As it turns out, the FileSystemObject (as much as we love it) has at least one more eccentricity: there’s no straightforward way to read the last line – and only the last line – in a file. Most of the time that’s no problem. In this case, however, it is a problem: after all, AG’s replacement values are all found in the last line of the text file. So what are we going to do about that? Well, we’re going to execute this block of code:

arrContents = Split(strContents, vbCrlf)
intUpper = Ubound(arrContents)
arrReplacements = Split(arrContents(intUpper), ",")

So how does this chunk of code let us read the last line in the file? Well, to begin with, we use the Split function to split the contents of the file into an array named arrContents; by splitting this value on the carriage return-linefeed character (vbCrLf) we end up creating an array in which each and every element in that array happens to be a line in the text file. As soon as we’ve created this array, we can then use the Ubound function to determine the index number of the very last item in that array (which, needless to say, is also the last line in the text file).

So then what’s this line of code for?

arrReplacements = Split(arrContents(intUpper), ",")

Well, as you recall, the last line in the text file is a set of replacement values; that last line looks like this:

'','C','Y',' ','C','Y',' '

All we’re doing here is using the Split function to create another array, this one composed of the individual items found in the last line of the file. By splitting on the comma, we end up with an array named arrReplacements that looks like:

''
'C'
'Y'
' '
'C'
'Y'
' '

Note. In case you’re wondering, yes, we do need to keep the single quote marks.

Believe it or not, we’re now ready to start replacing variables with values (or at least we will be after we set the value of a counter variable named i to 1). To carry out this task, the first thing we do is set up a For Each loop that will loop us through all the values in the variable arrReplacements. Inside that loop, the first thing we do is execute this line of code:

strTarget = "@P" & i

What we’re doing here is simply constructing the target text that needs to be replaced. As AG noted in his email, his text file contains a series of variables that look like this: @P1; @P2; @P3; etc. With this line of code we’re simply constructing the first of these variables, combining the string value @P with the value of the counter variable i. Because i is currently equal to 1, that’s going to make our target text equal to this: @P1. See how that works?

As soon as we’ve done that we can then use the Replace function to search our text file (or, more correctly, the virtual copy of the text file stored in the variable strContents) for the target text, and the replace that value with the first value in the array arrReplacements. That’s what this line of code is for:

strContents = Replace(strContents, strTarget, strReplacement)

From there we increment the value of the counter variable i by 1, then go back to the top of the loop and try again, this time replaced the variable @P2 with the second value found in the array arrReplacements.

Etc., etc.

After all the replacements have been made we reopen Test.txt (this time for writing), then use the WriteLine method to replace the existing contents of the file with the value of the variable strContents:

objFile.WriteLine strContents

We then close the file, and call it good.

Incidentally, here’s what the data section of Test.txt will now look like:

select pilot.dbo.CUSTOMERS.CUSTNAME , case when ( ( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) 
= rtrim(ltrim(reverse( '' ))) ) ) then ( pilot.dbo.CUSTOMERS.CUSTDES ) else 
( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) ) end , pilot.dbo.ORDERS.CURDATE , pilot.dbo.ORDERS.ORDNAME , 
pilot.dbo.ORDERS.BOOKNUM , pilot.dbo.DOCUMENTS.DOCNO , pilot.dbo.ORDSTATUS.ORDSTATUSDES , 
pilot.dbo.ORDSTATUS.XVR_GRADING , case when ( ( pilot.dbo.ORDERS.CLOSED = 'C' ) ) then ( 'Y' ) else ( ' ' ) 
end , case when ( ( pilot.dbo.ORDERS.PCLOSED = 'C' ) ) then ('Y' ) else ( ' ' ) end , pilot.dbo.CPROF.CPROFNUM , 
pilot.dbo.DEAL.ORDNAME

Not bad, huh?

That should do it, AG; as always, it turns out that the best way to get something done is to go ahead and do it yourself. So does that apply to making your own concentrated water as well? You bet it does. After all, why pay $9.95 for a 14-ounce bottle of concentrated water when you can make your own? All you have to do is take regular tap water and restore the super high exahertz and nanotesla range frequencies. Season with salt and pepper, and then stir well.

ScriptingGuy1

Follow