{"id":64623,"date":"2007-06-20T00:08:00","date_gmt":"2007-06-20T00:08:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/06\/20\/how-can-i-insert-commas-at-specified-locations-in-each-line-in-a-text-file\/"},"modified":"2007-06-20T00:08:00","modified_gmt":"2007-06-20T00:08:00","slug":"how-can-i-insert-commas-at-specified-locations-in-each-line-in-a-text-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-insert-commas-at-specified-locations-in-each-line-in-a-text-file\/","title":{"rendered":"How Can I Insert Commas at Specified Locations in Each Line in a Text File?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! How can I insert commas at specified locations in each line in a text file?

— MM <\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

Hey, MM. Seeing as how Father\u2019s Day has just come and gone we couldn\u2019t talk about inserting commas into text files without first relating a heartwarming Father\u2019s Day tale of our own. On Father\u2019s Day the Scripting Guy who writes this column went to the gym to play basketball with the Scripting Son. At one point the ball took a funny bounce off the rim and skipped out towards half court. \u201cHey, Dad,\u201d said the Scripting Son. \u201cWatch this.\u201d Without turning around he grabbed the ball and flung it back over his head, ostensibly towards the basket.<\/P>\n

Now, had the Scripting Son made the basket that would have been pretty impressive; nevertheless, it wouldn\u2019t have been anywhere near as cool as what happened instead. As it turns out, the gym the duo plays at has a small bathroom at one end, and the last person to use this bathroom neglected to close the door. When the Scripting Son flung the ball over his head, the ball zoomed through the bathroom door, rattled off the sink a few times, and then landed in the toilet.<\/P>\n

You couldn\u2019t have asked for a better Father\u2019s Day present. Or at least not a funnier one. <\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. For those of you who keep track of this sort of thing, that ranks as the Scripting Son\u2019s second-greatest basketball achievement. Earlier this year he took an ill-advised, off-balance jump shot from behind the three-point line. The shot was way off-target; in fact, it sailed completely over the backboard \u2026 at which point it hit the wall, caromed off the supports holding up the basket, then somehow bounced back over top of the backboard and dropped down through the hoop. Three points for the Scripting Son.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Admittedly, it\u2019s a bit difficult, if not downright impossible, to top a basketball landing in a toilet. But if anything can<\/I> top a basketball in the toilet, it would have to be a script that inserts commas at specified locations in each line in a text file:<\/P>

Const ForReading = 1\nConst ForWriting = 2<\/p>\n
arrCommas = Array(2,7,11,17,18,20)<\/p>\n
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)<\/p>\n
Do Until objFile.AtEndOfStream\n    strLine = objFile.ReadLine\n    intLength = Len(strLine)\n    For Each strComma in arrCommas\n        strLine = Left(strLine, strComma – 1) + “,” + Mid(strLine, strComma, intLength)\n    Next\n    strText = strText & strLine & vbCrLf\nLoop<\/p>\n
objFile.Close<\/p>\n
Set objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForWriting)\nobjFile.Write strText\nobjFile.Close\n<\/PRE>\n
So how does this all work? That\u2019s a good question. Let\u2019s see if we can figure out what\u2019s going on here.<\/P>\n
We actually start out simply enough, defining a pair of constants (ForReading and ForWriting) that we\u2019ll need when it comes time to open our text file. (And yes, we need two constants because we have to open the text file twice: once to read in the existing contents and once to write the revised contents back to the file.) We then use this line of code to create an array named arrCommas:<\/P>
arrCommas = Array(2,7,11,17,18,20)\n<\/PRE>\n
Why did we assign these particular numbers to the array? That\u2019s easy: these values represent the character positions where we want to insert our commas. For example, suppose our text file looks like this:<\/P>
ABBBBCCCDDDDDEFFFFFFFFFFFFF\nGHHHHIIIJJJJJKLLLLLLLLLLLLL\nMNNNNOOOPPPPPQRRRRRRRRRRRRR\n<\/PRE>\n
We want to insert commas in character positions 2, 7, 11, 17, 18, and 20. That means that, when the script finishes, the text file will look like this:<\/P>
A,BBBB,CCC,DDDDD,,E,FFFFFFFFFFFFF\nG,HHHH,III,JJJJJ,,K,LLLLLLLLLLLLL\nM,NNNN,OOO,PPPPP,,Q,RRRRRRRRRRRRR\n<\/PRE>\n
The only way to achieve that result is to put commas in the proper spots:<\/P>\n\n<\/THEAD>\n\n\n\n
\n
1<\/P><\/TD>\n
\n
2<\/P><\/TD>\n
\n
3<\/P><\/TD>\n
\n
4<\/P><\/TD>\n
\n
5<\/P><\/TD>\n
\n
6<\/P><\/TD>\n
\n
7<\/P><\/TD>\n
\n
8<\/P><\/TD>\n
\n
9<\/P><\/TD>\n
\n
10<\/P><\/TD>\n
\n
11<\/P><\/TD>\n
\n
12<\/P><\/TD>\n
\n
13<\/P><\/TD>\n
\n
14<\/P><\/TD>\n
\n
15<\/P><\/TD>\n
\n
16<\/P><\/TD>\n
\n
17<\/P><\/TD>\n
\n
18<\/P><\/TD>\n
\n
19<\/P><\/TD>\n
\n
20<\/P><\/TD>\n
\n
21<\/P><\/TD><\/TR>\n
\n
A<\/P><\/TD>\n
\n
,<\/P><\/TD>\n
\n
B<\/P><\/TD>\n
\n
B<\/P><\/TD>\n
\n
B<\/P><\/TD>\n
\n
B<\/P><\/TD>\n
\n
,<\/P><\/TD>\n
\n
C<\/P><\/TD>\n
\n
C<\/P><\/TD>\n
\n
C<\/P><\/TD>\n
\n
,<\/P><\/TD>\n
\n
D<\/P><\/TD>\n
\n
D<\/P><\/TD>\n
\n
D<\/P><\/TD>\n
\n
D<\/P><\/TD>\n
\n
D<\/P><\/TD>\n
\n
,<\/P><\/TD>\n
\n
,<\/P><\/TD>\n
\n
E<\/P><\/TD>\n
\n
,<\/P><\/TD>\n
\n
F<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
You get the idea.<\/P>\n
The next part is also easy; we create an instance of the Scripting.FileSystemObject<\/B> object and then use the OpenTextFile<\/B> method to open the file C:\\Scripts\\Test.txt for reading:<\/P>
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)\n<\/PRE>\n
Now it starts to get a little tricky.<\/P>\n
For starters, we set up a Do Until loop that runs until the file\u2019s AtEndOfStream<\/B> property is True (which simply means that we want to keep reading until there\u2019s nothing left to read). Inside this loop we use the ReadLine<\/B> method to read the first line from the file and store it in a variable named strLine:<\/P>
strLine = objFile.ReadLine\n<\/PRE>\n
OK, so maybe that wasn\u2019t so tricky after all. But that\u2019s about to change. Our next step is to use the Len<\/B> function to determine the number of characters in the variable strLine:<\/P>
intLength = Len(strLine)\n<\/PRE>\n
Why do we do that? Just be patient; we\u2019ll explain that in a second.<\/P>\n
Once we know the length of the line we then set up a For Each loop, a loop that runs through all the items in the array arrCommas:<\/P>
For Each strComma in arrCommas\n<\/PRE>\n
And what do you suppose we do inside that loop? This is what we do inside that loop:<\/P>
strLine = Left(strLine, strComma – 1) + “,” + Mid(strLine, strComma, intLength)\n<\/PRE>\n
As it turns out, this<\/I> is the tricky part. What we\u2019re doing here is inserting all the required commas into the required places. Why is that so tricky? Mainly due to the fact that there\u2019s no command we can call that will simply insert commas in the designated spaces. Instead, we have to do this comma-by-comma, character-by-character. For example, the very first thing we need to do is insert a comma in character position 2. How are we going to do that? Well, first we need to grab all the characters up to character position 2 (in other words, we need to grab the first character). That\u2019s what this piece of code is for:<\/P>
Left(strLine, strComma – 1)\n<\/PRE>\n
All we\u2019re doing here is using the Left<\/B> function to grab the first x<\/I> number of characters from the variable strLine. What is x equal to? x is equal to the first value in the array (2) minus<\/I> 1. And why minus 1? If you think about it for a second, you\u2019ll see why we need to specify the minus 1. If we want our comma to hold down position 2 that means that we can only grab one character; if we took the first 2<\/I> characters then the comma would actually be in position 3<\/I>. And who wants a comma in position 3? Maybe someone else does, but not us.<\/P>\n
Therefore, we take the first 2 \u2013 1 characters and stash them (or, rather it: that\u2019s simply going to bet the letter A<\/I>) in the variable strLine. We then use this bit of code to tack a comma onto the end of strLine:<\/P>
+ “,”\n<\/PRE>\n
In turn, that makes the variable strLine equal to this:<\/P>
A,\n<\/PRE>\n
That\u2019s nice; we now have a comma in character position 2. But what about the rest<\/I> of the line? Well, that\u2019s what this piece of code is for:<\/P>
+ Mid(strLine, strComma, intLength)\n<\/PRE>\n
Here we\u2019re using the Mid<\/B> function to grab the remaining characters in the line. When you use the Mid function you need to specify three things: the string value you are working with (the variable strLine); the starting position in the string; and the ending position in the string. In this case we want to start with character position 2, the second character in the string. Fortunately, that\u2019s easy to do, because strComma just happens to equal 2. (Remember, we\u2019re not dealing with the revised string, we\u2019re dealing with the actual line of text read in from the text file, a line of text that doesn\u2019t<\/I> have a comma in character position2). As for the ending position, we want to grab all<\/I> the remaining characters. How many characters is that? To tell you the truth, we don\u2019t know. But that\u2019s fine: after all, we\u2019ve stored that value in the variable intLength. (That\u2019s why we needed to determine the length of the string.)<\/P>\n
What does all that mean? That means we\u2019re going to grab the following set of characters:<\/P>
BBBBCCCDDDDDEFFFFFFFFFFFFF\n<\/PRE>\n
And when everything gets added together, that means that strLine will be assigned the following value:<\/P>
A,BBBBCCCDDDDDEFFFFFFFFFFFFF\n<\/PRE>\n
That\u2019s really nothing special; it\u2019s just the first line of text, albeit with a comma inserted in character position 2. Now we\u2019re going to loop around and repeat this process with the next item in the array arrCommas. Because that item happens to be the number 7, we\u2019ll end up inserting a comma in character position 7, making our string equal to this:<\/P>
A,BBBB,CCCDDDDDEFFFFFFFFFFFFF\n<\/PRE>\n
And then we\u2019ll loop around and repeat this process with all the remaining items in the array, slowly but surely inserting all the commas in their appointed palces.<\/P>\n
After all the commas have been inserted we then execute this line of code:<\/P>
strText = strText & strLine & vbCrLf\n<\/PRE>\n
That line simply takes the variable strText and assigns it the existing value of strText plus<\/I> the recently-revised line (strLine) plus<\/I> a carriage return-linefeed. From there we loop around, read in the second line of text, and start inserting commas in that line, eventually appending that value to the variable strText.<\/P>\n
Sooner or later, we\u2019ll finish reading all the lines in the text file. At that point we close the file and then reopen it, this time for writing:<\/P>
Set objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForWriting)\n<\/PRE>\n
Once the file is open we use the Write<\/B> method to overwrite the existing contents of Test.txt with the value of strText, then call the Close<\/B> method and close the file once and for all:<\/P>
objFile.Write strText\nobjFile.Close\n<\/PRE>\n
The net result? A text file that looks like this:<\/P>
A,BBBB,CCC,DDDDD,,E,FFFFFFFFFFFFF\nG,HHHH,III,JJJJJ,,K,LLLLLLLLLLLLL\nM,NNNN,OOO,PPPPP,,Q,RRRRRRRRRRRRR\n<\/PRE>\n
A little tricky, perhaps, but still a task we can accomplish with just a few lines of code.<\/P>\n
Incidentally, to the best of his recollection the Father\u2019s Day game marked the first time the Scripting Guy who writes this column has ever seen a basketball land in a toilet. (Somewhat surprising to those who\u2019ve heard about the weird competitions the Scripting Dad and Scripting Son have engaged in over the years.) As unusual as that might have been, however, the Scripting Guy who writes this column wasn\u2019t really fazed by seeing a basketball land in the toilet; after all, his Microsoft career landed there a long, long time ago.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. If you\u2019ve always assumed that the Scripting Guys were at the top<\/I> of the Microsoft org chart, well \u2026.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I insert commas at specified locations in each line in a text file?— MM Hey, MM. Seeing as how Father\u2019s Day has just come and gone we couldn\u2019t talk about inserting commas into text files without first relating a heartwarming Father\u2019s Day tale of our own. On Father\u2019s Day the […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[3,4,14,5],"class_list":["post-64623","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I insert commas at specified locations in each line in a text file?— MM Hey, MM. Seeing as how Father\u2019s Day has just come and gone we couldn\u2019t talk about inserting commas into text files without first relating a heartwarming Father\u2019s Day tale of our own. On Father\u2019s Day the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=64623"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64623\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=64623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}