Back to the main data prep VBA page
This is a small Excel VBA script I wrote to pull the SVMON memory statistics for a specific PID that contains concatenated SVMON output. I’ve had to modify this script a few times depending on what customer was collecting data and how they wrote it out to a text file. Some customers (and sometimes different people at the same customer site) used time stamps, while others did not. I have not automated all of the parsing, instead I commented out different sections as necessary.
[vb]
Sub parseSVMON()
‘set variables
”””””””
Dim rawLOG As String, rawLOGpath As String, splitArray() As String
pathFull = "C:\workspace\MDS_field\wellpoint\EPDS\2014-06-10-ToroLabTest\2014-06-12-pingN\svmon_ping.out"
Set objrawLOG = CreateObject("Scripting.FileSystemObject")
rawLOGpath = objrawLOG.GetFile(pathFull).ParentFolder.path & "\"
‘setup clean files
”””””””””
srcCSV = Dir(objrawLOG.GetFile(pathFull).ParentFolder.path & "\")
Do While srcCSV <> ""
tempFILE = rawLOGpath & "\" & srcCSV & ""
If srcCSV Like "svmonClean.txt" Then
Set delFILE = CreateObject("Scripting.FileSystemObject")
delFILE.Deletefile tempFILE, True
End If
srcCSV = Dir
Loop
fnameLOGclean = "svmonClean.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objCLEAN = objFSO.CreateTextFile(rawLOGpath & "\" & fnameLOGclean, 1)
‘objCLEAN.WriteLine "date/time,PID,inuse,free,pin,virtual"
objCLEAN.WriteLine "PID,inuse,free,pin,virtual"
‘setup dictionary
””””””””’
Dim ixnDIC As New Dictionary
ixnDIC.Add "month", ""
ixnDIC.Add "day", ""
ixnDIC.Add "year", ""
ixnDIC.Add "time", ""
ixnDIC.Add "PID", ""
‘loop through mlg files
”””””””””””’
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objrawLOG = objFSO.OpenTextFile(pathFull, 1)
Do Until objrawLOG.AtEndOfStream
txnstring = objrawLOG.ReadLine
‘If txnstring Like "*:*" Then
‘ ixnDIC.Item("month") = "": ixnDIC.Item("day") = ""
‘ ixnDIC.Item("year") = "": ixnDIC.Item("time") = ""
‘ ixnDIC.Item("month") = splitArray(Val(1))
‘ ixnDIC.Item("day") = splitArray(Val(2))
‘ ixnDIC.Item("year") = splitArray(Val(3))
‘ ixnDIC.Item("time") = splitArray(Val(5))
‘End If
If txnstring Like "*mpinet_wellpo*" Then
tempstring = Application.WorksheetFunction.Trim(txnstring)
splitArray() = Split(tempstring, " ")
ixnDIC.Item("PID") = ixnDIC.Item("PID") & splitArray(Val(0)) & "," ‘pid
ixnDIC.Item("PID") = ixnDIC.Item("PID") & splitArray(Val(2)) & "," ‘inuse
ixnDIC.Item("PID") = ixnDIC.Item("PID") & splitArray(Val(3)) & "," ‘free
ixnDIC.Item("PID") = ixnDIC.Item("PID") & splitArray(Val(4)) & "," ‘pin
ixnDIC.Item("PID") = ixnDIC.Item("PID") & splitArray(Val(5)) & "," ‘virtual
‘CLNstring = ixnDIC.Item("month") & " " & _
‘ ixnDIC.Item("day") & " " & _
‘ ixnDIC.Item("year") & " " & _
‘ ixnDIC.Item("time") & "," & _
‘ ixnDIC.Item("PID")
CLNstring = ixnDIC.Item("PID")
objCLEAN.WriteLine CLNstring
CLNstring = ""
ixnDIC.Item("PID") = ""
End If
Loop
objrawLOG.Close
End Sub
[/vb]