Parse text file

prashanthd,

I tried this but get "800A03F3" error on Liine 21 Char 17.  Error says Expected =.

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

Select allOpen in new window

~bp

Replace line no 21 with following

objTextFile.close

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.

Could you post sample input file of raw.txt?

Attached is the file I'm trying to parse.  Thanks so much.
RAW.TXT

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

Select allOpen in new window

~bp

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

Select allOpen in new window

Both of you did a great job.  Help is greatly appreciated.

Welcome, glad to help, thanks.

~bp