Dwarfsoft [GPA]

Manipulating CSV Files – Part 1

by on Sep.09, 2008, under Certification, LDAP, Novell, Politics, Scripting, Study, Training, Work

Print This Post Print This Post

Before I jump in to the fun that is the Scripting that I have been wading through this past week, I thought I’d let all of those who may care that I finally have some kind of certification. I passed Microsoft exams 70-291 and 70-284. So I am now the proud holder of a Microsoft Certified Systems Administrator Messaging Specialist certification (MCSA Messaging). Now I am concentrating on the final three exams so that I can get my MCSE.

After a rather hectic week of scripting a solution and then distributing it under an excessively short deadline, I have been asked to provide stats on the result of forcing this solution out to clients. The solution that I had to develop keeps tabs on a System Volume Image of PCs, and ensures that this Image never gets out of date too far. Currently I am not forcing a Store every restart, as the planned solution was to do, but I do inform the client that their current Image is out of date and ask them if they want to do a Store now. If they click on Yes then their PC is rebooted and the Store is done (providing that one of many flaws in the current Store process do not interrupt the process).

Because of some of the issues that we have had, as well as Managements want to view the impact of what we have done in a statistical format, I was required to provide a secondary application, which is just an updated version of an AutoIT Script that logs the Image Date into a log file during login. Because this file was preexisting it has been used in this way to gather stats for some time.

The issue is that the log file includes EVERY instance of a login for a PC, and I want only the most recent with the timestamp of the image, not all the other instances, or the log values that are too old (I was unable to clear the logs due to current procedure). Therefore I was required to learn some new things in the VBScript world. This solution is surprisingly fast considering how much data it is required to crunch in order to produce the output CSV file.

Const ForReading = 1
Const ForWriting = 2

Dim arrLines()
i = 0

Set objDictionary = CreateObject("Scripting.Dictionary")
Set objFSO = CreateObject("Scripting.FileSystemObject")

objStartFolder = WScript.ScriptFullNameobjStartFolder = Replace  (objStartFolder, WScript.ScriptName, "") 

Set objFolder = objFSO.GetFolder(objStartFolder & "Logs")

Set colFiles = objFolder.Files
For Each objFile in colFiles
    Set textFile = objFSO.OpenTextFile(objFile.Path, ForReading)

    Do Until textFile.AtEndOfStream
        Redim Preserve arrLines(i)
        arrLines(i) = textFile.ReadLine
        i = i + 1

    For i = Ubound(arrLines) to LBound(arrLines) Step -1
        splt = Split (arrLines(i),",")

        If (UBound(splt) < 19) Then
            i = LBound(arrLines)
            If Splt(19) <> "" Then
               If Not objDictionary.Exists(splt(1)) Then
                  objDictionary.Add splt(1),splt(19)
               End If
            End If
        End If

    ReDim arrLines(0)
    i = 0

Set writeFile = objFSO.CreateTextFile(objStartFolder & "Stores.csv")
For Each strKey in objDictionary.Keys
   writeFile.WriteLine strKey & "," & objDictionary.Item(strKey)

In this particular script the splt Array has 20 columns (or 19 for those items that were prior to me updating the script). Column 2 (or splt(1)) is the PC Name, and Column 20 (or splt(19)) is the Timestamp of the Image. This script reads through every file in the Logs folder (in the same path as the script) and reads the file in reverse until it comes across the entries with only 19 columns. For every unique PC Name that it finds it adds this into a Dictionary object with the Timestamp as the value. The Dictionary is then written to the new CSV file one at a time.

In order to get the stats on how effective this has been, I paste a couple of Excel Macros:


The magic date here is the 22nd of August 2008. This was chosen purely because it was a recent date, and it was newer than most of the Images for the PCs within the IT Department, who already had the AutoStore scripts working on their machines. Which provided for a controlled Environment for testing some of the modifications I was forced to make last minute.

One thing that I could do to improve the accuracy of this script is to pull out all the Workstation Names from Novell using LDAP and get a total overall status of where the Store dates are across all PCs, but that would pollute the pool with a large number of machines that have been decommissioned (they still appear as a Workstation in Novell until they have not been logged on to for 3 months, then they fall off the system).

On a side note, I have to pull in the Logs into the Logs directory in some way, which is achieved for me by running a Batch file which I have written as follows:

ECHO Pulling Logs %DATE% - %TIME%>>Log.txt
cd %~dp0
FOR /F "tokens=*" %%A IN (Sources.txt) DO (
  FOR /F "tokens=1 delims=" %%Z IN ("%%A") DO (
     COPY "%%AlogsLogIn.csv" "%~dp0Logs%%Z.csv" /A /D /Y /Z >>Log.txt
ECHO Current Stores being Rebuilt at %DATE - %TIME%>>Log.txt
WScript Stores.vbs
Start Excel Stores.csv
ECHO Log Pull Finished at %DATE% - %TIME%>>Log.txt

This script uses a file called “Sources.txt” which is a list of paths on newlines which point to where each of the Logs are stored. Each line consists of something like \ServerNameSYSPUBLIC. The line FOR /F “tokens=1 delims=” %%Z IN (“%%A”) allows me to pull out the servername from each line in the file, so that when I copy the file from the server, I can rename it so that its source can be identified.

I think this will conclude todays lesson in VBScript and Batch, and I’d best get back to studying.

Cheers, Chris.

:, , , , , , , ,

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!