VBScript that Cleans Up Eroneous Characters From Log File

I have a script that basically goes out and scrubs the log file of eroneous characters such as ^ (carrots), : (colons) and spaces and then mashes everything together as a true comma separated file.  The script also add's the headers to the top of the CSV that it creates so that I can parse through it more efficiently.

I noticed though that the script output is putting the name of the header in the actual data along with the value and there are still little squares present.  I would assume these ASCI text squares represent "breaks" or "tabs" of some kind not sure.

If I could get it to read as such (Screen shot 1) that would be great!

Set objDialog = CreateObject("UserAccounts.CommonDialog")
 
strHeader = "EVT,DATE,TIME,TYPEOFEVT,EVTID,STATUS,ACCOUNT,TYPEOFUSER,DC,CHANGEPASSWORDATTEMPT,TARGETACCTNAME,TARGETDOMAIN,TARGETACCTID,CALLERUSERNAME,CALLERDOMAIN,CALLERLOGON,PRIVILEGES"
arrHeaders = Split(strHeader, ",")
 
objDialog.Filter = "VBScript Scripts|*.vbs|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\TEST"
 
intResult = objDialog.ShowOpen
 
If intResult = 0 Then
    Wscript.Quit
End If
 
Dim fso, DestFilename
Dim SourceFileName, Source, TopDestPath, basename, dot, SendtoPath
 
TopDestPath = "C:\TEST"
SendtoPath = "C:\TEST-RESULTS"
'SendtoPath = "C:\TEST\TEST-RESULTS"
 
Set fso = CreateObject("Scripting.FileSystemObject")
Source = objDialog.FileName
SourceFileName = fso.GetFileName(Source)
Set DestFileName = fso.GetFile(Source)
 
dot = InStr(SourceFileName, ".")
basename = Left(SourceFileName, dot-1)
'''MsgBox SendToPath & "\" & basename & ".csv"
DestFileName.Copy SendToPath & "\" & basename & ".csv", True
wscript.echo SourceFileName & " copied to " & SendToPath & "\" & basename & ".csv"
Set objCSV = fso.OpenTextFile(SendtoPath & "\" & basename & ".csv", 1, False)
strNewContents = strHeader
While Not objCSV.AtEndOfStream
        strTheLine = objCSV.ReadLine
        If strTheLine <> "" Then
                ' --- NEW SUGGESTION TO GET RID OF ^' INSTANCES AND DOUBLE SPACES ---
                'If InStr(strTheLine, Chr(94) & Chr(96)) > 0 Then MsgBox Asc(Mid(strTheLine, InStr(strTheLine, "^") + 1, 1))
                If InStr(strTheLine, Chr(94) & Chr(96)) > 0 Then strTheLine = Replace(strTheLine, Chr(94) & Chr(96), ",")
                While InStr(strTheLine, "  ") > 0
                   strTheLine = Replace(strTheLine, "  ", " ")
                Wend
                strTheLine = Replace(strTheLine, "-,", "-")
                strTheLine = Replace(strTheLine, ",0x", "-0x")
                ' -------------------------------------------------------------------
                strNewContents = strNewContents & VbCrLf & strTheLine
        End If
Wend
objCSV.Close
Set objCSV = fso.CreateTextFile(SendtoPath & "\" & basename & ".csv", True)
objCSV.Write strNewContents
objCSV.Close
Set objCSV = Nothing
 
Set fso = nothing
Set DestFileName = Nothing
 
' ********* SECOND SCRIPT STARTS HERE **********
 
'strInputFile = "C:\TEST\TEST.CSV"
'strOutputFile = "C:\TEST\TEST-RESULTS.XLS"
strInputFile = SendtoPath & "\" & basename & ".csv"
strOutputFile = Left(strInputFile, InStrRev(strInputFile, ".") - 1) & "_Results.xls"
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Const intForReading = 1
 
Const adVarChar = 200
Const adBigInt = 20
Const MaxCharacters = 255
Set objDataList = CreateObject("ADOR.Recordset")
For intField = 0 To UBound(arrHeaders)
        objDataList.Fields.Append arrHeaders(intField), adVarChar, MaxCharacters
Next
objDataList.Open
        
strNames = ";"
 
Set objFile = objFSO.OpenTextFile(strInputFile, intForReading, False)
While Not objFile.AtEndOfStream
        strTheLine = objFile.ReadLine
        If Trim(strTheLine) <> "" Then
                strLine = Split(strTheLine, ",")
                If LCase(strTheLine) <> LCase(Join(arrHeaders, ",")) Then
                        If InStr(strNames, ";" & strLine(4) & ";") = 0 Then
                                strNames = strNames & strLine(4) & ";"
                        End If
                        objDataList.AddNew
                        For intField = 0 To UBound(arrHeaders)
                                objDataList(arrHeaders(intField)) = strLine(intField)
                        Next
                        objDataList.Update
                End If
        End If
Wend
objFile.Close
If Left(strNames, 1) = ";" Then strNames = Mid(strNames, 2)
If Right(strNames, 1) = ";" Then strNames = Left(strNames, Len(strNames) - 1)
 
objDataList.Sort = "DATE"
objDataList.MoveFirst
 
If Right(LCase(strOutputFile), 4) = ".xls" Then
        Set objExcel = CreateObject("Excel.Application")
        Const xlUp = -4162
        objExcel.Visible = True
        Set objWB = objExcel.Workbooks.Add
        While objWB.Sheets.Count > 1
                objWB.Sheets(objWB.Sheets.Count).Delete
        Wend
        For intField = 0 To UBound(arrHeaders)
        objWB.Sheets(objWB.Sheets.Count).Cells(1, intField + 1).Value = arrHeaders(intField)
    Next
        objWB.Sheets(objWB.Sheets.Count).Rows("1:1").Font.Bold = True
Else
        Set objOutput = objFSO.CreateTextFile(strOutputFile, True)
        objOutput.WriteLine Join(arrHeaders, ",")
        objOutput.Write String(70, "*")
End If
 
If InStr(Join(arrHeaders, ","), ",IP,") > 0 Then
        For Each strUsername In Split(strNames, ";")
                objDataList.Filter = "Username='" & strEvtID & "'"
                objDataList.MoveFirst
                boolDifferent = False
                strCurrentIP = objDataList.Fields("IP")
                While Not objDataList.EOF
                        If objDataList.Fields("IP") <> strCurrentIP Then
                                boolDifferent = True
                        End If
                        objDataList.MoveNext
                Wend
                If boolDifferent = True Then
                        objDataList.MoveFirst
                        strCurrentIP = objDataList.Fields("IP")
                        While Not objDataList.EOF
                                If Right(LCase(strOutputFile), 4) = ".xls" Then
                                        intLastRow = objWB.Sheets(objWB.Sheets.Count).Cells(65536, "A").End(xlUp).Row + 1
                                        For intField = 0 To UBound(arrHeaders)
                                        objWB.Sheets(objWB.Sheets.Count).Cells(intLastRow, intField + 1).Value = objDataList.Fields(arrHeaders(intField))
                                    Next
                                        If objDataList.Fields("IP") <> strCurrentIP Then
                                                objWB.Sheets(objWB.Sheets.Count).Rows(intLastRow & ":" & intLastRow).Font.Bold = True
                                                objWB.Sheets(objWB.Sheets.Count).Rows(intLastRow & ":" & intLastRow).Font.ColorIndex = 3
                                        End If
                                Else
                                        strLine = ""
                                        For intField = 0 To UBound(arrHeaders)
                                        If strLine = "" Then
                                                strLine = objDataList.Fields(arrHeaders(intField))
                                        Else
                                                strLine = strLine & "," & objDataList.Fields(arrHeaders(intField))
                                        End If
                                    Next
                                        objOutput.Write VbCrLf & strLine
                                End If
                                objDataList.MoveNext
                        Wend
                End If
        Next
End If
 
objDataList.Filter = "EvtID <> '540'"
On Error Resume Next
objDataList.MoveFirst
If Err.Number = 0 Then
        On Error GoTo 0
        While Not objDataList.EOF
                If Right(LCase(strOutputFile), 4) = ".xls" Then
                        intLastRow = objWB.Sheets(objWB.Sheets.Count).Cells(65536, "A").End(xlUp).Row + 1
                        For intField = 0 To UBound(arrHeaders)
                        objWB.Sheets(objWB.Sheets.Count).Cells(intLastRow, intField + 1).Value = objDataList.Fields(arrHeaders(intField))
                    Next
                        objWB.Sheets(objWB.Sheets.Count).Rows(intLastRow & ":" & intLastRow).Font.Bold = True
                        objWB.Sheets(objWB.Sheets.Count).Rows(intLastRow & ":" & intLastRow).Font.ColorIndex = 3
                Else
                        strLine = ""
                        For intField = 0 To UBound(arrHeaders)
                        If strLine = "" Then
                                strLine = objDataList.Fields(arrHeaders(intField))
                        Else
                                strLine = strLine & "," & objDataList.Fields(arrHeaders(intField))
                        End If
                    Next
                        objOutput.Write VbCrLf & strLine
                End If
                objDataList.MoveNext
        Wend
Else
        Err.Clear
        On Error GoTo 0
End If
 
If Right(LCase(strOutputFile), 4) = ".xls" Then
        objWB.Sheets(objWB.Sheets.Count).Columns.AutoFit
        objExcel.DisplayAlerts = False
        objWB.SaveAs strOutputFile
        objExcel.DisplayAlerts = True
Else
        objOutput.Close
        Set objShell = CreateObject("WScript.Shell")
        objShell.Run "notepad """ & strOutputFile & """", 1, False
End If

Select allOpen in new window

ScreenShot1.xls
TEST-Results.xls
TEST.txt