Page 1 of 1

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

PostPosted: Tue Jun 27, 2006 7:44 am
by mbolgian
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

PostPosted: Tue Jun 27, 2006 8:20 am
by beakmyn
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.

PostPosted: Tue Jun 27, 2006 8:42 am
by Scruge
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.

PostPosted: Tue Jun 27, 2006 8:50 am
by mbolgian
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?

PostPosted: Tue Jun 27, 2006 9:03 am
by streaker69
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.

PostPosted: Tue Jun 27, 2006 10:00 am
by beakmyn
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.

PostPosted: Wed Jun 28, 2006 3:40 am
by goldfndr
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?