Every day we get a flat text file. Some days there are lines in the file that need to be deleted before it can be processed. These lines can appear in different places, but always start with the characters 6999 or 7999. We would like to run a script that will delete these particular lines. However, and this is way beyond me, any where there is a line that starts 6999 there will be a line immediately before it that starts 5442 that also needs to be deleted, but only if it appears immediately before the 6999 line.
We are a Windows shop and would run this script as part of a simple batch file in Windows. We do not use Unix or Linux nor desire to.
The file name extension reflects the date. today's file is file.100621, tomorrow's will be file.100622. I am having trouble with this aspect, as it seems vbscript does not like file.*
Here is a sample of the text file:
4006006602 03334060000100580
40060066039 0334070000100580
700600000011571006210060001255863
544264287250111000025000000000040008000801
6999001000000000000000000000000000000000000000000000000000
6999001000000000000000000000000000000000000000000000000000
6999001000000000000000000000000000000000000000000000000000
799900000011571006210030000000000
8007000000115710062102530054008920
We'd like to remove 5 lines in this file (the 5442 line, the three 6999 lines, and the 7999 line).
Here is a sample of the script that I found on this site, have modified and had some success, but don't know the way to delete the lines (only know how to replace data in the line). I realize this will either need major modifications or need to be thrown out altogether, but I post this to provide an idea of what I think we are looking for. I put this in a directory with the cscript.exe and call it from a simple batch file:
Set objFS = CreateObject("Scripting.FileSystemObject")
strFile = "c:\temp\file.100621"
Set objFile = objFS.OpenTextFile(strFile)
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
If InStr(strLine,"6999")> 0 Then
strLine = Replace(strLine,"6999","delete line")
End If
WScript.Echo strLine
Loop
Which gets me this:
40060066039 0334070000100580
700600000011571006210060001255863
544264287250111000025000000000040008000801
delete line001000000000000000000000000000000000000000000000000000
delete line001000000000000000000000000000000000000000000000000000
delete line001000000000000000000000000000000000000000000000000000
799900000011571006210030000000000
8007000000115710062102530054008920
Close! just need to delete lines instead of write "delete line". So here are my specific needs based on what I know:
I made some changes to try to eliminate the blank line, I also added a function to loop through the output file and remove any blank lines. Hope this one works.
Select Case Wscript.Arguments.Count
case 1:
strInput = GetFile(WScript.Arguments(0))
RemoveUnwantedLines strInput, strInput
RemoveBlankLines strInput
case 2:
strInput = GetFile(WScript.Arguments(0))
strOutput = Wscript.Arguments(1)
RemoveUnwantedLines strInput, strOutput
RemoveBlankLines strOutput
End Select
Function GetFile(strDirectory)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strDirectory)
dateLastModified = Null
strFile = ""
For Each objFile in objFolder.Files
If IsNull(dateLastModified) Then
dateLastModified = objFile.DateLastModified
strFile = objFile.Path
ElseIf dateLastModified < objFile.DateLastModified Then
dateLastModified = objFile.DateLastModified
strFile = objFile.Path
End If
Next
GetFile = strFile
End Function
Sub RemoveUnwantedLines(strInputFile, strOutputFile)
'Open the file for reading.
Set objFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(strInputFile,1)
'Read the entire file into memory.
strFileText = objFile.ReadAll
'Close the file.
objFile.Close
'Split the file at the new line character. *Use the Line Feed character (Char(10))
arrFileText = Split(strFileText,Chr(10))
'Open the file for writing.
Set objFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(strOutputFile,2,true)
'Loop through the array of lines looking for lines to keep.
For i = LBound(arrFileText) to UBound(arrFileText)
'If the line is not blank process it.
If arrFileText(i) <> "" Then
'If the line starts "5442", see if the next line is "6999".
If Left(arrFileText(i),4) = "5442" Then
'Make sure the next line exists (Don't want an out of bounds exception).
If i + 1 <= UBound(arrFileText)Then
'If the next line is not "6999"
If Left(arrFileText(i + 1), 4) <> "6999" Then
'Write the "5442" line to the file.
objFile.WriteLine(arrFileText(i))
End If
Else
'If the next line does not exist, write the "5442" line to the file (without a new line).
objFile.WriteLine(arrFileText(i))
End If
'If the line does not start with "6999" and the line does not start with "7999".
Elseif Left(arrFileText(i),4) <> "6999" AND Left(arrFileText(i),4) <> "7999" Then
'Write the line to the file.
objFile.WriteLine(arrFileText(i))
End If
End If
Next
'Close the file.
objFile.Close
Set objFile = Nothing
End Sub
Sub RemoveBlankLines(strInputFile)
Set objFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(strInputFile,1)
'Read the entire file into memory.
strFileText = objFile.ReadAll
'Close the file.
objFile.Close
'Split the file at the new line character.
arrFileText = Split(strFileText,VbNewLine)
Set objFile = CreateObject("Scripting.FileSystemObject").OpenTextFile(strInputFile,2,true)
'Loop through the array of lines looking for lines to keep.
For i = LBound(arrFileText) to UBound(arrFileText)
'If the line is not blank.
if arrFileText(i) <> "" Then
'If there is another element.
if i + 1 <= UBound(arrFileText) Then
'If the next element is not blank.
if arrFileText(i + 1) <> "" Then
'Write the line to the file.
objFile.WriteLine(arrFileText(i))
Else
'Write the line to the file (Without a blank line).
objFile.Write(arrFileText(i))
End If
Else
'Write the line to the file (Without a blank line).
objFile.Write(arrFileText(i))
End If
End If
Next
'Close the file.
objFile.Close
Set objFile = Nothing
End Sub
To use it call it from the command line in one of two ways.
RemoveUnwantedLines "C:\TestDirectory\" "C:\Output.txt"
or
RemoveUnwantedLines "C:\TestDirectory\"