wildatom
asked on
Parse text file using VbScript
I need to parse a text file so the results only show like the following:
The data.txt file I'm parsing from:
D08-10-06 10:05:27.010 XXX:390823892aw4dca: -rate(Dlr/u):15.792581 for ASDGADFSCSS:[TRUCKS->TRAIL ERS]
D08-10-06 10:07:28.593 XXX:390823892aw4dca: -rate(Dlr/u):94.99838 for ASDRFACSD:[TRAILERS->AIRPL ANE]
Results Requested:
DLR,Rate,Method
Dlr/u,15,TRUCKS->TRAILERS
Dlr/u,94,TRAILERS->AIRPLAN E
I would like to use either FindStr or some DOS method, or vbscript to parse the file. I have writen one but doesn't quite do the job.
The data.txt file I'm parsing from:
D08-10-06 10:05:27.010 XXX:390823892aw4dca: -rate(Dlr/u):15.792581 for ASDGADFSCSS:[TRUCKS->TRAIL
D08-10-06 10:07:28.593 XXX:390823892aw4dca: -rate(Dlr/u):94.99838 for ASDRFACSD:[TRAILERS->AIRPL
Results Requested:
DLR,Rate,Method
Dlr/u,15,TRUCKS->TRAILERS
Dlr/u,94,TRAILERS->AIRPLAN
I would like to use either FindStr or some DOS method, or vbscript to parse the file. I have writen one but doesn't quite do the job.
Option Explicit
Dim objFSO, objLog
Dim strInputLog, strOutputLog, strData, strLine, strMethod, strTime
Dim strDetail, strDlr
Dim arrLines, arrDetail
Dim intLength
Dim strFnl
CONST ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set path to input and output files
strInputLog = "data.txt"
strOutputLog = "Final_data.txt"
'Open input log and create the output log
strData = objFSO.OpenTextFile(strInputLog,ForReading).ReadAll
Set objLog = objFSO.CreateTextFile(strOutputLog)
'Split the input log into lines
arrLines = Split(strData,vbCrLf)
'Step through the input log lines and break them into
'strings based on spaces
For Each strLine in arrLines
If strLine <> "" Then
arrDetail = Split(strLine," ")
'Parse through strings looking for matches, process the
'strings and write results to output log
For Each strDetail in arrDetail
intLength = LEN(strDetail)
If InStr(strDetail,"TRUCKS->TRAILERS") Then
strMethod = Right(strDetail,intLength - 78)
ElseIf InStr(strDetail,"TRAILERS->AIRPLANE") Then
strMethod = Right(strDetail,intLength - 78)
ElseIf InStr(strDetail,"TRAILERS->ROCKET") Then
strMethod = Right(strDetail,intLength - 78)
ElseIf InStr(strDetail,"Dlr/u,") Then
strDlr = Right(strDetail,intLength - 5)
strMethod = Replace(strMethod, ":", ",")
strMethod = Replace(strMethod, "[", ",")
strMethod = Replace(strMethod, "]", "")
strDlr = Replace(strDlr, ":", ",")
strDlr = Replace(strDlr, ".", ",")
strDlr = Replace(strDlr, "(", "")
strDlr = Replace(strDlr, ")", "")
strFnl = strDlr & "" & strMethod & "," & Date
strFnl = Replace(strFnl, "),,", ",")
strFnl = Replace(strFnl, ",,", ",")
objLog.Writeline strFnl
End If
Next
I didn't put the column headers into the output file... =\
Add this line:
objLog.Writeline "DLR, Rate, Method"
Right above the "For Each" loop.
If you want a space after the comma in the data lines then change:
objLog.Writeline Join(Array(DLR, Rate, Method), ",")
To:
objLog.Writeline Join(Array(DLR, Rate, Method), ", ") ' <--- Space after the comma!
Add this line:
objLog.Writeline "DLR, Rate, Method"
Right above the "For Each" loop.
If you want a space after the comma in the data lines then change:
objLog.Writeline Join(Array(DLR, Rate, Method), ",")
To:
objLog.Writeline Join(Array(DLR, Rate, Method), ", ") ' <--- Space after the comma!
ASKER
Thank you Idle_mind however I kept getting errors when trying to use your script. I ended up coming up with this script instead.
Const ForReading = 1
strInputLog = "data.txt"
strOutputLog = "Final_data.txt"
strSearchString1 = "dlr/"
strSearchString2 = ":["
Set objFSO = CreateObject("Scripting.Fi leSystemOb ject")
Set objInputFile = objFSO.OpenTextFile(strInp utLog, ForReading)
Set objOutputFile = objFSO.CreateTextFile(strO utputLog)
Do Until objInputFile.AtEndOfStream
strText = objInputFile.ReadLine
strFoundString1 = Mid(strText, InStr(1, strText, strSearchString1) + 7)
strFoundString1 = Mid(strFoundString1, 1, InStr(1, strFoundString1, " ") - 1)
strFoundString2 = Mid(strText, InStr(1, strText, strSearchString2) + 2)
strFoundString2 = Mid(strFoundString2, 1, InStr(1, strFoundString2, "]") - 1)
objOutputFile.WriteLine (strFoundString1 & "," & strFoundString2 & "," & date)
Loop
objInputFile.Close
objOutputFile.Close
Set objInputFile = Nothing
Set objOutputFile = Nothing
Set objFSO = Nothing
Const ForReading = 1
strInputLog = "data.txt"
strOutputLog = "Final_data.txt"
strSearchString1 = "dlr/"
strSearchString2 = ":["
Set objFSO = CreateObject("Scripting.Fi
Set objInputFile = objFSO.OpenTextFile(strInp
Set objOutputFile = objFSO.CreateTextFile(strO
Do Until objInputFile.AtEndOfStream
strText = objInputFile.ReadLine
strFoundString1 = Mid(strText, InStr(1, strText, strSearchString1) + 7)
strFoundString1 = Mid(strFoundString1, 1, InStr(1, strFoundString1, " ") - 1)
strFoundString2 = Mid(strText, InStr(1, strText, strSearchString2) + 2)
strFoundString2 = Mid(strFoundString2, 1, InStr(1, strFoundString2, "]") - 1)
objOutputFile.WriteLine (strFoundString1 & "," & strFoundString2 & "," & date)
Loop
objInputFile.Close
objOutputFile.Close
Set objInputFile = Nothing
Set objOutputFile = Nothing
Set objFSO = Nothing
No problem...glad you came up with a working solution... =)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window