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

06-27-2006

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:

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

beakmyn · 06-27-2006

Quote:

Originally Posted 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:

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.

Scruge · 06-27-2006

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.

06-27-2006

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?

streaker69 · 06-27-2006

Quote:

Originally Posted by beakmyn

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.

beakmyn · 06-27-2006

Quote:

Originally Posted by streaker69

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.

goldfndr · 06-28-2006

Quote:

Originally Posted by mbolgian

Code:

    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?

06-27-2006	#3 (permalink)
Scruge Nyuk nyuk! Join Date: Jan 2005 Location: TX Posts: 1,378	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. __________________ KNSGEM A wifi boundary plotter for Google Earth Click Me

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

06-27-2006	#4 (permalink)
mbolgian Posts: n/a	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?