Delete Row from Text File

[dpgirl]

I don't know if VBScript is the best solution for my problem so feel free to suggest other options. I have a text file containing 100K+ records. I would like to delete all rows in the file that start with a space or double-quotes (") or the words "Grand Total". Would a VBScript be the best option and if so, can someone help me with the script as I am clueless. I found the below script on the web -- is this something that I can use to adapt to my situation?


Dim fso As Object
Dim tsOld As Object
Dim tsNew As Object
Dim strLine As String
Const ForReading = 1
Const ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsOld = fspenTextFile("c:\temp\DART\joined.csv", ForReading)
Set tsNew = fspenTextFile("c:\temp\DART\joined_new.csv", ForWriting, True)
Do While Not tsOld.AtEndOfStream
strLine = tsOld.ReadLine
If Left$(strLine, 1) <> """"" Then tsNew.WriteLine
Loop
tsNew.Close
tsOld.Close

 

[tsuji]

Brut force as sketched is just adding a couple of conditionals: nothing wrong with it for small number of well-defined criteria.

>If Left$(strLine, 1) <> """"" Then tsNew.WriteLine
There isn't left$(), vbs only implements left().
[tt]If Left(strLine, 1)<>" " or Left(strLine,1" <> """"" or strcomp(Left(strLine,11),"Grand Total")<>0 Then tsNew.WriteLine[/tt]

 

[tsuji]

A typo there after the 2nd left(). Here is the amendment.
[tt] If Left(strLine, 1)<>" " or Left(strLine,1[red])[/red] <> """"" or strcomp(Left(strLine,11),"Grand Total")<>0 Then tsNew.WriteLine [/tt]

 

[tsuji]

Missed the string, sorry! Here is the correct line.
[tt] If Left(strLine, 1)<>" " or Left(strLine,1[red])[/red] <> """"" or strcomp(Left(strLine,11),"Grand Total")<>0 Then tsNew.WriteLine [red]strLine[/red][/tt]

 

[dpgirl]

Thank you tsuji! I am getting a compilation error still. Any clue what's wrong? Also, should the "If" statement be based on "and" instead of "or"?


Dim fso As Object
Dim tsOld As Object
Dim tsNew As Object
Dim strLine As String
Const ForReading = 1
Const ForWriting = 2

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsOld = fspenTextFile("c:\temp\DART\MV_All.csv", ForReading)
Set tsNew = fspenTextFile("c:\temp\DART\MV_All_new.csv", ForWriting, True)
Do While Not tsOld.AtEndOfStream
strLine = tsOld.ReadLine
If Left(strLine,1) <> """"" or strcomp(Left(strLine,14),"Proposal Total")<>0 Then tsNew.WriteLine strLine
Loop
tsNew.Close
tsOld.Close

 

[dpgirl]

Okay, I figured out the problem. The quotes within the quotes was the problem. Here is a total revised script in case anyone is interested.


Option Explicit

Dim strFileSourcePath, strFileTargetPath, objFSOSource, objFSOTarget, fso, objFilesSource, objFilesTarget, strCurrentLine

Const ForReading = 1, ForWriting = 2

strFileSourcePath = "c:\temp\DART\MV_All.csv"
strFileTargetPath = "c:\temp\DART\MV_All_new.csv"

Set objFSOSource = CreateObject("scripting.filesystemobject")
Set objFSOTarget = CreateObject("scripting.filesystemobject")

Set objFilesSource = objFSOSource.OpenTextFile(strFileSourcePath,ForReading,True,0)
Set objFilesTarget = objFSOSource.OpenTextFile(strFileTargetPath,ForWriting,True,0)
Set fso = CreateObject("Scripting.FileSystemObject")

Do While objFilesSource.AtEndOfStream <> True
strCurrentLine = objFilesSource.ReadLine

if StrComp(Left(strCurrentLine,1),chr(34)) <> 0 and StrComp(Left(strCurrentLine,14),"Proposal Total") <> 0 and Len(strCurrentLine) > 0 then
objFilesTarget.WriteLine strCurrentLine
else

end if

Loop

objFilesSource.Close
objFilesTarget.Close

Set objFSOSource = Nothing
Set objFSOTarget = Nothing

Set fso = Nothing

 

[tsuji]

>Also, should the "If" statement be based on "and" instead of "or"?
Sure, it is "and", my bad.