Script To Parse the OUI.TXT file with Visual Basic (in Microsoft Access)

Take a script, leave a script - Like the penny tray only different

Script To Parse the OUI.TXT file with Visual Basic (in Microsoft Access)

Postby mbolgian » Tue Jun 27, 2006 7:44 am

I threw this together to help with one of my projects. It may help you also. I'm storing my APs in an MS Access database. I needed a table to relate the MAC IDs to the BSSID in the FoundAP table. Creating it the first time is easy enough, but when the table needs to be updated, this Sub can be run and it will do it for you. Be warned, it runs very slow, taking about five minutes to parse all 9,374 records. Strings are slow as hell, at least in VB. Under ASP/VBScript this would run much faster, I believe.

Modify as needed. Enjoy!

Code: Select all
Sub UpdateOUI()
' This will parse the OUI.txt file located at http://standards.ieee.org/regauth/oui/oui.txt
' This version does not grab the file automatically.  You'll have to download it and place it
' somewhere that's accessible by this code.  I have made a separate version that uses Microsoft's
' XML parser to download the file automatically AND parse it (useful for automated scripts run daily).
' This version is for the few people that may not have the XML parser installed on their system.

' Create a FSO.
Set fso = CreateObject("Scripting.FileSystemObject")
' Open the file.  Change the path to where you have your file saved.
Set ts = fso.OpenTextFile("C:\OUI\OUI.txt")

' The first six lines contain nothing useful to us, so we skip them.
For i = 1 To 6
ts.SkipLine
Next

' Any line that contains (hex) in it has the info we need.
Do While Not ts.AtEndOfStream

tvar = ts.ReadLine
If Mid(tvar, 12, 5) = "(hex)" Then
    Dim MMACID As String
    Dim Company As String
    MMACID = Replace(Left(tvar, 8), "-", "")
    Company = Replace(Mid(tvar, 19), "'", "''")
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MACID FROM OUI WHERE MACID = '" & MMACID & "'")
    If rs.EOF Then
        CurrentDb.Execute "INSERT INTO OUI (MACID, Company) VALUES ('" & MMACID & "', '" & Company & "')"
    End If
End If
Loop

'Tidy up after ourselves.
ts.Close
Set ts = Nothing
rs.Close
Set rs = Nothing
End Sub
mbolgian
 

Postby beakmyn » Tue Jun 27, 2006 8:20 am

mbolgian wrote:I threw this together to help with one of my projects. It may help you also. I'm storing my APs in an MS Access database. I needed a table to relate the MAC IDs to the BSSID in the FoundAP table. Creating it the first time is easy enough, but when the table needs to be updated, this Sub can be run and it will do it for you. Be warned, it runs very slow, taking about five minutes to parse all 9,374 records. Strings are slow as hell, at least in VB. Under ASP/VBScript this would run much faster, I believe.

Modify as needed. Enjoy!

Code: Select all
Sub UpdateOUI()
' This will parse the OUI.txt file located at http://standards.ieee.org/regauth/oui/oui.txt
' This version does not grab the file automatically.  You'll have to download it and place it
' somewhere that's accessible by this code.  I have made a separate version that uses Microsoft's
' XML parser to download the file automatically AND parse it (useful for automated scripts run daily).
' This version is for the few people that may not have the XML parser installed on their system.

' Create a FSO.
Set fso = CreateObject("Scripting.FileSystemObject")
' Open the file.  Change the path to where you have your file saved.
Set ts = fso.OpenTextFile("C:\OUI\OUI.txt")

' The first six lines contain nothing useful to us, so we skip them.
For i = 1 To 6
ts.SkipLine
Next

' Any line that contains (hex) in it has the info we need.
Do While Not ts.AtEndOfStream

tvar = ts.ReadLine
If Mid(tvar, 12, 5) = "(hex)" Then
    Dim MMACID As String
    Dim Company As String
    MMACID = Replace(Left(tvar, 8), "-", "")
    Company = Replace(Mid(tvar, 19), "'", "''")
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MACID FROM OUI WHERE MACID = '" & MMACID & "'")
    If rs.EOF Then
        CurrentDb.Execute "INSERT INTO OUI (MACID, Company) VALUES ('" & MMACID & "', '" & Company & "')"
    End If
End If
Loop

'Tidy up after ourselves.
ts.Close
Set ts = Nothing
rs.Close
Set rs = Nothing
End Sub



move your DIM outside of the loop so you declare them once and not everytime you go through the loop.
The real memory hog and slowness is creating a hook into your database every time you want to add a record.

Instead of using opensrecordset and insert consider adding a WHERE clause to your insert statment.
beakmyn
 
Posts: 4858
Joined: Sun Aug 03, 2003 1:53 pm

Postby Scruge » Tue Jun 27, 2006 8:42 am

5 minutes is a long time..

You should be able to get your time down to less than 1 second using low level read and write. I convert the OUI data into .dbf and tablized .txt in about 1 sec.
User avatar
Scruge
 
Posts: 1399
Joined: Wed Jan 26, 2005 3:29 pm

Postby mbolgian » Tue Jun 27, 2006 8:50 am

Thanks for the tips. :) I moved the Dim statements outside of the loop (not sure why I put them inside, other than I threw this together in 5 min) but it still runs dog slow due to the recordset lookup. I wasn't sure what you meant by using a WHERE clause in the INSERT statement. How would I do that?
mbolgian
 

Postby streaker69 » Tue Jun 27, 2006 9:03 am

beakmyn wrote:move your DIM outside of the loop so you declare them once and not everytime you go through the loop.
The real memory hog and slowness is creating a hook into your database every time you want to add a record.

Instead of using opensrecordset and insert consider adding a WHERE clause to your insert statment.


I may be wrong, but how does one do a WHERE on a Record that doesn't exist?

If you're inserting it, then you're creating the record, you can do a WHERE in the UPDATE, but I've never see one on a INSERT.

I could be wrong, never claimed to know everything about SQL.
Treat your gun like your genitals, only whip it out when it's absolutely necessary.
User avatar
streaker69
 
Posts: 11867
Joined: Thu Jul 08, 2004 10:09 am
Location: Virginville, BlueBall, Bird In Hand, Intercourse, Paradise, PA

Postby beakmyn » Tue Jun 27, 2006 10:00 am

streaker69 wrote:I may be wrong, but how does one do a WHERE on a Record that doesn't exist?

If you're inserting it, then you're creating the record, you can do a WHERE in the UPDATE, but I've never see one on a INSERT.

I could be wrong, never claimed to know everything about SQL.


I was thinking something else, basically a insert/select all in one using not Exist


Normally you'd open a connection to the table then use a select statement to return a true/false then perform an update/insert based on the conditions. There are more elequeont ways of doing this rather then they method you're using.
beakmyn
 
Posts: 4858
Joined: Sun Aug 03, 2003 1:53 pm

Postby goldfndr » Wed Jun 28, 2006 3:40 am

mbolgian wrote:
Code: Select all
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT MACID FROM OUI WHERE MACID = '" & MMACID & "'")
    If rs.EOF Then
        CurrentDb.Execute "INSERT INTO OUI (MACID, Company) VALUES ('" & MMACID & "', '" & Company & "')"
    End If


My thought is, rather than a huge number of OpenRecordsets (with all of their associated overhead), simply use Scripting.Dictionary[1] to keep track of Unique entries[2], then insert the whole lot of Dictionary entries to CurrentDb without error checking. That'll allow you to just use CurrentDb.Execute statements, no need for CurrentDb.OpenRecordset evaluations.

If you're unfamiliar with Scripting.Dictionary, look at the Master Script's ns04speech.vbs: adding at OnScanResultSpeech, regurgitating at OnScanCompleteSpeech. ns04database.vbs also has some potentially useful code. Obviously MSDN et al have more info.

[1]Assuming the language you're using exposes Scripting.Dictionary. You're using Dim with types, so my early assumption of vbscript was incorrect.
[2]Is an OUI listing with duplicates valid?
goldfndr
 
Posts: 181
Joined: Sat Aug 28, 2004 3:30 am
Location: Bellevue, WA, USA


Return to Scripts

Who is online

Users browsing this forum: No registered users and 2 guests