collect number of times unique lines occur in a text file

For the code that I accepted from before in VB6:

Private Sub Command1_Click()
 Dim f As Integer
    Dim g As Integer
    Dim strLine As String
    
    f = FreeFile
    Open "C:\Users\Alpesh\Desktop\111114.txt" For Input As #f
        g = FreeFile
        Open "c:\Users\Alpesh\Desktop\052214.txt" For Append As #g
            Do Until EOF(f)
                Line Input #f, strLine
                If InStr(strLine, "BlockedIP") > 0 Then
                    Print #g, strLine
                End If
            Loop
        Close #g
    Close #f
End Sub

Open in new window



Is it possible to count how many unique lines are collected for output?  For example, instead of pasting all 502 "BlockedIP" lines, I would rather see a count of them as seen below:  502 times for the first one and 4 times for the second, etc...


502- 00:01:05 192.168.1.100 [DNSRedir] BlockedIP response sent, keyword blogspot.com: adsense.blogspot.com. -> 192.168.1.100
4 -00:01:33 192.168.1.102 [DNSRedir] BlockedIP response sent, keyword adnxs.com: ib.adnxs.com. -> 192.168.1.100
052214.txt
al4629740Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Using the file you attached and the code below I get the following which is not the counts that you said there should be. The code assumes that a record is unique based on the text after "keyword". Is that not correct?

7- adnxs.com: ib.adnxs.com. -> 192.168.1.100
1207- blogspot.com: adsense.blogspot.com. -> 192.168.1.100

Private Sub Command1_Click()
Dim f As Integer
Dim strLine As String
Dim lngLines As Long
Dim arrKeys() As String
Dim bFound As Boolean
Dim bFirst As Boolean
Dim intCount As Integer
Dim strParts() As String

bFirst = True
f = FreeFile

Open "C:\temp\052214.txt" For Input As #f
ReDim arrKeys(1, 0)
Do Until EOF(f)
    Line Input #f, strLine
    bFound = False
    If InStr(strLine, "BlockedIP") > 0 Then
        strParts = Split(strLine, "keyword")
        For lngLines = 0 To intCount - 1
            If arrKeys(1, lngLines) = strParts(1) Then
                arrKeys(0, intCount - 1) = arrKeys(0, intCount - 1) + 1
                bFound = True
                Exit For
            End If
        Next
        If Not bFound Then
            If Not bFirst Then
                ReDim Preserve arrKeys(1, intCount)
            End If
            arrKeys(1, intCount) = strParts(1)
            arrKeys(0, intCount) = 1
            bFirst = False
            intCount = intCount + 1
        End If
    End If
Loop
Close
For lngLines = 0 To UBound(arrKeys)
    Debug.Print arrKeys(0, lngLines) & "-" & arrKeys(1, lngLines)
Next
MsgBox "done"
End Sub

Open in new window

0
al4629740Author Commented:
It looks like I have the wrong numbers. Let me test it and get back to you
0
Martin LissOlder than dirtCommented:
Any results from your testing? Did you see the message I sent you?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

al4629740Author Commented:
Martin,

Where is the output file?  It executes but I can't see where the results are.
0
Martin LissOlder than dirtCommented:
They are in the Debug window which you can access if  you Goto the VBE and type Ctrl+g
0
al4629740Author Commented:
Not every blocked site shows up
0
Martin LissOlder than dirtCommented:
There are 1992 lines in the file you posted that contain "BlockedIP". My results in the Immediate Window (which you may have to scroll in order to see it all) shows this:
7- adnxs.com: ib.adnxs.com. -> 192.168.1.100
1207- blogspot.com: adsense.blogspot.com. -> 192.168.1.100
20- visualwebsiteoptimizer.com: dev.visualwebsiteoptimizer.com. -> 192.168.1.100
41- ^.*s(3|e)x: expertsexchange.112.2o7.net. -> 192.168.1.100
389- ^.*\.(asp|aspx|htm|html|jsp|php|xml)-: www.xml-sitemaps.com. -> 192.168.1.100
1- adsrvr.org: match.adsrvr.org. -> 192.168.1.100
1- tube: rtd.tubemogul.com. -> 192.168.1.100
1- criteo.com: dis.criteo.com. -> 192.168.1.100
1- w55c.net: geo-lb02.w55c.net. -> 192.168.1.100
1- tube: rtb.tubemogul.com. -> 192.168.1.100
1- w55c.net: i.w55c.net. -> 192.168.1.100
7- xnxx: www.xnxx.com. -> 192.168.1.100
1- criteo.com: rtax.criteo.com. -> 192.168.1.100
4- twitt: twitter.github.io. -> 192.168.1.100
1- dailymotion.com: www.dailymotion.com. -> 192.168.1.100
1- ^(.*\.)?xvideos\.(com|net)$: www.xvideos.com. -> 192.168.1.100
1- pinterest.com: assets.pinterest.com. -> 192.168.1.100
2- taboo: cdn.taboola.com. -> 192.168.1.100
79- disqus.com: collegetimescom.disqus.com. -> 192.168.1.100
3- pinterest.com: www.pinterest.com. -> 192.168.1.100
1- tumblr.com: officegirls.tumblr.com. -> 192.168.1.100
1- tumblr.com: greekpowerlady.tumblr.com. -> 192.168.1.100
1- tumblr.com: www.tumblr.com. -> 192.168.1.100
3- tumblr.com: sandybrown121.tumblr.com. -> 192.168.1.100
3- eroti: eleganteroticdresses.tumblr.com. -> 192.168.1.100
1- tumblr.com: assets.tumblr.com. -> 192.168.1.100
1- tumblr.com: static.tumblr.com. -> 192.168.1.100
1- tumblr.com: 38.media.tumblr.com. -> 192.168.1.100
1- tumblr.com: 33.media.tumblr.com. -> 192.168.1.100
1- tumblr.com: 40.media.tumblr.com. -> 192.168.1.100
1- tumblr.com: 41.media.tumblr.com. -> 192.168.1.100
1- tumblr.com: 36.media.tumblr.com. -> 192.168.1.100
4- tumblr.com: secure.assets.tumblr.com. -> 192.168.1.100
3- lingerie: www.lingeriediva.com. -> 192.168.1.100
2- tumblr.com: platform.tumblr.com. -> 192.168.1.100
1- pinterest.com: passets-lt.pinterest.com. -> 192.168.1.100
3- lingerie: www.spicylingerie.com. -> 192.168.1.100
2- mature: elegantmatures.tumblr.com. -> 192.168.1.100
2- tumblr.com: classicwomen.tumblr.com. -> 192.168.1.100
2- tumblr.com: strictbeauties.tumblr.com. -> 192.168.1.100
1- adcash.com: www.adcash.com. -> 192.168.1.100
3- tumblr.com: api.tumblr.com. -> 192.168.1.100
1- mgid.com: jsc.mgid.com. -> 192.168.1.100
1- directrev.com: xch.directrev.com. -> 192.168.1.100
1- pinterest.com: widgets.pinterest.com. -> 192.168.1.100
2- addthisedge.com: m.addthisedge.com. -> 192.168.1.100
7- tumblr.com: 31.media.tumblr.com. -> 192.168.1.100
9- adsrvr.org: rtb.adsrvr.org. -> 192.168.1.100
7- blogspot.com: 1.bp.blogspot.com. -> 192.168.1.100
1- tumblr.com: heavenlycheesecake.tumblr.com. -> 192.168.1.100
155- tumblr.com: elegantsexy.tumblr.com. -> 192.168.1.100
and the sum of the counts displayed for each address is 1992.
0
al4629740Author Commented:
I ran the code twice in the same immediate window.  In the immediate window I got this:

7- adnxs.com: ib.adnxs.com. -> 192.168.1.100
1207- blogspot.com: adsense.blogspot.com. -> 192.168.1.100
7- adnxs.com: ib.adnxs.com. -> 192.168.1.100
1207- blogspot.com: adsense.blogspot.com. -> 192.168.1.100

This is the code:

Dim f As Integer
Dim strLine As String
Dim lngLines As Long
Dim arrKeys() As String
Dim bFound As Boolean
Dim bFirst As Boolean
Dim intCount As Integer
Dim strParts() As String

bFirst = True
f = FreeFile

Open "C:\Users\Me\Desktop\111114.txt" For Input As #f
ReDim arrKeys(1, 0)
Do Until EOF(f)
    Line Input #f, strLine
    bFound = False
    If InStr(strLine, "BlockedIP") > 0 Then
        strParts = Split(strLine, "keyword")
        For lngLines = 0 To intCount - 1
            If arrKeys(1, lngLines) = strParts(1) Then
                arrKeys(0, intCount - 1) = arrKeys(0, intCount - 1) + 1
                bFound = True
                Exit For
            End If
        Next
        If Not bFound Then
            If Not bFirst Then
                ReDim Preserve arrKeys(1, intCount)
            End If
            arrKeys(1, intCount) = strParts(1)
            arrKeys(0, intCount) = 1
            bFirst = False
            intCount = intCount + 1
        End If
    End If
Loop
Close
For lngLines = 0 To UBound(arrKeys)
    Debug.Print arrKeys(0, lngLines) & "-" & arrKeys(1, lngLines)
Next
MsgBox "done"

Open in new window

0
al4629740Author Commented:
The immediate window output is also short.  No where to scroll to
0
Martin LissOlder than dirtCommented:
The only thing that I can imagine is that you aren't using the file that you posted in your original question. Just in case something has happened to the original on your PC why don't you download it from your post. I'm attaching my whole project. Change the Open "C:\temp\052214.txt" For Input As #f statement in Command1_Click to match your file path, run it and tell me what happens. I'm mystified because as I've shown I get a different output then you.
Project1.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
I must have copied your code incorrectly.  

Thank you Martin
0
Martin LissOlder than dirtCommented:
If you need any tweaks let me know. In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.