vbscript remove line from text file if duplicate found

[Nu2Java]

Hi All,

I am using a vbscript file to count lines of data for a specific date. Based on this sample data from my text file, how can I remove lines that contain a duplicate after the first comma?

8-879003-02_TOP,B1021,218,149,4/18/2022 5:37:24 AM
8-879003-02_TOP,B1022,403,80,4/18/2022 6:11:50 AM
8-879003-02_TOP,B1022,327,288,4/18/2022 6:17:20 AM
8-879003-02_TOP,B1022,112,89,4/18/2022 6:17:37 AM
8-879003-02_BTM,B1022,463,55,4/18/2022 6:33:44 AM
8-879003-02_BTM,B1022,741,55,4/18/2022 6:33:49 AM
8-879003-02_BTM,B1022,857,59,4/18/2022 6:33:59 AM
8-879003-02_BTM,B1022,1024,55,4/18/2022 6:34:02 AM
8-879003-02_BTM,B1022,284,669,4/18/2022 6:34:13 AM

So from the data above, this would become only 2 lines as it would count only B1021 & B1022 and remove all other duplicates. Thanks for any help.

**Note - This is a duplicate from:

https://www.vbforums.com/showthread...remove-line-from-text-file-if-duplicate-found

 

[mikrom]

But which of the B1022-lines you need as result, first or last ?

 

[strongm]

here's one way

[COLOR=blue]    Set out = CreateObject("Scripting.Dictionary")

    With CreateObject("scripting.filesystemobject").OpenTextFile("d:\downloads\deleteme\source.txt")
        Set outfile = CreateObject("scripting.filesystemobject").CreateTextFile("d:\downloads\deleteme\dest.txt", True)
        Do
            mystring = .ReadLine
            On Error Resume Next
                out.Add Split(mystring, ",")(1), mystring
                If Err = 0 Then outfile.WriteLine mystring
            On Error GoTo 0
        Loop Until .AtEndOfStream
    End With[/color]

 

[mikrom]

Is the file sorted on second column, like you posted above, i.e. first all lines with B1021 come together one after the other, then all lines with B1022 come together one after the other etc.. ?
Or it's not that case, i.e. for example something like this can occur?

8-879003-02_TOP,B1022,403,80,4/18/2022 5:37:50 AM
8-879003-02_TOP,B1021,112,89,4/18/2022 6:17:37 AM
8-879003-02_BTM,B1022,463,55,4/18/2022 6:33:44 AM
8-879003-02_BTM,B1023,741,55,4/18/2022 6:33:49 AM
8-879003-02_BTM,B1021,857,59,4/18/2022 6:33:59 AM
8-879003-02_BTM,B1022,1024,55,4/18/2022 6:34:02 AM
8-879003-02_BTM,B1021,284,669,4/18/2022 6:34:13 AM

 

[Nu2Java]

Thanks strongm, I will give this a try and report back.

mikrom: you are correct, these can be in various orders and also will rarely match a pattern. to give more detail on this, I am going to simply use it to count parts from all of the data by date, but of course I need to exclude duplicates and it doesn't matter which line remains. These are serial numbers of parts, so I just need to get an accurate count.

 

[mikrom]

My second question about order is superfluous, since strongm has already posted a universal solution with a dictionary.

 

[Nu2Java]

Thanks to both of you for your help and time. >>> strongm, this is working great! Thanks a lot.

 

[strongm]

>so I just need to get an accurate count.

That info is immediately available as [tt]out.count[/tt]...

 

[Nu2Java]

Hi all.. I have run into a problem here and I am unsure what is causing it. Here is the entire code I am using. When I run just the data file only, the count is correct. When I run it with my additional code, it is missing some serial numbers that I guess it did not accept? Or I must be doing something wrong someplace else.

window.moveTo 0, 0

Sub Window_OnLoad
  	Dim width,height
  	width=250
  	height=130
  	self.ResizeTo width,height
  	self.MoveTo (screen.AvailWidth-width)/2,(screen.AvailHeight-height)/2
  'This method will be called when the application loads
  'Add your code here
End Sub


Sub OnClickButtonCancel()
  window.Close
End Sub

Sub FPY

'On Error Resume Next

 Const ForReading = 1, ForWriting = 2, ForAppending = 8, CreateIfNeeded = True
 Dim strSearchFor, strSearchWrd, LineCount, objFSO, objTextFile, arrLines, ask, objLogFile, DateCount, fpy, strEmployee1, strEmployee2, strEmployee3, strEmployee4
 
 Set WshShell = CreateObject("WScript.Shell")
 strDir = WshShell.CurrentDirectory & "\"
 
 
'***Delete File****
Dim objFileSys
Set objFileSys = CreateObject("Scripting.FileSystemObject")
If objFileSys.FileExists(strDir & "Formatted-Defect-Data.txt") Then
    objFileSys.DeleteFile strDir & "Formatted-Defect-Data.txt"
End If

 Set out = CreateObject("Scripting.Dictionary")

    With CreateObject("scripting.filesystemobject").OpenTextFile(strDir & "Defects.txt")
        Set outfile = CreateObject("scripting.filesystemobject").CreateTextFile(strDir & "Formatted-Defect-Data.txt", True)
        Do
            mystring = .ReadLine
            On Error Resume Next
                out.Add Split(mystring, ",")(1), mystring
                If Err = 0 Then outfile.WriteLine mystring
            On Error GoTo 0
        Loop Until .AtEndOfStream
    End With
 
 If WeekDayName(WeekDay(Now())) = "Monday" Then
 strSearchFor = Date - 3
 Else
 strSearchFor = Date - 1
 End If
 
 strSearchWrd = "NO DEFECTS"
 strEmployee1 = "thvan"
 strEmployee2 = "LiTran"
 strEmployee3 = "kimtran"
 strEmployee4 = "kmarionn"

 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objTextFile = objFSO.OpenTextFile(strDir & "Formatted-Defect-Data.txt", ForReading)
 
 LineCount = 0
 DateCount = 0
 
 do until objTextFile.AtEndOfStream

 strLine = objTextFile.ReadLine()
 If InStr(strLine, strEmployee1) <> 0 Or InStr(strLine, strEmployee2) <> 0 Or InStr(strLine, strEmployee3) <> 0 Or InStr(strLine, strEmployee4) <> 0 Then
  If InStr(strLine, strSearchFor) <> 0 then
   DateCount = DateCount + 1
  If InStr(strLine, strSearchWrd) <> 0 then
   LineCount = LineCount + 1
  End If
  End If
  End If
 loop
 
 fpy = FormatPercent(LineCount/DateCount,1)
 
 MsgBox "Run Date: " & strSearchFor & vbCrlf & vbCrlf & "Total Boards Inspected:  " & DateCount & vbCrlf & vbCrlf & _
"Total Passed:  " & LineCount & vbCrlf & vbCrlf & "FPY:  " & fpy,, "Flow Line FPY"
 objTextFile.Close
 
 Set objLogFile = objFSO.OpenTextFile(strDir & "DAILY_FPY.txt", ForAppending, True)
 objLogFile.Write "Run Date," & strSearchFor & ", Total Boards," & DateCount & ", Total Passed," & LineCount & ", FPY," & fpy & vbCrlf
 objLogFile.Close
 
End Sub

Here is the data I ran today. This is in the exact format as it is processed. I get a total count of 26, but it should be 36

8-879003-02_TOP,B1024,183,489,4/25/2022 5:22:32 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,163,220,4/25/2022 5:22:53 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,180,432,4/25/2022 5:24:07 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,198,546,4/25/2022 5:24:29 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,805,490,4/25/2022 5:24:40 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,172,355,4/25/2022 5:24:48 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,162,268,4/25/2022 5:24:57 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,803,204,4/25/2022 5:27:09 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,531,204,4/25/2022 5:27:21 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,126,177,4/25/2022 5:30:36 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,124,260,4/25/2022 5:30:43 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,129,217,4/25/2022 5:30:51 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,278,246,4/25/2022 5:30:56 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,282,271,4/25/2022 5:31:05 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,882,598,4/25/2022 5:31:55 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1024,887,602,4/25/2022 5:32:04 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_BTM,B1024,872,506,4/25/2022 5:32:58 AM,kimtran,No Solder,Color [LightSkyBlue],A2524140,
8-879003-02_BTM,B1024,781,505,4/25/2022 5:33:10 AM,kimtran,No Solder,Color [LightSkyBlue],A2524140,
8-879003-02_BTM,B1024,887,308,4/25/2022 5:33:25 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_BTM,B1024,883,308,4/25/2022 5:33:30 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_BTM,B1024,912,284,4/25/2022 5:33:33 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_BTM,B1024,918,292,4/25/2022 5:33:38 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_TOP,B1025,415,661,4/25/2022 6:04:48 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,372,664,4/25/2022 6:04:55 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,635,670,4/25/2022 6:05:00 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,632,658,4/25/2022 6:05:05 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,952,660,4/25/2022 6:05:10 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,928,662,4/25/2022 6:05:14 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,1114,633,4/25/2022 6:05:18 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,1118,632,4/25/2022 6:05:23 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,1077,115,4/25/2022 6:05:28 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,1075,97,4/25/2022 6:05:32 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,907,57,4/25/2022 6:05:37 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,911,59,4/25/2022 6:05:41 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,656,60,4/25/2022 6:05:45 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,667,51,4/25/2022 6:05:51 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,464,48,4/25/2022 6:05:56 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1024,437,45,4/25/2022 6:06:02 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_TOP,B1025,219,54,4/25/2022 6:06:07 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-347505-07_TOP,G1603,346,165,4/25/2022 6:13:28 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,314,162,4/25/2022 6:13:34 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-879003-02_BTM,B1025,422,503,4/25/2022 6:15:58 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_BTM,B1025,414,498,4/25/2022 6:16:09 AM,kimtran,Missing Part,Color [Yellow],A2524140,
8-879003-02_BTM,B1025,241,388,4/25/2022 6:16:23 AM,kimtran,No Solder,Color [LightSkyBlue],A2524140,
8-879003-02_BTM,B1025,243,388,4/25/2022 6:16:31 AM,kimtran,No Solder,Color [LightSkyBlue],A2524140,
8-347505-07_TOP,G1603,287,152,4/25/2022 6:16:41 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,290,141,4/25/2022 6:16:46 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,288,152,4/25/2022 6:19:24 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,287,138,4/25/2022 6:19:31 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,377,139,4/25/2022 6:19:37 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,376,152,4/25/2022 6:19:40 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,313,165,4/25/2022 6:19:52 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1603,342,161,4/25/2022 6:19:58 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_BTM,G1603,53,36,4/25/2022 6:21:06 AM,LiTran,H.S. Error,Color [MediumSpringGreen],A2525325,
8-347505-07_TOP,G1604,314,164,4/25/2022 6:24:10 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1604,322,167,4/25/2022 6:24:18 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1604,346,161,4/25/2022 6:24:26 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1604,288,143,4/25/2022 6:24:41 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1604,287,137,4/25/2022 6:24:51 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1604,375,167,4/25/2022 6:25:00 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1606,311,166,4/25/2022 6:36:01 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1606,346,165,4/25/2022 6:36:04 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1607,376,129,4/25/2022 6:39:37 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1607,347,128,4/25/2022 6:39:42 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1607,315,129,4/25/2022 6:39:48 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1609,380,130,4/25/2022 6:52:01 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1609,345,129,4/25/2022 6:52:06 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1609,311,124,4/25/2022 6:52:12 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-347505-07_TOP,G1610,380,129,4/25/2022 6:58:18 AM,LiTran,Insuff Solder,Color [Orange],A2525325,
8-879003-02_TOP,B1024,121,233,4/25/2022 7:41:51 AM,kimtran,Insuff Solder,Color [Orange],A2524140,
8-879003-02_BTM,B1024,238,390,4/25/2022 7:42:31 AM,kimtran,Missing Part,Color [Yellow],A2524140,
4-959030-07_TOP,G7549,426,392,4/25/2022 8:21:50 AM,thvan,NO DEFECTS,Color [Empty],A2526637,
4-959030-07_TOP,G7550,414,425,4/25/2022 8:25:39 AM,thvan,NO DEFECTS,Color [Empty],A2526637,
4-959030-07_TOP,G7551,434,449,4/25/2022 8:26:30 AM,thvan,NO DEFECTS,Color [Empty],A2526637,
4-959030-07_TOP,G7552,430,446,4/25/2022 8:27:43 AM,thvan,NO DEFECTS,Color [Empty],A2526637,
4-959030-07_TOP,G7553,422,434,4/25/2022 8:28:57 AM,thvan,NO DEFECTS,Color [Empty],A2526637,
4-959030-07_TOP,G7554,214,218,4/25/2022 8:30:18 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,177,214,4/25/2022 8:30:34 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,136,215,4/25/2022 8:30:50 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,105,217,4/25/2022 8:31:05 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,66,221,4/25/2022 8:31:25 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,195,76,4/25/2022 8:32:02 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,192,109,4/25/2022 8:32:18 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,192,151,4/25/2022 8:32:38 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-959030-07_TOP,G7554,195,188,4/25/2022 8:33:16 AM,thvan,Insuff Solder,Color [Orange],A2526637,
4-946020-12_BTM,315395-376,67,268,4/25/2022 9:05:59 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-376,509,237,4/25/2022 9:07:01 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-376,464,313,4/25/2022 9:07:39 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-377,68,289,4/25/2022 9:10:44 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-377,512,236,4/25/2022 9:11:54 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-377,478,319,4/25/2022 9:12:51 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-377,570,447,4/25/2022 9:13:23 AM,thvan,Solder Bridge,Color [Chartreuse],A2526535,
4-946020-12_BTM,315395-377,612,471,4/25/2022 9:14:21 AM,thvan,Solder Bridge,Color [Chartreuse],A2526535,
4-946020-12_BTM,315395-378,64,273,4/25/2022 9:15:58 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-378,516,238,4/25/2022 9:16:58 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-378,470,320,4/25/2022 9:17:58 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-379,69,278,4/25/2022 9:19:59 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-379,498,232,4/25/2022 9:20:56 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-379,467,318,4/25/2022 9:21:58 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-379,597,220,4/25/2022 9:22:24 AM,thvan,No Solder,Color [LightSkyBlue],A2526535,
4-946020-12_BTM,315395-379,601,368,4/25/2022 9:24:57 AM,thvan,No Solder,Color [LightSkyBlue],A2526535,
4-946020-12_BTM,315395-380,70,271,4/25/2022 9:26:06 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-380,516,238,4/25/2022 9:26:20 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-380,470,316,4/25/2022 9:26:38 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-380,580,441,4/25/2022 9:30:08 AM,thvan,Solder Bridge,Color [Chartreuse],A2526535,
4-946020-12_BTM,315395-381,70,277,4/25/2022 9:30:34 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-381,522,239,4/25/2022 9:31:28 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-381,473,317,4/25/2022 9:35:12 AM,thvan,Insuff Solder,Color [Orange],A2526535,
4-946020-12_BTM,315395-381,572,440,4/25/2022 9:35:25 AM,thvan,Solder Bridge,Color [Chartreuse],A2526535,
4-959020-08_TOP,H7497,1183,746,4/25/2022 9:52:28 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7497,1086,747,4/25/2022 9:52:36 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7497,1036,937,4/25/2022 9:53:07 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7497,928,831,4/25/2022 9:57:28 AM,thvan,Over Flow,Color [DarkOrange],A2525420,
4-959020-08_TOP,H7497,185,816,4/25/2022 9:58:34 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7497,123,466,4/25/2022 9:59:02 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7497,133,706,4/25/2022 10:00:37 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7497,121,588,4/25/2022 10:01:30 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7497,599,809,4/25/2022 10:02:08 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7497,1181,729,4/25/2022 10:04:36 AM,thvan,Excess Solder,Color [HotPink],A2525420,
4-959020-08_BTM,H7497,1169,107,4/25/2022 10:05:55 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7497,449,95,4/25/2022 10:06:16 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7497,390,117,4/25/2022 10:07:00 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7498,1185,748,4/25/2022 10:08:51 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7498,1090,748,4/25/2022 10:09:01 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7498,1036,936,4/25/2022 10:09:23 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7498,932,828,4/25/2022 10:11:06 AM,thvan,Insuff Solder,Color [Orange],A2525420,
8-583-05_BTM,9998,424,364,4/25/2022 10:46:34 AM,kimtran,NO DEFECTS,Color [Empty],A2527326,
8-583-05_TOP,9999,325,319,4/25/2022 10:46:42 AM,kimtran,NO DEFECTS,Color [Empty],A2527326,
4-959020-08_TOP,H7498,718,753,4/25/2022 10:51:41 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7498,166,817,4/25/2022 10:53:08 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7498,316,679,4/25/2022 10:53:27 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7498,138,894,4/25/2022 10:54:23 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7498,138,706,4/25/2022 10:55:00 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7498,121,590,4/25/2022 10:55:46 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7498,481,920,4/25/2022 10:56:36 AM,thvan,Excess Solder,Color [HotPink],A2525420,
4-959020-08_TOP,H7498,341,113,4/25/2022 10:57:58 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7498,1144,44,4/25/2022 10:58:34 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_TOP,H7498,1142,129,4/25/2022 10:58:53 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_TOP,H7498,488,42,4/25/2022 10:59:32 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_TOP,H7499,1186,749,4/25/2022 11:01:59 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,1084,751,4/25/2022 11:02:13 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,1038,933,4/25/2022 11:03:46 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,933,828,4/25/2022 11:04:02 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,172,815,4/25/2022 11:04:33 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,713,752,4/25/2022 11:05:07 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,121,464,4/25/2022 11:05:37 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,155,774,4/25/2022 11:06:02 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7499,110,856,4/25/2022 11:07:32 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_BTM,H7499,91,878,4/25/2022 11:07:53 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_BTM,H7499,132,704,4/25/2022 11:08:16 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7499,121,587,4/25/2022 11:08:48 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_BTM,H7499,819,848,4/25/2022 11:09:19 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_BTM,H7499,1014,806,4/25/2022 11:09:39 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_BTM,H7499,1368,505,4/25/2022 11:11:04 AM,thvan,Solder Bridge,Color [Chartreuse],A2525420,
4-959020-08_TOP,H7499,344,114,4/25/2022 11:16:14 AM,thvan,Insuff Solder,Color [Orange],A2525420,
4-959020-08_TOP,H7499,1137,130,4/25/2022 11:16:59 AM,thvan,No Solder,Color [LightSkyBlue],A2525420,
4-959020-08_TOP,H7499,487,44,4/25/2022 11:20:42 AM,thvan,Insuff Solder,Color [Orange],A2525420,
80-114005-04_TOP,D295,637,661,4/25/2022 11:33:09 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,453,518,4/25/2022 11:36:11 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,456,536,4/25/2022 11:44:03 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,86,468,4/25/2022 11:45:14 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,53,550,4/25/2022 11:45:18 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,51,563,4/25/2022 11:45:22 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,447,394,4/25/2022 11:52:02 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,281,369,4/25/2022 11:52:05 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D295,38,119,4/25/2022 11:55:47 AM,LiTran,No Solder,Color [LightSkyBlue],A2524136,
80-114005-04_TOP,D296,448,400,4/25/2022 11:58:33 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D296,454,516,4/25/2022 11:58:38 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D296,407,567,4/25/2022 11:59:06 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D296,379,631,4/25/2022 11:59:12 AM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D296,452,536,4/25/2022 12:03:31 PM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D296,85,470,4/25/2022 12:06:30 PM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D296,358,567,4/25/2022 12:18:52 PM,LiTran,Insuff Solder,Color [Orange],A2524136,
8-848302-02_TOP,1037,461,205,4/25/2022 12:20:00 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1037,594,177,4/25/2022 12:20:30 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1037,333,148,4/25/2022 12:21:18 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1037,217,160,4/25/2022 12:21:47 PM,thvan,Insuff Solder,Color [Orange],A2524152,
80-114005-04_TOP,D296,284,679,4/25/2022 12:22:54 PM,LiTran,Insuff Solder,Color [Orange],A2524136,
8-848302-02_TOP,1037,213,479,4/25/2022 12:24:03 PM,thvan,Insuff Solder,Color [Orange],A2524152,
80-114005-04_TOP,D297,458,517,4/25/2022 12:26:06 PM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D297,455,534,4/25/2022 12:26:10 PM,LiTran,Insuff Solder,Color [Orange],A2524136,
8-848302-02_TOP,1037,154,507,4/25/2022 12:27:12 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_BTM,1037,1167,524,4/25/2022 12:30:32 PM,thvan,Solder Bridge,Color [Chartreuse],A2524152,
8-848302-02_TOP,1038,151,494,4/25/2022 12:35:19 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1038,511,479,4/25/2022 12:36:25 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1038,400,162,4/25/2022 12:37:30 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1038,527,172,4/25/2022 12:38:01 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1038,659,158,4/25/2022 12:39:21 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_TOP,1038,331,133,4/25/2022 12:41:29 PM,thvan,Insuff Solder,Color [Orange],A2524152,
8-848302-02_BTM,1038,101,528,4/25/2022 12:42:37 PM,thvan,No Solder,Color [LightSkyBlue],A2524152,
8-848302-02_BTM,1038,87,495,4/25/2022 12:43:20 PM,thvan,No Solder,Color [LightSkyBlue],A2524152,
8-848302-02_BTM,1038,229,599,4/25/2022 12:44:19 PM,thvan,No Solder,Color [LightSkyBlue],A2524152,
8-848302-02_BTM,1038,227,572,4/25/2022 12:44:59 PM,thvan,No Solder,Color [LightSkyBlue],A2524152,
81-101060-03_BTM,C1205,413,367,4/25/2022 12:54:46 PM,kmarionn,NO DEFECTS,Color [Empty],A2524155,
81-101060-03_TOP,C1206,505,353,4/25/2022 12:56:22 PM,kmarionn,NO DEFECTS,Color [Empty],A2524155,
81-101060-03_BTM,C1205,543,388,4/25/2022 12:56:41 PM,kmarionn,NO DEFECTS,Color [Empty],A2524155,
81-101060-03_TOP,C1207,508,338,4/25/2022 12:56:59 PM,kmarionn,NO DEFECTS,Color [Empty],A2524155,
81-101060-03_BTM,C1208,520,395,4/25/2022 12:57:19 PM,kmarionn,NO DEFECTS,Color [Empty],A2524155,
80-114005-04_TOP,D297,48,438,4/25/2022 12:58:17 PM,LiTran,Solder Bridge,Color [Chartreuse],A2524136,
80-114005-04_TOP,D298,51,565,4/25/2022 1:04:05 PM,LiTran,Insuff Solder,Color [Orange],A2524136,
80-114005-04_TOP,D298,42,428,4/25/2022 1:04:09 PM,LiTran,Solder Bridge,Color [Chartreuse],A2524136,
80-114005-04_TOP,D298,334,396,4/25/2022 1:10:01 PM,LiTran,Insuff Solder,Color [Orange],A2524136,

 

[strongm]

Hmm. I ran your exact code (ok, with very minor modifications to deal with fact that firstly I am in UK and therefore have UK date formats, and secondly today isn't Monday ...)

a) total in the dictionary and the destination text file is 35, not 36
b) msgbox I get reflects that:

so, given you say you are running this same code, I have no idea why you are getting different results.


A couple of asdditional obeservations, if I may.

a) '***Delete File****
You don't need this block of code. I wrote my example to deal with this already:

Set outfile = CreateObject("scripting.filesystemobject").CreateTextFile(strDir & "Formatted-Defect-Data.txt", True)

will delete Formatted-Defect-Data.txt if it already exists

b) You write out the data to Formatted-Defect-Data.txt only to rerad it back in again to do some analysis. You don't need to - all that data already (very deliberately) exists in the dictionary object.

c) But, now I have seen your actual requirement, I think I'd be tempted to read the data into a recordset, and work against that. Just a thought ...

 

[Nu2Java]

thanks for the help strongm.. I will remove the delete file section. How do I use what is in the library from your observation (b) when doing the rest of my search? Now that I see you really had no problems, I need to look and see if there is something in there from previous days that is causing the problem I am having. I am unfamiliar with the recordset, I would love to see an example of that if you have time.
**Edit: I should have waited another minute or two. I just confirmed that there is something in the data somewhere else that must be a duplicate that is messing me up. I stripped out everything but yesterday and I get the correct count like you did. So now I guess I need to extract JUST the date I am looking at? Or is there another way to handle it?

 

[strongm]

Ok, here's a rewrite of sub FPY using recordsets; this is just one way of doing it. You'll need to put in your own path and source filename:

[COLOR=blue]Sub FPY2()
    strSearchFor = "4/25/2022"
    
    Set myRS = CreateObject("ADODB.Recordset")
    Set conn = CreateObject("ADODB.Connection")

    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='D:\Downloads\DeleteMe'; Extended Properties='text;HDR=no;FMT=Delimited'"
    conn.Open

    myRS.Open "Select distinct F2,F6 from source.txt where F6 in ('thvan', 'LiTran', 'kimtran', 'kmarionn') and F5 Like '" & strSearchFor & "%'", conn, adOpenStatic
    DateCount = myRS.RecordCount
    myRS.Close
    
    myRS.Open "Select distinct F2,F6 from source.txt where F6 in ('thvan', 'LiTran', 'kimtran', 'kmarionn') and F5 Like '" & strSearchFor & "%' and F7 = 'NO DEFECTS'", conn, adOpenStatic
    LineCount = myRS.RecordCount
    myRS.Close
    
    FPY = FormatPercent(LineCount / DateCount, 1)
 
 MsgBox "Run Date: " & strSearchFor & vbCrLf & vbCrLf & "Total Boards Inspected:  " & DateCount & vbCrLf & vbCrLf & _
"Total Passed:  " & LineCount & vbCrLf & vbCrLf & "FPY:  " & FPY, , "Flow Line FPY"
End Sub[/color]

 

[Nu2Java]

thanks strongm... not sure what I am doing wrong, but when I run this I get values of (-1) for both Total Inspected & Total Passed. This is how I have my code with the changed path and filenames. Maybe I entered something incorrectly

Sub FPY()

strSearchFor = "4/25/2022"
    
Set myRS = CreateObject("ADODB.Recordset")
Set conn = CreateObject("ADODB.Connection")

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\scripts\adodb'; Extended Properties='text;HDR=no;FMT=Delimited'"
conn.Open

myRS.Open "Select distinct F2,F6 from defects.txt where F6 in ('thvan', 'LiTran', 'kimtran', 'kmarionn') and F5 Like '" & strSearchFor & "%'", conn, adOpenStatic
DateCount = myRS.RecordCount
myRS.Close
    
myRS.Open "Select distinct F2,F6 from defects.txt where F6 in ('thvan', 'LiTran', 'kimtran', 'kmarionn') and F5 Like '" & strSearchFor & "%' and F7 = 'NO DEFECTS'", conn, adOpenStatic
LineCount = myRS.RecordCount
myRS.Close
    
fpyPrcnt = FormatPercent(LineCount / DateCount, 1)
 
MsgBox "Run Date: " & strSearchFor & vbCrLf & vbCrLf & "Total Boards Inspected:  " & DateCount & vbCrLf & vbCrLf & _
"Total Passed:  " & LineCount & vbCrLf & vbCrLf & "FPY:  " & fpyPrcnt, , "Flow Line FPY"
End Sub

 

[strongm]

Mea culpa. I actually built this in VBA and then translated to VBScript - and I made one error: left an ADO library constant in ...

Simple to fix; just add

[tt]adOpenStatic = 3[/tt]

anywhere before the first [tt]MyRS.Open[/tt] line

 

[Nu2Java]

strongm, this works great! thank you so much for your time helping me with this. The recordset is totally new to me, so I need to dig into it and learn more about it. This is so much simpler and cleaner than what I had.