Using VBScript to Add Hostnames in CSV Logs

Written By: Kevin Jordan

- 24 May 2006 -
















Description: Get your server logs ready for a database by using this VBScript to pull the hostname from a computer and add it to the beginning of each line in a .csv file.

  1. Creating the Logs and Calling the Script
  2. Helper Functions
  3. Adding to the CSV
  4. Pull it Together

The Meat: AddToCSV(strColumn, strFileName)

This is where it most of the functions come together. strColumn is what we’re wanting to add to the front of each line in the .csv. In our case we’re adding the hostname, but you could add anything you wanted with this subroutine. strFileName is obviously the .csv file.

If FileLen(strFileName) > 0 Then                ' Check if the file is empty

First thing is that I didn’t want to add columns to empty files, that would just clutter our database. So we use our newly created FileLen function to find out if it’s empty or not. If it is then that’s it, but otherwise...

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strFileName, 1)
strText = objFile.ReadAll
arrText = split(strText, vbCrLf)    ' Split each line the file into an array

Read in the contents of the file and then split each line into an element in an array. You could also put this into a separate function if you wanted. I tend to use it a lot on file lists and call it FileToArray(). It’s up to you.

' Add hostname to the front of each item in the array
For i=0 to Ubound(arrText)
        If trim(arrText(i)) <> "" Then
                arrText(i) = strColumn & ", " & arrText(i) & vbCrLf
        End If
Next

For each element with in the array we’re going to add strColumn to the front. To do this, a for loop works great. Ubound() returns the number of elements within an array so that we can start at 0 and work our way all the way to the end of the file. I added a trim() to make sure I wasn’t adding columns to lines that ended up being just empty spaces.

strText = join(arrText)
Set objFile = objFSO.OpenTextFile(strFileName, 2)
objFile.WriteLine strText
objFile.Close

Finally, I join the array back together and write the new string to the original .csv file. Here’s what it looked like all together.

' // * Adds  strColumn in front of a csv row * //
Sub AddToCSV(strColumn, strFileName)
        Dim objFSO, objFile, strText, arrText, i
 
        If FileLen(strFileName) > 0 Then               ' Check if the file is empty
                Set objFSO = CreateObject("Scripting.FileSystemObject")
                Set objFile = objFSO.OpenTextFile(strFileName, 1)
                strText = objFile.ReadAll
                arrText = split(strText, vbCrLf)    ' Split each line the file into an array
 
                ' Add hostname to the front of each item in the array
                For i=0 to Ubound(arrText)
                        If trim(arrText(i)) <> "" Then
                                arrText(i) = strColumn & ", " & arrText(i) & vbCrLf
                        End If
                Next
 
                strText = join(arrText)
                Set objFile = objFSO.OpenTextFile(strFileName, 2)
                objFile.WriteLine strText
                objFile.Close
        End If
 
        Set objFSO = NOTHING
        Set objFile = NOTHING
 
End Sub ' AddToCSV

<< Previous

Next >>