![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n <\/p>\n
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?<\/p>\n
— AG<\/p>\n
Hey, AG. You know what you need here? You need some \u201cconcentrated water.\u201d That\u2019s 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<\/i> to the party, but that\u2019s another story.) Concentrated water: because who would drink water that hasn\u2019t<\/i> been restructured through a system of centrifugal vortex and magnetic resonance?<\/p>\n Good question: what exactly is<\/i> concentrated water? To tell you the truth, we weren\u2019t sure. Therefore, we did a Goo \u2013 um, an Internet<\/i> search and managed to find the following explanation. It turns out that concentrated water:<\/p>\n \u201c\u2026 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.\u201d<\/p>\n Needless to say, that cleared things up.<\/p>\n Anyway, the cool thing about concentrated water is this: although you can<\/i> 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.<\/p>\n Good point: why didn\u2019t\u2019<\/i> the Scripting Guys think of that? <\/p>\n But that\u2019s 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\u2019s first take a look at the text file (which we\u2019ve simplified a bit). The file looks something like this:<\/p>\n 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<\/b>) in the main body of his text file. For example, part of AG\u2019s TSQL text looks like this:<\/p>\n What he wants to do is replace @P1 with the first value in the last line of the text file (\u2018 \u2018<\/b>). In other words, his new-and-improved TSQL syntax needs to look like this:<\/p>\n See how that works?<\/p>\n 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<\/i> of life\u2019s 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\u2019t be sure about that, and we don\u2019t really recommend you try it. (At the very least, don\u2019t dunk your own computer in a bucket of water; try using someone else\u2019s computer first.) Because of that, we recommend that you use the following script insteadinstead:<\/p>\n OK, let\u2019s see what we have here. To begin with, we define a pair of constants (ForReading and ForWriting), constants that we\u2019ll need to open our text file. (Why do we need two<\/i> constants? Because we\u2019re 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<\/b>, then use the OpenTextFile<\/b> method to open the file C:\\Scripts\\Test.txt:<\/p>\n Just a second here; our vibrational frequencies seem to be a little out-of-whack.<\/p>\n There, that\u2019s better. So what are we going to do with this file now that it\u2019s open? That\u2019s easy: we\u2019re going to use the ReadAll<\/b> method to read the entire contents of the file into a variable named strContents, then we\u2019re going to immediately close the file.<\/p>\n Note<\/b>. What\u2019s the point of opening the file and then immediately closing it again? Well, ideally, we\u2019d be able to open the file and modify the contents as we go. For better or worse, however, the FileSystemObject won\u2019t let us do that. Instead, we need to open the file, read in the contents, and then close the file; we then modify the \u201cvirtual\u201d contents of the file in memory. As soon as our changes are complete we reopen the file, this time for writing, and replace the existing contents with our new, modified contents.<\/p>\n And no, we aren\u2019t sure why the FileSystemObject works that way. We\u2019re assuming it has something to do with the inverse hydrochloric polarity of the vibrational calculometer built into the FileSystemObject. But we could be mistaken about that.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n As it turns out, the FileSystemObject (as much as we love it) has at least one more eccentricity: there\u2019s no straightforward way to read the last line \u2013 and only the last line \u2013 in a file. Most of the time that\u2019s no problem. In this case, however, it is<\/i> a problem: after all, AG\u2019s replacement values are all found in the last line of the text file. So what are we going to do about that? Well, we\u2019re going to execute this block of code:<\/p>\n So how does this chunk of code let us read the last line in the file? Well, to begin with, we use the Split<\/b> function to split the contents of the file into an array named arrContents; by splitting this value on the carriage return-linefeed character (vbCrLf<\/b>) 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\u2019ve created this array, we can then use the Ubound<\/b> 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).<\/p>\n So then what\u2019s this<\/i> line of code for?<\/p>\n Well, as you recall, the last line in the text file is a set of replacement values; that last line looks like this:<\/p>\n All we\u2019re 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:<\/p>\n Note<\/b>. In case you\u2019re wondering, yes, we do need to keep the single quote marks.<\/p>\n <\/p>\n Believe it or not, we\u2019re 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:<\/p>\n What we\u2019re 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\u2019re simply constructing the first of these variables, combining the string value @P<\/b> with the value of the counter variable i. Because i is currently equal to 1, that\u2019s going to make our target text equal to this: @P1<\/b>. See how that works?<\/p>\n As soon as we\u2019ve done that we can then use the Replace<\/b> 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\u2019s what this line of code is for:<\/p>\n 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.<\/p>\n Etc., etc.<\/p>\n After all the replacements have been made we reopen Test.txt (this time for writing), then use the WriteLine<\/b> method to replace the existing contents of the file with the value of the variable strContents:<\/p>\n We then close the file, and call it good.<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a> <\/p>\nselect pilot.dbo.CUSTOMERS.CUSTNAME , case when ( ( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) = \nrtrim(ltrim(reverse( @P1 ))) ) ) then ( pilot.dbo.CUSTOMERS.CUSTDES ) else \n( coalesce( pilot.dbo.NSCUST.CUSTDES , '''' ) ) end , pilot.dbo.ORDERS.CURDATE , pilot.dbo.ORDERS.ORDNAME , \npilot.dbo.ORDERS.BOOKNUM , pilot.dbo.DOCUMENTS.DOCNO , pilot.dbo.ORDSTATUS.ORDSTATUSDES , \npilot.dbo.ORDSTATUS.XVR_GRADING , case when ( ( pilot.dbo.ORDERS.CLOSED = @P2 ) ) then ( @P3 ) else ( @P4 ) \nend , case when ( ( pilot.dbo.ORDERS.PCLOSED = @P5 ) ) then (@P6 ) else ( @P7 ) end , pilot.dbo.CPROF.CPROFNUM , \npilot.dbo.DEAL.ORDNAME \n\n'','C','Y',' ','C','Y',' '\n<\/pre>\n
rtrim(ltrim(reverse( @P1 )<\/pre>\n
rtrim(ltrim(reverse( '' )<\/pre>\n
Const ForReading = 1\nConst ForWriting = 2\n\nSet objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\n\nstrContents = objFile.ReadAll\nobjFile.Close\n\narrContents = Split(strContents, vbCrlf)\nintUpper = Ubound(arrContents)\narrReplacements = Split(arrContents(intUpper), \",\")\n\ni = 1\n\nFor Each strReplacement in arrReplacements\n strTarget = \"@P\" & i\n strContents = Replace(strContents, strTarget, strReplacement)\n i = i + 1\nNext\n\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForWriting)\nobjFile.WriteLine strContents\nobjFile.Close\n<\/pre>\n
Set objFSO = CreateObject(\"Scripting.FileSystemObject\")\nSet objFile = objFSO.OpenTextFile(\"C:\\Scripts\\Test.txt\", ForReading)\n<\/pre>\n
\n<\/thead>\n\n
\n \n <\/div>\narrContents = Split(strContents, vbCrlf)\nintUpper = Ubound(arrContents)\narrReplacements = Split(arrContents(intUpper), \",\")\n<\/pre>\n
arrReplacements = Split(arrContents(intUpper), \",\")<\/pre>\n
'','C','Y',' ','C','Y',' '<\/pre>\n
''\n'C'\n'Y'\n' '\n'C'\n'Y'\n' '\n<\/pre>\n
strTarget = \"@P\" & i<\/pre>\n
strContents = Replace(strContents, strTarget, strReplacement)<\/pre>\n
objFile.WriteLine strContents<\/pre>\n