Script To Parse the OUI.TXT file with Visual Basic (in Microsoft Access)
Posted: 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!
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