Deleting lines with vbscript

November 2, 2010 at 15:37:01
Specs: Windows 7
I have a text file with the following data;

SB_17_MP_OM_OIL,01/11/2010,15:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,16:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,16:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,17:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,17:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,18:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,18:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,19:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,19:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,20:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,20:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,21:00,1967.066772
SB_17_MP_OM_OIL,01/11/2010,21:30,1970.665894
SB_17_MP_OM_OIL,01/11/2010,22:00,1972.165527
SB_17_MP_OM_OIL,01/11/2010,22:30,1973.265259
SB_17_MP_OM_OIL,01/11/2010,23:00,1974.464966
SB_17_MP_OM_OIL,01/11/2010,23:30,1975.664673

What i need to be able to do is, starting from the bottom line, look at the values at the end of each line and when there are two values the same, delete all the lines above the duplicate values. So looking at the above, the script would start at the bottom line containing SB_17_MP_OM_OIL,01/11/2010,23:30,1975.664673
and when it reaches the first line containing SB_17_MP_OM_OIL,01/11/2010,20:30,1966.766846
as there is a duplicate line above this one, delete all lines above this line leaving the text file with the following data
SB_17_MP_OM_OIL,01/11/2010,20:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,21:00,1967.066772
SB_17_MP_OM_OIL,01/11/2010,21:30,1970.665894
SB_17_MP_OM_OIL,01/11/2010,22:00,1972.165527
SB_17_MP_OM_OIL,01/11/2010,22:30,1973.265259
SB_17_MP_OM_OIL,01/11/2010,23:00,1974.464966
SB_17_MP_OM_OIL,01/11/2010,23:30,1975.664673
Any help would be appreciated.


See More: Deleting lines with vbscript

Report •


#1
November 3, 2010 at 00:03:27
Uhm, use some good ole' unix text processing apps - you can get unixkit-tiny for Windows to get these tools.

cat <file> | sort -r | uniq -s 33 | sort

Example

# cat wt
SB_17_MP_OM_OIL,01/11/2010,15:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,16:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,16:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,17:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,17:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,18:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,18:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,19:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,19:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,20:00,1966.766846
SB_17_MP_OM_OIL,01/11/2010,20:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,21:00,1967.066772
SB_17_MP_OM_OIL,01/11/2010,21:30,1970.665894
SB_17_MP_OM_OIL,01/11/2010,22:00,1972.165527
SB_17_MP_OM_OIL,01/11/2010,22:30,1973.265259
SB_17_MP_OM_OIL,01/11/2010,23:00,1974.464966
SB_17_MP_OM_OIL,01/11/2010,23:30,1975.664673
#
# cat wt | sort -r | uniq -s 33 | sort
SB_17_MP_OM_OIL,01/11/2010,20:30,1966.766846
SB_17_MP_OM_OIL,01/11/2010,21:00,1967.066772
SB_17_MP_OM_OIL,01/11/2010,21:30,1970.665894
SB_17_MP_OM_OIL,01/11/2010,22:00,1972.165527
SB_17_MP_OM_OIL,01/11/2010,22:30,1973.265259
SB_17_MP_OM_OIL,01/11/2010,23:00,1974.464966
SB_17_MP_OM_OIL,01/11/2010,23:30,1975.664673
#


Report •

#2
November 3, 2010 at 00:24:51
Oh that's not VBscript, sorry

Report •

#3
November 4, 2010 at 12:19:09
Seems like there should be a better way to do this, but this was the easiest. Also, I don't know how large the input file is, but I read it all into memory. You have been warned.
With CreateObject("Scripting.FileSystemObject")
  inLines = Split(.OpenTextFile("in.csv").ReadAll, vbNewLine)
  lineCnt = UBound(inLines)
  ReDim useLine(lineCnt)
  Set outFile = .OpenTextFile("out.csv", 2, True)
End With

'Mark lines for output
For i = lineCnt To 0 Step -1
  valStart = InStrRev(inLines(i), ",")
  If (valStart) Then
    thisVal = Mid(inLines(i), valStart)
    useLine(i) = thisVal <> prevVal
    prevVal = thisVal
  End If
Next

'Output marked lines
For i = 0 To lineCnt
  If useLine(i) Then _
    outFile.WriteLine inLines(i)
Next
WScript.Echo "Done"

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
November 4, 2010 at 18:41:46
Very close Razor2.3, I can do somethings in vbscript but you have me entering new territory with your code, which is a good thing because it's helping me learn.
I ran your script and although it did delete duplicate lines the output contained more data than I needed. Hoping to make myself clear, when the script runs (from the bottom and comes to two lines (together) with duplicate values all I need to do is output the bottom line up until the first duplicate line and then just delete everything above that line. So the example from my first post would delete everything above the line containing SB_17_MP_OM_OIL,01/11/2010,20:30,1966.766846
so the output file would only have the bottom 7 lines.

Appreciate the efforts from all posts.


Report •

#5
November 5, 2010 at 09:51:55
So scan up, and stop scanning when you hit a duplicate value?
With CreateObject("Scripting.FileSystemObject")
  inLines = Split(.OpenTextFile("in.csv").ReadAll, vbNewLine)
  lineCnt = UBound(inLines)
  Set outFile = .OpenTextFile("out.csv", 2, True)
End With

'Find starting line
For i = lineCnt To 0 Step -1
  valStart = InStrRev(inLines(i), ",")
  If (valStart) Then
    thisVal = Mid(inLines(i), valStart)
    If (thisVal = prevVal) Then _
      Exit For
    prevVal = thisVal
  End If
Next

'Output lines
For i = i + 1 To lineCnt
  outFile.WriteLine inLines(i)
Next
WScript.Echo "Done"

How To Ask Questions The Smart Way


Report •

#6
November 5, 2010 at 15:32:29
Genius, how did you learn all this? I will have to go through my script book to find out what some of the statements mean.

Appreciate your time and effort.


Report •

#7
November 6, 2010 at 14:10:26
Thanks Razor2.3, this is what I eneded up doing with your help. Appreciate the effort and time.

Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Dim FNameArray(10)

FNameArray(0) = "SB_32_MP_OM_OIL.txt"
FNameArray(1) = "GB_BA_MP_OIL.txt"
FNameArray(2) = "SB_11_MT_OM_11O.txt"
FNameArray(3) = "SB_23_MP_OM_23O.txt"
FNameArray(4) = "SB_CT_MT_OM_OIL.txt"
FNameArray(5) = "SB_17_MP_OM_OIL.txt"
FNameArray(6) = "GV_OM_OMP.txt"
FNameArray(7) = "MA_MT_BOM.txt"
FNameArray(8) = "SP_BM_BLG.txt"
FNameArray(9) = "SP_BM_FSG.txt"
FNameArray(10) = "SP_BM_ING.txt"

For fna = 0 To 10

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.MoveFile "C:\TEAM Data\" & FNameArray(fna) , "C:\Temp\" & FNameArray(fna)

With objFSO
inLines = Split(.OpenTextFile("C:\Temp\" & FNameArray(fna)).ReadAll, vbNewLine)
lineCnt = UBound(inLines)
Set outputFile = .OpenTextFile("C:\TEAM Data\" & FNameArray(fna), 2, True)
End With

'Find starting line
For i = lineCnt To 0 Step -1
valStart = InStrRev(inLines(i), ",")
If (valStart) Then
thisVal = Mid(inLines(i), valStart)
If (thisVal = prevVal) Then _
Exit For
prevVal = thisVal
End If
Next

'Output lines
For i = i + 1 To lineCnt
outputFile.WriteLine inLines(i)
Next

outputFile.Close

objFSO.DeleteFile("C:\Temp\" & FNameArray(fna))

Next

MsgBox ("File Clean Up Completed")


Report •

#8
November 6, 2010 at 14:37:22
You might want to clear out prevVal after closing the file; the original script assumes that variable starts out empty.

How To Ask Questions The Smart Way


Report •

#9
June 8, 2011 at 09:51:42
23:48:04 User Name: MOSSSearch Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 172.19.32.14

23:47:53 User Name: Kipucamayoc Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 172.19.50.128

23:47:46 User Name: Kipucamayoc Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 192.168.1.106

this is what i have in 2 text files(i mean the information), i want a script that read the contents of this txt files, join and eliminate duplicates lines and put the result in a new txt file , but there´s a problem the hour on this lines always is different , i wat the script omit the hour on every line the result must be show like this:

23:47:46 User Name: Kipucamayoc Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 192.168.1.106

23:48:04 User Name: MOSSSearch Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 172.19.32.14

i reaaly apreciatte if you could give me a hand with this problem



Report •

#10
June 8, 2011 at 09:53:23
23:48:04 User Name: MOSSSearch Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 172.19.32.14

23:47:53 User Name: Kipucamayoc Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 172.19.50.128

23:47:46 User Name: Kipucamayoc Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 192.168.1.106

this is what i have in 2 text files(i mean the information), i want a script that read the contents of this txt files, join and eliminate duplicates lines and put the result in a new txt file , but there´s a problem the hour on this lines always is different , i wat the script omit the hour on every line the result must be show like this:

23:47:46 User Name: Kipucamayoc Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 192.168.1.106

23:48:04 User Name: MOSSSearch Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 172.19.32.14

i reaaly apreciatte if you could give me a hand with this problem


my email is : <removed>@hotmail.com


pd: i want this script read all txt files *.txt

edited by moderator: Removed email address -Razor2.3


Report •

#11
June 8, 2011 at 09:56:49

sorry must show like this:

User Name: Kipucamayoc Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 192.168.1.106

User Name: MOSSSearch Pre-Authentication Type: 0x0 Failure Code: 0x19 Client Address: 172.19.32.14


Report •


Ask Question