Manipulating CSV Files – Part 1
by dwarfsoft on Sep.09, 2008, under Certification, LDAP, Novell, Politics, Scripting, Study, Training, Work
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 Loop For i = Ubound(arrLines) to LBound(arrLines) Step -1 splt = Split (arrLines(i),",") If (UBound(splt) < 19) Then i = LBound(arrLines) Else If Splt(19) <> "" Then If Not objDictionary.Exists(splt(1)) Then objDictionary.Add splt(1),splt(19) End If End If End If Next ReDim arrLines(0) i = 0 textFile.Close Next Set writeFile = objFSO.CreateTextFile(objStartFolder & "Stores.csv") For Each strKey in objDictionary.Keys writeFile.WriteLine strKey & "," & objDictionary.Item(strKey) Next writeFile.Close
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:
=COUNTIF(B:B,">22/08/2008") =COUNT(B:B)
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 OFF ECHO Pulling Logs %DATE% - %TIME%>>Log.txt ECHO.>>Log.txt %~d0 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.>>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.
You must be logged in to post a comment.