Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

Parse text file

I need a vbscript that does the following:

Parse the file:  c:\sos\raw.txt & place results in c:\Manager Files\SOS folder with computer name & yesterday’s date in filename:  so file created today on computer named BK01442 would be named BK01442-051911-PRaw.txt.
I need to copy any lines containing the following words into the parsed file:
•      Car_Pull_In
•      Delete_Auto_Pullout
•      Delete_Auto_Over
•      Power_Down
•      Power_Up

Any help would be appreciated.  Thanks.
0
BigBobBK
Asked:
BigBobBK
  • 4
  • 4
  • 4
2 Solutions
 
prashanthdCommented:
Try the following

regards
Prashanth
Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputer = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
WScript.Echo "Computer Name: " & strComputer
Set objTextFile = objFSO.OpenTextFile("c:\sos\raw.txt", ForReading)
wfile="c:\Manager Files\SOS\"&strcomputer&"-"&Month(Date)&Day(Date)&Year(Date)&"-PRAW.txt"
objfso.CreateTextFile(wfile)
Set objWriteFile = objFSO.OpenTextFile(wfile, ForWriting)

Do Until objTextFile.AtEndOfStream
    strNextLine = objTextFile.Readline
    If InStr(strnextline,"Car_Pull_In")>0 Or InStr(strnextline,"Delete_Auto_Pullout")>0 Or InStr(strnextline,"Delete_Auto_Over")>0 Or InStr(strnextline,"Power_Down")>0 Or InStr(strnextline,"Power_Up")>0 Then 
        objWriteFile.WriteLine strnextline
    End If
Loop

objWriteFile.Close
Set objTextFile

Open in new window

0
 
BigBobBKAuthor Commented:
prashanthd,

I tried this but get "800A03F3" error on Liine 21 Char 17.  Error says Expected =.
0
 
Bill PrewCommented:
Here's how I would approach it:

' Define some needed constants
Const ForReading = 1
Const ForWriting = 2
Const cInFile = "c:\sos\raw.txt"
Const cOutFilePrefix = "c:\Manager Files\SOS\"
Const cOutFileSuffix = "-PRAW.txt"
arrMatches = Array("Car_Pull_In", "Car_Pull_In", "Delete_Auto_Pullout", "Delete_Auto_Over", "Power_Down", "Power_Up")

' Create needed filesystem and shell objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshShell = WScript.CreateObject("WScript.Shell")

' Build output file name from constants above, computername, and date (MMDDYY)
strDateStamp = Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & Right("0" & Year(Date), 2)
strComputerName = wshShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
strOutFile = cOutFilePrefix & strComputerName & "-" & strDateStamp & cOutFileSuffix

' Open input and output file
Set objInFile = objFSO.OpenTextFile(cInFile, ForReading)
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Read each line from input file, if it matches a pattern output to new file
Do Until objInFile.AtEndOfStream
   strData = objInFile.Readline
   For Each strMatch in arrMatches
      If Instr(LCase(strData), LCase(strmatch)) > 0 Then
         objOutFile.WriteLine strData
         Exit For
      End If
   Next
Loop

' Wrap up
objInFile.Close
objOutFile.Close
Wscript.Quit

Open in new window

~bp
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
prashanthdCommented:
Replace line no 21 with following

objTextFile.close
0
 
BigBobBKAuthor Commented:
This works but saw 2 issues when I looked at data.  

1.  First it is hard to read.  I changed the ".txt" extension to ".xls" so an excel file is created.  It works but when I open the file I get the msg "File is in a different format then specified by file extension".  Is there a better way to parse to an excel file?

2.  The file does not have the headers from the original file.  In this file the header text is in the very first line.  How can I put the headers in the parsed file.

Thank you very much for the help.
0
 
prashanthdCommented:
Could you post sample input file of raw.txt?
0
 
BigBobBKAuthor Commented:
Attached is the file I'm trying to parse.  Thanks so much.
RAW.TXT
0
 
Bill PrewCommented:
This will add the header line.

To view it in Excel, leave it as a TXT file, then start Excel, and open the TXT file.  It will pop up a window asking if it is delimted, selected that option, and use TAB delimited.  That should look decent in Excel.

' Define some needed constants
Const ForReading = 1
Const ForWriting = 2
Const cInFile = "c:\sos\raw.txt"
Const cOutFilePrefix = "c:\Manager Files\SOS\"
Const cOutFileSuffix = "-PRAW.txt"
arrMatches = Array("Car_Pull_In", "Car_Pull_In", "Delete_Auto_Pullout", "Delete_Auto_Over", "Power_Down", "Power_Up")

' Create needed filesystem and shell objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshShell = WScript.CreateObject("WScript.Shell")

' Build output file name from constants above, computername, and date (MMDDYY)
strDateStamp = Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & Right("0" & Year(Date), 2)
strComputerName = wshShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
strOutFile = cOutFilePrefix & strComputerName & "-" & strDateStamp & cOutFileSuffix

' Open input and output file
Set objInFile = objFSO.OpenTextFile(cInFile, ForReading)
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

blnHeader = True

' Read each line from input file, if it matches a pattern output to new file
Do Until objInFile.AtEndOfStream
   strData = objInFile.Readline
   If blnHeader Then
      objOutFile.WriteLine strData
   Else
      For Each strMatch in arrMatches
         If Instr(LCase(strData), LCase(strmatch)) > 0 Then
            objOutFile.WriteLine strData
            Exit For
         End If
      Next
   End If
Loop

' Wrap up
objInFile.Close
objOutFile.Close
Wscript.Quit

Open in new window

~bp
0
 
prashanthdCommented:
Try the following code..

As Bill had already mentioned, import the output file to excel using TAB delimited.

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshShell = WScript.CreateObject( "WScript.Shell" )
strComputer = wshShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
WScript.Echo "Computer Name: " & strComputer
Set objTextFile = objFSO.OpenTextFile("c:\sos\raw.txt", ForReading)
wfile="c:\Manager Files\SOS\"&strcomputer&"-"&Month(Date)&Day(Date)&Year(Date)&"-PRAW.txt"
objfso.CreateTextFile(wfile)
Set objWriteFile = objFSO.OpenTextFile(wfile, ForWriting)
hctr=0
Do Until objTextFile.AtEndOfStream
    strNextLine = objTextFile.ReadLine
    If hctr=0 Then
    	objWriteFile.WriteLine strnextline
    	hctr=1
    End If
    If InStr(strnextline,"Car_Pull_In")>0 Or InStr(strnextline,"Delete_Auto_Pullout")>0 Or InStr(strnextline,"Delete_Auto_Over")>0 Or InStr(strnextline,"Power_Down")>0 Or InStr(strnextline,"Power_Up")>0 Then 
        objWriteFile.WriteLine strnextline
    End If
Loop

objWriteFile.Close
objTextFile.Close

Open in new window

0
 
Bill PrewCommented:
Ah yes, a correction to my last post, need to flip the header flag...

' Define some needed constants
Const ForReading = 1
Const ForWriting = 2
Const cInFile = "c:\sos\raw.txt"
Const cOutFilePrefix = "c:\Manager Files\SOS\"
Const cOutFileSuffix = "-PRAW.txt"
arrMatches = Array("Car_Pull_In", "Car_Pull_In", "Delete_Auto_Pullout", "Delete_Auto_Over", "Power_Down", "Power_Up")

' Create needed filesystem and shell objects
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set wshShell = WScript.CreateObject("WScript.Shell")

' Build output file name from constants above, computername, and date (MMDDYY)
strDateStamp = Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & Right("0" & Year(Date), 2)
strComputerName = wshShell.ExpandEnvironmentStrings("%COMPUTERNAME%")
strOutFile = cOutFilePrefix & strComputerName & "-" & strDateStamp & cOutFileSuffix

' Open input and output file
Set objInFile = objFSO.OpenTextFile(cInFile, ForReading)
Set objOutFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

blnHeader = True

' Read each line from input file, if it matches a pattern output to new file
Do Until objInFile.AtEndOfStream
   strData = objInFile.Readline
   If blnHeader Then
      objOutFile.WriteLine strData
      blnHeader = False
   Else
      For Each strMatch in arrMatches
         If Instr(LCase(strData), LCase(strmatch)) > 0 Then
            objOutFile.WriteLine strData
            Exit For
         End If
      Next
   End If
Loop

' Wrap up
objInFile.Close
objOutFile.Close
Wscript.Quit

Open in new window

~bp
0
 
BigBobBKAuthor Commented:
Both of you did a great job.  Help is greatly appreciated.
0
 
Bill PrewCommented:
Welcome, glad to help, thanks.

~bp
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now