Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Parse text file using VbScript

Posted on 2008-10-07
6
Medium Priority
?
810 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 86

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 86

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 86

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
For most people, the WrapPanel seems like a magic when they switch from WinForms to WPF. Most of us will think that the code that is used to write a control like that would be difficult. However, most of the work is done by the WPF engine, and the W…
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

705 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