{"id":64003,"date":"2007-09-18T02:11:00","date_gmt":"2007-09-18T02:11:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2007\/09\/18\/how-can-i-parse-a-comma-separated-values-file\/"},"modified":"2007-09-18T02:11:00","modified_gmt":"2007-09-18T02:11:00","slug":"how-can-i-parse-a-comma-separated-values-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-parse-a-comma-separated-values-file\/","title":{"rendered":"How Can I Parse a Comma-Separated Values File?"},"content":{"rendered":"

\"Hey, \n

Hey, Scripting Guy! I have a text file which consists of individual fields separated by commas. I need to look at the second field in each line and, if it includes the word Everyone<\/I>, I then need to take the sixth field in the line and write that value to a second text file. How can I do that?

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

Hey, RP. You know, a lot of you are probably sitting there thinking, \u201cGee, I wonder what life in the Seattle area is really<\/I> like?\u201d Well, we\u2019ll tell you. This morning the Scripting Guy who writes this column was driving to work when he heard sirens; like most people, he promptly pulled over to the side of the road as both an ambulance and fire truck hurtled into view. As the vehicles got closer, a lady who was stopped at a red light suddenly put the pedal to the metal and zoomed across the intersection, forcing the ambulance to screech to a halt. A close call, but, fortunately, the ambulance and the fire truck were able to keep going.<\/P>\n

Now, Seattle being Seattle, where do you suppose this lady was going, and in such a hurry that she not only ran a red light, but nearly collided with an ambulance and a fire truck in the process? You\u2019re absolutely right: after running a red light and nearly smashing into an ambulance, the lady pulled into the drive-through lane at Starbucks.<\/P>\n

But, then again, you can\u2019t blame her; after all, what are the odds of finding a Starbucks in Seattle? Here\u2019s a little advice for you from the Scripting Guys: any time you see a Starbucks pull in immediately. If you don\u2019t, well, good grief, in that case you might have to drive three or four blocks before you find another one.<\/P>\n\n<\/THEAD>\n\n\n
\n

Geographic note<\/B>. In an eight-block stretch right by the Scripting Home, you\u2019ll find two Starbucks, an independent coffee shop, a coffee cart outside a grocery store, and two of those little drive-through coffee huts set up in parking lots. Oh, and a McDonald\u2019s, which, sells coffee lattes and iced coffees as well as regular coffee.<\/P>\n

Not to mention7-11, which also sells coffee.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Incidentally, we don\u2019t know what the emergency was or where the ambulance was headed. However, we assume it must have been something pretty serious; otherwise the ambulance driver probably would have pulled into Starbucks as well.<\/P>\n

Of course, if you\u2019re like most people, you\u2019re probably tired of hearing about Seattleites and their obsession with coffee. Needles to say, the average person would much rather hear about a script that can parse a comma-separated values file, extract information from selected lines in that file, and then save that extracted information to a new file. Well, you\u2019re in luck. When the lady ran the red light and pulled into Starbucks, the Scripting Guy who writes this column took advantage of the confusion to pull into the drive-through lane of the local scripting store. And, lo and behold, guess what he found there? A script that can parse a comma-separated values file, extract information from selected lines in that file, and then save that extracted information to a new file!<\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. Yes, he was<\/I> lucky to find one of those. Usually those things sell out in no time.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

Here\u2019s the script:<\/P>

Const ForReading = 1<\/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    arrFields = Split(strLine, “,”)<\/p>\n
    If InStr(arrFields(1), “Everyone”) Then\n        strContents = strContents & arrFields(5) & vbCrlf\n    End If\nLoop<\/p>\n
objFile.Close<\/p>\n
Set objFile = objFSO.CreateTextFile(“C:\\Scripts\\Everyone.txt”)\nobjFile.Write strContents<\/p>\n
objFile.Close\n<\/PRE>\n
Before we launch into an explanation of how the script works, let\u2019s take a look at the text file in question (C:\\Scripts\\Test.txt):<\/P>
MBPGPS1PRN-001\\,Administrators,LOCALGROUP,S-1-5-32-544,PRINTSHARE,\\\\MBPGPS1PRN-001\\mbbetsb01,OWNER,,\nMBPGPS1PRN-001,Everyone,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\\\MBPGPS1PRN-001\\mbbetsb01,(+PrR),,\nMBPGPS1PRN-001,Everyone,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\\\MBPGPS1PRN-002\\mbbetsb01,(+PrR),,\nMBPGPS1PRN-001,Administrators,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\\\MBPGPS1PRN-001\\mbbetsb01,(+PrR),,\nMBPGPS2PRN-001,Everyone,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\\\MBPGPS2PRN-001\\mbbet02,(+PrR),,\n<\/PRE>\n
As RP noted, each line in this file is made up of several different fields. We need to look at the second item in each line (e.g., Administrators<\/I>) and determine whether or not that value contains the word Everyone<\/I>. (No, it doesn\u2019t, at least not in line 1.) If the second item does<\/I> contain the word Everyone<\/I> (which is the case for line 2 in the file), then we need to extract the sixth parameter (a printer path like \\\\MBPGPS1PRN-001\\mbbetsb01) and write that information to a second text file.<\/P>\n
Got that? Good. Now let\u2019s talk about the script and how it performs its magic.<\/P>\n
As you can see, we start out by defining a constant named ForReading and assigning it the value 1; we\u2019ll use this constant when we go to open the file Test.txt. Coincidentally enough, we next create an instance of the Scripting.FileSystemObject<\/B>, then use the OpenTextFile<\/B> method to open Test.txt for reading. That\u2019s what these two lines of code are for:<\/P>
Set objFSO = CreateObject(“Scripting.FileSystemObject”)\nSet objFile = objFSO.OpenTextFile(“C:\\Scripts\\Test.txt”, ForReading)\n<\/PRE>\n
Before we can read and process the file line-by-line we have to set up a Do Until loop that runs until the file\u2019s AtEndOfStream<\/B> property is True. (Which is just a hoity-toity way of saying, \u201cKeep reading until you reach the end of the file.\u201d) Inside the loop, the first thing we do is use the ReadLine<\/B> method to read the initial line in the file and then store that value in a variable named strLine:<\/P>
strLine = objFile.ReadLine\n<\/PRE>\n
That brings us to this line of code:<\/P>
arrFields = Split(strLine, “,”)\n<\/PRE>\n
Here we\u2019re using the Split<\/B> function to turn the string value strLine into an array; to be a little more specific, we\u2019re asking the Split function to create a new item for the array arrFields each time Split encounters a comma. Why would we do that? We do that because this gives us an array with the following items:<\/P>\n\n\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
MBPGPS1PRN-001\\<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
Administrators<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
LOCALGROUP<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
S-1-5-32-544<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
PRINTSHARE<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
\\\\MBPGPS1PRN-001\\mbbetsb01<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
OWNER<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Is that worth having? You bet it is; after all, each item in the array corresponds quite nicely with the individual fields in our line of text. As we noted earlier, we need to know if the second field in this line of text has the word Everyone<\/I> in it. Well, now we have a very easy way to check for that:<\/P>
If InStr(arrFields(1), “Everyone”) Then\n<\/PRE>\n
Here we\u2019re using the InStr<\/B> function to determine whether or not the word Everyone<\/I> can be found in the second field of our line of text. Or, more correctly, we\u2019re checking to see if Everyone<\/I> can be found in the second item in our array. And don\u2019t worry, we really are<\/I> checking the second item in the array, despite the syntax arrFields(1)<\/B>. In VBScript, arrays are \u201c0-index.\u201d That means that the first item in the array is assigned the index number 0, while the second item in the array (the item we\u2019re interested in) is assigned the index number 1. Hence the value 1<\/B> in arrFields(1). If we wanted to look at the third<\/I> item in the array, then we\u2019d use the syntax arrFields(2).<\/P>\n
And so on.<\/P>\n
If the target word is not<\/I> found in arrFields(1) then we simply loop around and repeat the process with the next line in the text file. If the target word is<\/I> found, then we execute this line of code:<\/P>
strContents = strContents & arrFields(5) & vbCrlf\n<\/PRE>\n
All we\u2019re doing here is assigning a value to a variable named strContents, a value consisting of the existing value of the variable plus<\/I> the value of the sixth item in the array plus<\/I> a carriage return-linefeed character (vbCrLf). And yes, we specified 5<\/B> as the index number for a very good reason: the sixth item in an array will always have an index number of 5.<\/P>\n
After we\u2019ve read and processed each line in the text file we call the Close<\/B> method to close the file Test.txt. We then use this line of code to create a new text file named C:\\Scripts\\Everyone.txt:<\/P>
Set objFile = objFSO.CreateTextFile(“C:\\Scripts\\Everyone.txt”)\n<\/PRE>\n
As you might have guessed, this is where we plan on writing the data we\u2019ve extracted from Test.txt, something we do by calling the Write<\/B> method, passing the method the variable strContents:<\/P>
objFile.Write strContents\n<\/PRE>\n
If we then close the file Everyone.txt and examine the contents, we should see something that look like this:<\/P>
\\\\MBPGPS1PRN-001\\mbbetsb01\n\\\\MBPGPS1PRN-002\\mbbetsb01\n\\\\MBPGPS2PRN-001\\mbbet02\n<\/PRE>\n
In other words, these are the printers that the Everyone group has access to. (Well, assuming that\u2019s what the file Test.txt is all about.)<\/P>\n
That should do it, RP. Or at least we hope it does; as much as we\u2019d like to stick around and chat about parsing text files, well, we need to get going. Time for a coffee break, you know. <\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. Actually, in Seattle people don\u2019t take coffee breaks; that is, we don\u2019t steal a few moments away from work in order to drink a little coffee. Instead, we take work<\/I> breaks, stealing a few moments away from drinking coffee in order to do a little work.<\/P><\/TD><\/TR><\/TBODY><\/TABLE><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! I have a text file which consists of individual fields separated by commas. I need to look at the second field in each line and, if it includes the word Everyone, I then need to take the sixth field in the line and write that value to a second text file. How […]<\/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":[169,3,4,5],"class_list":["post-64003","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! I have a text file which consists of individual fields separated by commas. I need to look at the second field in each line and, if it includes the word Everyone, I then need to take the sixth field in the line and write that value to a second text file. How […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64003","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=64003"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/64003\/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=64003"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=64003"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=64003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}