Solved

Parse text file using VbScript

Posted on 2008-10-07
6
794 Views
Last Modified: 2012-08-14
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->TRAILERS]
D08-10-06 10:07:28.593 XXX:390823892aw4dca: -rate(Dlr/u):94.99838 for ASDRFACSD:[TRAILERS->AIRPLANE]

Results Requested:

DLR,Rate,Method
Dlr/u,15,TRUCKS->TRAILERS
Dlr/u,94,TRAILERS->AIRPLANE

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

Open in new window

0
Comment
Question by:wildatom
  • 3
6 Comments
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 22663303
Try this out...
Option Explicit 
 

CONST ForReading = 1  
 

Dim objFSO, objLog 

Dim strInputLog, strOutputLog, strData, strLine, arrLines

Dim DLR, Rate, Method

Dim DlrTagLeft, DlrTagRight, rateTag, methodTag

Dim valuesA, valuesB, valuesC, valuesD 

 

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) 

 

' Sample Input Line:

' D08-10-06 10:05:27.010 XXX:390823892aw4dca: -rate(Dlr/u):15.792581 for ASDGADFSCSS:[TRUCKS->TRAILERS]
 

' Sample Output Line:

' Dlr/u,15,TRUCKS->TRAILERS
 
 

DlrTagLeft = "-rate("

DlrTagRight = "):"

rateTag = " for "

methodTag = ":["
 

For Each strLine in arrLines 

    If strLine <> "" Then

        valuesA = Split(strLine, dlrTagLeft)

        valuesB = Split(valuesA(1), dlrTagRight)

        DLR = valuesB(0)

        valuesC = Split(valuesB(1), rateTag)

        Rate = CInt(valuesC(0))

        valuesD = Split(valuesC(1), methodTag)

        Method = Left(valuesD(1), Len(valuesD(1)) - 1)

        objLog.Writeline Join(Array(DLR, Rate, Method), ",")

    End If

Next

Open in new window

0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 22663342
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!
0
 

Author Comment

by:wildatom
ID: 22670801
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.FileSystemObject")
Set objInputFile = objFSO.OpenTextFile(strInputLog, ForReading)

Set objOutputFile = objFSO.CreateTextFile(strOutputLog)

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
0
 
LVL 85

Expert Comment

by:Mike Tomlinson
ID: 22670860
No problem...glad you came up with a working solution...  =)

0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22956131
PAQed with points refunded (125)

Computer101
EE Admin
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now