CSV is short for comma-separated-values file, a text file in which individual elements 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:

How can you create a CSV file all your own? That’s easy: just use the FileSystemObject.

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:

<span style="color: #0000ff;">Const</span> ForReading = 1

<span style="color: #0000ff;">Const</span> ForWriting = 2

<span style="color: #0000ff;">Set</span> fso = CreateObject( <span style="color: #006080;">"Scripting.FileSystemObject"</span> )

<span style="color: #0000ff;">Set</span> logFile = fso.CreateTextFile( <span style="color: #006080;">"test.csv"</span>, ForWriting, <span style="color: #0000ff;">True</span> )

logFile.Write <span style="color: #006080;">"A,"</span>

logFile.Write <span style="color: #006080;">"B,"</span>

logFile.Write <span style="color: #006080;">"C"</span>

logFile.Writeline

logFile.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, and 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 logFile. 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,. 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 Process_List.csv:

<span style="color: #0000ff;">Const</span> ForAppending = 2

<span style="color: #0000ff;">Set</span> fso = CreateObject( <span style="color: #006080;">"Scripting.FileSystemObject"</span> )

<span style="color: #0000ff;">Set</span> logFile = fso.CreateTextFile( <span style="color: #006080;">"process_list.csv"</span>, ForWriting, <span style="color: #0000ff;">True</span> )

<span style="color: #0000ff;">Set</span> wmiService = GetObject( <span style="color: #006080;">"winmgmts:root\cimv2"</span> )

<span style="color: #0000ff;">Set</span> processList = wmiService.ExecQuery( <span style="color: #006080;">"Select * from Win32_Process"</span> )

<span style="color: #0000ff;">For</span> <span style="color: #0000ff;">Each</span> process <span style="color: #0000ff;">in</span> processList

    logFile.Write process.Name & <span style="color: #006080;">","</span>

    logFile.Write process.ProcessId & <span style="color: #006080;">","</span>

    logFile.Write process.ThreadCount

    logFile.Writeline

<span style="color: #0000ff;">Next</span>

logFile.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 process.Name. That’s not a problem, we just use the variable as the parameter to the Write method. However, because these are variables, we cannot enclose them and the following comma in double quotes. A line of code like this will not work

logFile.Write <span style="color: #006080;">"process.Name,"</span>

Instead, we specify the variable (process.Name), and then use the ampersand to tack a comma (“,”) to the end. We do the same thing for the ProcessId but not for ThreadCount; that’s because ThreadCount is the last item on each line. Consequently, we use WriteLine to press ENTER and start a new line in the file. 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

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 process ExecutablePath? Then just add a line of code like this:

logFile.Write process.ExecutablePath & <span style="color: #006080;">","</span>

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:

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:

Moreover, 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 process.Name), uses Chr(34) to write another double quote mark, and only then tacks a comma on the end.

<span style="color: #0000ff;">Const</span> ForWriting = 2

<span style="color: #0000ff;">Set</span> fso = CreateObject( <span style="color: #006080;">"Scripting.FileSystemObject"</span> )

<span style="color: #0000ff;">Set</span> logFile = fso.CreateTextFile( <span style="color: #006080;">"process_list.csv"</span>, ForWriting, <span style="color: #0000ff;">True</span> )

<span style="color: #0000ff;">Set</span> wmiService = GetObject( <span style="color: #006080;">"winmgmts:root\cimv2"</span> )

<span style="color: #0000ff;">Set</span> processList = wmiService.ExecQuery( <span style="color: #006080;">"Select * from Win32_Process"</span> )

<span style="color: #0000ff;">For</span> <span style="color: #0000ff;">Each</span> process <span style="color: #0000ff;">in</span> processList

    logFile.Write Chr(34) & process.Name & Chr(34) & <span style="color: #006080;">","</span>

    logFile.Write & Chr(34) & process.ProcessId & Chr(34) & <span style="color: #006080;">","</span>

    logFile.Write & Chr(34) & process.ThreadCount & Chr(34)

    logFile.Writeline

<span style="color: #0000ff;">Next</span>

logFile.Close

Based on “How Can I Create a CSV File?” from the Scripting Guys.

Tags: Advanced, text files

Lets say we have a text file with customer names:

What you’re looking for is a script that can read through the file and report the number of occurrences for each item; in other words, a script that can return a report similar to this:

There are several different ways you can do this, but we preferred to show you a very powerful and flexible method for dealing with text files as databases. It’s not well-known, but you can actually use ADO (ActiveX Data Objects) to run database queries against a text file. And that’s exactly what we do with this script:

<span style="color: #0000ff;">Const</span> adOpenStatic = 3

<span style="color: #0000ff;">Const</span> adLockOptimistic = 3

<span style="color: #0000ff;">Const</span> adCmdText = &H0001

<span style="color: #0000ff;">Set</span> con = CreateObject( <span style="color: #006080;">"ADODB.Connection"</span> )

<span style="color: #0000ff;">Set</span> rst = CreateObject( <span style="color: #006080;">"ADODB.Recordset"</span> )

path2TextFile = <span style="color: #006080;">"C:\Data"</span>

dataProvider = <span style="color: #006080;">"Provider=Microsoft.Jet.OLEDB.4.0;"</span>

dataSource = <span style="color: #006080;">"Data Source="</span> & path2TextFile & <span style="color: #006080;">";"</span>

extProperties = <span style="color: #006080;">"Extended Properties="</span><span style="color: #006080;">"text;HDR=Yes;FMT=Delimited"</span><span style="color: #006080;">""</span>

con.Open dataProvider & dataSource & extProperties

<span style="color: #0000ff;">If</span> con.State = 1 <span style="color: #0000ff;">Then</span>

   fileName = <span style="color: #006080;">"Customers.txt"</span>

   sql = <span style="color: #006080;">"SELECT City, COUNT(City) AS CountCity "</span> & _

         <span style="color: #006080;">"FROM "</span> & fileName & <span style="color: #006080;">" GROUP BY City"</span>

   rst.Open sql, con, adOpenStatic, adLockOptimistic, adCmdText

   <span style="color: #0000ff;">Do</span> <span style="color: #0000ff;">Until</span> rst.EOF

      Print rst.Fields.Item( <span style="color: #006080;">"City"</span> ) & <span style="color: #006080;">" -> "</span> & rst.Fields.Item( <span style="color: #006080;">"CountCity"</span> )

      rst.MoveNext

   <span style="color: #0000ff;">Loop</span>

<span style="color: #0000ff;">End</span> <span style="color: #0000ff;">If</span>

Let’s start with the SQL query we use to retrieve data:

path2TextFile = <span style="color: #006080;">"C:\Data"</span>

dataProvider = <span style="color: #006080;">"Provider=Microsoft.Jet.OLEDB.4.0;"</span>

dataSource = <span style="color: #006080;">"Data Source="</span> & path2TextFile & <span style="color: #006080;">";"</span>

extProperties = <span style="color: #006080;">"Extended Properties="</span><span style="color: #006080;">"text;HDR=Yes;FMT=Delimited"</span><span style="color: #006080;">""</span>

con.Open dataProvider & dataSource & extProperties

At first glance, you might be confused by this query. Each line in the file contains a city name and the customer name. Yet, even though we have only one field in our “database,” our SQL query specifies two separate items: City and Count (City) AS CountCity. What gives?

Actually, we are not selecting two different fields; instead, we are selecting one field (City) and one “calculated field” we can use to tally up the number of times each city appears in the database. (A calculated field is a field that we calculate based on data found in other fields; the field itself does not actually exist in the database.) The item Count(City) AS CountCity (in conjunction with the clause GROUP BY City) can be read like this: Count up the number of times each city appears in the file and then store that number in a calculated field named CountCity.

The nice thing is that we get back this recordset with the city totals calculated for us. Alternatively, we could have opened the text file, read each line, and used an array or a Dictionary object to keep track of the number of times each city name appeared in the file. However, ADO makes this process far easier.

Once we get a recordset back and connection state open ( 1 ) we simply use this code to loop through all the records, echoing back the name of the city and the number of times each city appears in the file:

<span style="color: #0000ff;">Do</span> <span style="color: #0000ff;">Until</span> rst.EOF

   Print rst.Fields.Item( <span style="color: #006080;">"City"</span> ) & <span style="color: #006080;">" -> "</span> & rst.Fields.Item( <span style="color: #006080;">"CountCity"</span> )

   rst.MoveNext

<span style="color: #0000ff;">Loop</span>

Based on “How Can I Tally Items in a Text File?” from the Scripting Guys.

Tags: Advanced, text files