How Can I Parse a Comma-Separated Values File?

How Can I Parse a Comma-Separated Values File?

  • Comments 3
  • Likes
Hey, Scripting Guy! Question

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 can I do that?

-- RP

SpacerHey, Scripting Guy! AnswerScript Center

Hey, RP. You know, a lot of you are probably sitting there thinking, “Gee, I wonder what life in the Seattle area is really like?” Well, we’ll 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.

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’re absolutely right: after running a red light and nearly smashing into an ambulance, the lady pulled into the drive-through lane at Starbucks.

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

Geographic note. In an eight-block stretch right by the Scripting Home, you’ll 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’s, which, sells coffee lattes and iced coffees as well as regular coffee.

Not to mention7-11, which also sells coffee.

Incidentally, we don’t 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.

Of course, if you’re like most people, you’re 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’re 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!

Note. Yes, he was lucky to find one of those. Usually those things sell out in no time.

Here’s the script:

Const ForReading = 1

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

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    arrFields = Split(strLine, ",")

    If InStr(arrFields(1), "Everyone") Then
        strContents = strContents & arrFields(5) & vbCrlf
    End If
Loop

objFile.Close

Set objFile = objFSO.CreateTextFile("C:\Scripts\Everyone.txt")
objFile.Write strContents

objFile.Close

Before we launch into an explanation of how the script works, let’s take a look at the text file in question (C:\Scripts\Test.txt):

MBPGPS1PRN-001\,Administrators,LOCALGROUP,S-1-5-32-544,PRINTSHARE,\\MBPGPS1PRN-001\mbbetsb01,OWNER,,
MBPGPS1PRN-001,Everyone,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\MBPGPS1PRN-001\mbbetsb01,(+PrR),,
MBPGPS1PRN-001,Everyone,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\MBPGPS1PRN-002\mbbetsb01,(+PrR),,
MBPGPS1PRN-001,Administrators,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\MBPGPS1PRN-001\mbbetsb01,(+PrR),,
MBPGPS2PRN-001,Everyone,GLOBALGROUP,S-1-1-0,PRINTSHARE,\\MBPGPS2PRN-001\mbbet02,(+PrR),,

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) and determine whether or not that value contains the word Everyone. (No, it doesn’t, at least not in line 1.) If the second item does contain the word Everyone (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.

Got that? Good. Now let’s talk about the script and how it performs its magic.

As you can see, we start out by defining a constant named ForReading and assigning it the value 1; we’ll use this constant when we go to open the file Test.txt. Coincidentally enough, we next create an instance of the Scripting.FileSystemObject, then use the OpenTextFile method to open Test.txt for reading. That’s what these two lines of code are for:

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

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’s AtEndOfStream property is True. (Which is just a hoity-toity way of saying, “Keep reading until you reach the end of the file.”) Inside the loop, the first thing we do is use the ReadLine method to read the initial line in the file and then store that value in a variable named strLine:

strLine = objFile.ReadLine

That brings us to this line of code:

arrFields = Split(strLine, ",")

Here we’re using the Split function to turn the string value strLine into an array; to be a little more specific, we’re 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:

MBPGPS1PRN-001\

Administrators

LOCALGROUP

S-1-5-32-544

PRINTSHARE

\\MBPGPS1PRN-001\mbbetsb01

OWNER

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 in it. Well, now we have a very easy way to check for that:

If InStr(arrFields(1), "Everyone") Then

Here we’re using the InStr function to determine whether or not the word Everyone can be found in the second field of our line of text. Or, more correctly, we’re checking to see if Everyone can be found in the second item in our array. And don’t worry, we really are checking the second item in the array, despite the syntax arrFields(1). In VBScript, arrays are “0-index.” 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’re interested in) is assigned the index number 1. Hence the value 1 in arrFields(1). If we wanted to look at the third item in the array, then we’d use the syntax arrFields(2).

And so on.

If the target word is not 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 found, then we execute this line of code:

strContents = strContents & arrFields(5) & vbCrlf

All we’re doing here is assigning a value to a variable named strContents, a value consisting of the existing value of the variable plus the value of the sixth item in the array plus a carriage return-linefeed character (vbCrLf). And yes, we specified 5 as the index number for a very good reason: the sixth item in an array will always have an index number of 5.

After we’ve read and processed each line in the text file we call the Close 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:

Set objFile = objFSO.CreateTextFile("C:\Scripts\Everyone.txt")

As you might have guessed, this is where we plan on writing the data we’ve extracted from Test.txt, something we do by calling the Write method, passing the method the variable strContents:

objFile.Write strContents

If we then close the file Everyone.txt and examine the contents, we should see something that look like this:

\\MBPGPS1PRN-001\mbbetsb01
\\MBPGPS1PRN-002\mbbetsb01
\\MBPGPS2PRN-001\mbbet02

In other words, these are the printers that the Everyone group has access to. (Well, assuming that’s what the file Test.txt is all about.)

That should do it, RP. Or at least we hope it does; as much as we’d like to stick around and chat about parsing text files, well, we need to get going. Time for a coffee break, you know.

Note. Actually, in Seattle people don’t take coffee breaks; that is, we don’t steal a few moments away from work in order to drink a little coffee. Instead, we take work breaks, stealing a few moments away from drinking coffee in order to do a little work.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Works fine until you encounter a field with a comma in it, such as:

    John Smith,5th Floor,"Director, Purchasing",Laptop,Notes

  • It was very helpful to me!

    thank you!

  • "If InStr(arrFields(1), "Everyone") Then" That works for finding what you need but what if you also want to generate a list for the fields that do not match? I need to parse for matching and non-matching then place each list in a separate variable for populating two separate fields in an application.