VBA: CustomerID for IP-Addresses in external logfile.txt

[JVLennox]

Hi everybody,

I have a logfile.txt which looks like this:
62.68.174.12 - - [01/Jan/2005:00:07:58 +0100] "GET /pubid-848078832/index/index_pdf.html HTTP/1.0" 200 2862 "-" "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)" 0
62.68.174.12 - - [01/Jan/2005:00:08:01 +0100] "GET /pubid-848078832/show_pdf.html?/pdf/pubid-848078832_1043.pdf?page=9 HTTP/1.0" 200 130220 "-" "Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)" 2
...

Now I want to insert Customer-IDs to each IP-Address in that logfile. The CustomerID could be seperated from the IP-Address by a ";" or as you like.

I have an Excel-table which provides the CustomerIDs for the IP-Addresses, BUT those IP-Addresses are ranges...

That table looks like this (I have it as an external TXT also):
IP-Address(Range) -- CustomerID
203.75.24.6 -- 290
203.75.24.5 -- 290
203.75.24.250 -- 295
203.75.23.20 -- 295
199.212.31.* -- 293
198.96.61.* -- 293
192.82.131.* -- 293
192.82.130.* -- 296
192.82.129.* -- 296
192.82.128.* -- 296
192.75.254.* -- 296
192.75.177.* -- 296
192.139.207.* -- 296
192.139.206.* -- 298
192.12.183.* -- 298
142.151.*.* -- 298


I found this so far, but I couldn't apply it:
http://forums.microsoft.com/MSDN/Sho...22180&SiteID=1

The logfile has about 100,000 lines, so I cannot import it into Excel anymore which I was able to do until recently. Now I heard it was possible to alter an external file - but I don't know how...

Is there a way, that a MAKRO will check into which range of IP-addresses the IP-address of each entry of the logfile falls, and which will then produce the according CustomerID and put it into the logfile???

Thanks so MUCH!!!

[westconn1]

Private Sub Command1_Click()
Dim i As Integer, pos As Integer, mytest As String, pos1 As Integer
Dim f1 As Integer, f2 As Integer, f3 As Integer
Dim li As Integer, strfi As String, arrfi() As String, pard As Boolean
Dim tlen As Integer, myind As String
Dim clist As String, logln As String, iparr() As String
 
 
f1 = FreeFile
Open "logfile.txt" For Input As f1
 
f2 = FreeFile
Open "custlist.txt" For Input As f2
f3 = FreeFile
Open "newlist.txt" For Output As f3
 
clist = Input(LOF(f2), #f2)
 
Do While Not EOF(f1)
    Line Input #f1, logln
    logln = Left(logln, InStr(logln, "-") - 1)
    iparr = Split(logln, ".")
    pos = InStr(clist, iparr(0) & ".*.*.*")
    If pos = 0 Then pos = InStr(clist, iparr(0) & "." & iparr(1) & ".*.*")
    If pos = 0 Then pos = InStr(clist, iparr(0) & "." & iparr(1) & "." & iparr(2) & ".*")
    If pos = 0 Then pos = InStr(clist, logln)
    If pos = 0 Then GoTo noip 'this ip not found
    
    pos = InStr(pos + 1, clist, "-- ")
    pos1 = InStr(pos + 1, clist, vbNewLine)
    If pos1 = 0 Then pos1 = LOF(f2) + 1
    Print #f3, logln & Mid(clist, pos, pos1 - pos)
 
 
noip:
Loop
 
 
Close
Shell "notepad.exe newlist.txt", vbNormalFocus
Stop
Exit Sub

try this, i just pasted the lines you posted into files, might take a while to do 100,000 lines though

pete

[JVLennox]

GREAT THANKS SO MUCH!!!
It works great!!! Especially with the ranges.

One slight thing: so far the output newlist.txt contains only IP-Adress and corresponding CustomerID. But it should also have the rest of the original logfile.
How do I have to alter this code line:

Print #f3, logln & Mid(clist, pos, pos1 - pos)

Do you know whether there are limitation, lets say the logfile might eventually have 200,000 lines or 500,000 lines. Do you think it will still work?

THANKS AGAIN!!!!

[westconn1]

where do you want the customers id??

after the ip, or at the end

Do While Not EOF(f1)
    Line Input #f1, logln
 
    restofline = right(logln, len(logln) - instr(logln, " -") - 1 ) 'add here
    logln = Left(logln, InStr(logln, " -") - 1) 'edit
 
 
    iparr = Split(logln, ".")
    pos = InStr(clist, iparr(0) & ".*.*.*")
    If pos = 0 Then pos = InStr(clist, iparr(0) & "." & iparr(1) & ".*.*")
    If pos = 0 Then pos = InStr(clist, iparr(0) & "." & iparr(1) & "." & iparr(2) & ".*")
    If pos = 0 Then pos = InStr(clist, logln)
    If pos = 0 Then GoTo noip 'this ip not found
        pos = InStr(pos + 1, clist, "-- ")
    pos1 = InStr(pos + 1, clist, vbNewLine)
    If pos1 = 0 Then pos1 = LOF(f2) + 1
 
 
    Print #f3, logln & Mid(clist, pos, pos1 - pos) & restofline 'edit
 
 
noip:
Loop

this will put the rest of the line from the log after the customer id
i haven't tested the changes

i don't think it will matte how big you logfile gets, but it might need to be changed if your customer list (file) gets too big, as i keep the whole of that file in memory, you didn't indicate how big it might be.
pete

[JVLennox]

Hey, this is great!!! THANKS!!!
It is really quick...!

The customerID file might get up to a thousand entries, but not much more than that, so I guess it should be fine...

THANKS SO MUCH again!!

JVLennox

[JVLennox]

Hi, suddenly I have another problem:

I just put the code on a different computer and now I get this error message:
Run-time error '62': Input past end of file
...after this line:

clist = Input(LOF(f2), #f2)

No idea why! I put the same files into the same location on the harddrive, etc...

Can you help me out with that? Yesterday it ran on my computer...
Maybe it is because on this computer I use the full CustID-file which has around 1000 entries and the memory can only handle 256 items??? ???

THANKS again

[westconn1]

how big is the cust-id file?? Mb

how much memory both computers?

try copy it onto your computer to see what happens

try to run it on that computer with the cust id file you used

pete

[westconn1]

i tested the code with a customer file i generated with 100,000 customers, it was able to load the file with no problem on my laptop.

if this is now working, please mark the thread resolved

pete

[JVLennox]

Hi Pete!

I tried it again and again, and even got some more memory....
DIDn't help.

I always get the error "Overflow" - so I thought it would be the memory.
The customer file is not really big, has a couple thousand lines.
(I ATTACHED it.)

The code looks like that:

Private Sub Command1_Click()
Dim i As Integer, pos As Integer, mytest As String, pos1 As Integer
Dim f1 As Integer, f2 As Integer, f3 As Integer
Dim li As Integer, strfi As String, arrfi() As String, pard As Boolean
Dim tlen As Integer, myind As String
Dim clist As String, logln As String, iparr() As String
 
 
f1 = FreeFile
Open "C:\logfile.txt" For Input As f1
 
f2 = FreeFile
Open "C:\customers.txt" For Input As f2
f3 = FreeFile
Open "C:\newlist.txt" For Output As f3
 
clist = Input(LOF(f2), #f2)
 
Do While Not EOF(f1)
    Line Input #f1, logln
 
    restofline = right(logln, len(logln) - instr(logln, " ") - 1 ) 'add here
    logln = Left(logln, InStr(logln, " -") - 1) 'edit
 
 
    iparr = Split(logln, ".")
    pos = InStr(clist, iparr(0) & ".*.*.*")
    If pos = 0 Then pos = InStr(clist, iparr(0) & "." & iparr(1) & ".*.*")
    If pos = 0 Then pos = InStr(clist, iparr(0) & "." & iparr(1) & "." & iparr(2) & ".*")
    If pos = 0 Then pos = InStr(clist, logln)
    If pos = 0 Then GoTo noip 'this ip not found
        pos = InStr(pos + 1, clist, "-- ")
    pos1 = InStr(pos + 1, clist, vbNewLine)
    If pos1 = 0 Then pos1 = LOF(f2) + 1
 
 
    Print #f3, logln & Mid(clist, pos, pos1 - pos) & restofline 'edit
 
 
noip:
Loop
 
Close
Shell "notepad.exe newlist.txt", vbNormalFocus
Stop
Exit Sub

Why is it running at your computer, but not with mine? Or is there a slight typing error in the code???

THANKS so much!

[westconn1]

i doubt if memory was the problem, i had previously tested with customer file with 100,000 entries

i am not totally certain on this, but the error you are getting is to do with something in the txt file, probably an end of file marker. try a different text file.

your file worked fine on my machine, but it is a copy that has been downloaded, so it has been changed

i had a text file here that was giving the same error, i opened it in notepad, selected all, copied it to a new empty notepad and saved it, the new file then worked ok, but it may depend how the customers.txt file is created as to whether this will help you.
the alternative is to use the line input method to read all lines into a single variable, which is really no harder.
replace clist = Input(LOF(f2), #f2) with

Do While Not EOF(f2)
    Line Input #f2, mytest
    clist = clist & mytest
    Loop

theres a couple of options, something should work

pete

[JVLennox]

Thanks So Much For Your Help!!!!

Really!!!!