How Can I Create a CSV File?

ScriptingGuy1

Hey, Scripting Guy! How can I create a CSV file?

— LF

Hey, LF. For those of you who aren’t up on your acronyms, CSV is short for comma-separated-values file, a text file in which individual elements are separated by commas. For example, say you have a CSV file consisting of user first names, last names, and job titles; that file might look something like this:

Ken,Myer,Accountant
Pilar,Ackerman,Vice-President
Carol,Philips,Research Specialist

Incidentally, at Microsoft we love acronyms. The other day, for example, the Scripting Guys were sent a three-sentence product description that contained 11 acronyms. Our personal favorite – used extensively around here – is OOF, which is somehow short for Out Of Office.

No wonder we felt the need to add a spellchecker to all our products.

But back to your question. How can you create a CSV file all your own? That’s easy: just use the FileSystemObject. After all, that’s what it’s here for.

Let’s take a look at a very simple demonstration script, then we’ll take a look at a more practical example. Here’s a sample script that writes the string A,B,C to a text file named Test.csv:

Const ForWriting = 2

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objLogFile = objFSO.CreateTextFile(“test.csv”, _ 
    ForWriting, True)
objLogFile.Write “A,” 
objLogFile.Write “B,” 
objLogFile.Write “C”
objLogFile.Writeline
objLogFile.Close

We start out by defining a constant named ForWriting and setting the value to 2; any time you use the FileSystemObject, you need to use the appropriate constant, depending on whether you want to read from, write to, or append a file. We create an instance of the FileSystemObject, then use the CreateTextFile method to create a new text file named Test.cvs. (Notice that we did not specify a path; that means that the text file will be created in the same folder as the script. Had we wanted to, we could have specified a complete path, like C:\Scripts\Logfiles\Test.csv.)

When we call the CreateTextFile method, we simultaneously create an object reference to the new file; in our script, we named that object reference objLogFile (although we could have called it anything we wanted). With the object reference in hand, we can then use the Write method to write data to the file. Notice that we begin by writing the string A, (and, yes, we must manually include the commas when creating a CSV file). The Write method writes the specified data (in this case A,), and then leaves the cursor in place. Because of that, when we next call this method the string B, is tacked on right next to the A,. As a result, our text file looks like this:

A,B,

We call the Write method a third time, and write C. Because this marks the end of the line, we don’t append a comma after it; instead, we call the WriteLine method, which is equivalent to hitting ENTER on the keyboard. That gives us a text file that looks like this:

A,B,C

Note that there are no spaces between commas and the start of the next item. When one item ends, the next begins right away.

Now let’s take a look at a more practical example. This script uses WMI to retrieve service information, then uses the FileSystemObject to write that information to a text file named Service_List.csv:

Const ForAppending = 2

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objLogFile = objFSO.CreateTextFile(“service_list.csv”, _ 
    ForWriting, True)
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:” _
    & “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)
Set colListOfServices = objWMIService.ExecQuery(“Select * from Win32_Service”)
For Each objService in colListOfServices
    objLogFile.Write objService.Name & “,” 
    objLogFile.Write objService.StartMode & “,” 
    objLogFile.Write objService.State 
    objLogFile.Writeline
Next
objLogFile.Close

As you can see, we use the same exact technique used in our demonstration script. The biggest difference is that we aren’t using hard-coded values like A, B, and C. Instead, we’re using variables like objService.Name. That’s not a problem, we just use the variable as the parameter to the Write method. But because these are variables, we can’t enclose them and the following comma in double quotes. A line of code like this will not work:

objLogFile.Write “objService.Name,”

Instead, we specify the variable (objService.Name), and then use the ampersand to tack a comma (“,”) to the end. We do the same thing for the StartMode but not for State; that’s because State is the last item on each line. Consequently, we use WriteLine to press ENTER and start a new line in the file (the same way we sued WriteLine after the letter C in our first example). Because we do this within a For Each loop, we’ll end up writing this information for each service installed on the computer.

The net result is a text file that looks like this:

Alerter,Manual,Stopped
ALG,Manual,Stopped
AppMgmt,Manual,Stopped
aspnet_admin,Auto,Stopped

Although we could have cut down the number of lines of code slightly (by concatenating all our variables and what-not) writing the code in this fashion makes it pretty easy for you to see what’s going on. It also makes it easy for you to add new items to each line. Want to include the service PathName? Then just add a line of code like this:

objLogFile.Write objService.PathName & “,”

One more thing: When you’re working with WMI, the preceding script will likely handle all your needs. When working with other scripting technologies, however, you might encounter a problem with data that includes commas. For example, suppose you have a text file of users, their office addresses, and their job titles. One user has an address of 2049, but another has an address of 2050, Suite A. Your subsequent text file looks like this:

Ken,Myer,2049,Accountant
Pilar,Ackerman,2050,Suite A,Vice-President

Yikes! We have a problem: our first line contains four fields (remember commas indicate the end of one field and the beginning of the next): Ken / Myer / 2049 / Accountant. Unfortunately, because of the comma in the address itself, our second line has five fields: Pilar / Ackerman / 2050 / Suite A / Vice-President. Uh-oh ….

How do you deal with embedded commas like this? The secret is to surround each field with double quotes; if your text file looks like this, the embedded commas are ignored:

“Ken”,”Myer”,”2049″,”Accountant”
“Pilar”,”Ackerman”,”2050,Suite A”,”Vice-President”

And how do you surround your fields with double quotes. Here’s another sample script that uses the function Chr(34) to write a double quote mark, writes a service property (such as objService.Name), uses Chr(34) to write another double quote mark, and only then tacks a comma on the end.

Const ForWriting = 2

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objLogFile = objFSO.CreateTextFile(“service_list.csv”, _ 
    ForAppending, True)
strComputer = “.”
Set objWMIService = GetObject(“winmgmts:” _
    & “{impersonationLevel=impersonate}!\\” & strComputer & “\root\cimv2”)
Set colListOfServices = objWMIService.ExecQuery(“Select * from Win32_Service”)
For Each objService in colListOfServices
    objLogFile.Write chr(34) & objService.Name & chr(34) & “,” 
    objLogFile.Write chr(34) & objService.StartMode & chr(34) & “,” 
    objLogFile.Write chr(34) & objService.State  & chr(34)
    objLogFile.Writeline
Next
objLogFile.Close

For more information, check out this portion of the Microsoft Windows 2000 Scripting Guide. And we already know your next question: how do you read a CSV file using a script? For the answer, take a look at this Scripting Clinic columnon reading text files using ADO.

ScriptingGuy1

Follow