Import Bookmark.htm into Access 2

[PennWhite]

Does anyone know of any code samples that show how to import a Bookmark.htm file (from MSIE or Netscape) into an Access table? Since the Bookmark.htm file is not a table, the built-in Access TransferText - Transfer Type - Import HTML will not work.

Thank you very much,

Penn

 

[VBslammer]

I made a procedure to import the bookmark.htm file exported from Internet Explorer and load it into a table. I haven't looked at the Netscape format so I don't know if it will work with that, but it should be similar if not identical.

Basically, the file uses [tt]<DL>[/tt] tags (Definition List) and [tt]<DT>[/tt] tags (Definition Term) to organize the favorites into a hierarchical list, so the favorites can be organized into nested folders like:
[tt]
Folder1
SubFolder1
Favorite1
Favorite2
SubFolder2
Favorite1
Favorite2
Favorite1
Favorite2

Folder2
SubFolder1
SubSubFolder1
Favorite1
Favorite2
etc.
etc.
[/tt]
I tracked the folder nesting levels using a Scripting.Dictionary object so I could add/remove levels in the loop. Then I can add the path in the format [tt]"..\Folder1\SubFolder1\SubSubFolder1"[/tt] to a field in the table, with [tt]"..\"[/tt] representing the root path. I parsed the file using Regular Expressions which is simple to code and runs very fast. To store the information, I made a table like this:
[tt]
PK FavoriteID - Autonumber
Path - Text 255
Link - Memo (could be more than 255 characters?)
Title - Text 255
Visited - Text 12
Modified - Text 12
Added - Text 12
[/tt]
XML would probably be a better format to store the tree of information in, and it wouldn't take much to modify this example to do it that way. Anyway, here is the procedure:

[green]'********************
'*  §lamKeys §oftware 2005® (VBSlammer)
'*
'*  @CREATED  :   1/29/2005 1:21:55 PM
'*  @PARAMS   :   strFile - path and filename of "bookmark.htm"
'*  @RETURNS  :   N/A
'*  @NOTES    :   Assumes table "InternetFavorites" exists
'*  @CALL     :   ImportBookmarkFile("C:\..\..\bookmark.htm")
'*  @MODIFIED :
'********************[/green]
Sub ImportBookmarkFile(ByVal strFile As String)
On Error GoTo ErrHandler
  Dim rst As Recordset
  Dim fso As FileSystemObject
  Dim tsMyFile As TextStream
  Dim re As RegExp
  Dim strIn As String
  Dim intDepth As Integer
  Dim dicFolders As New Scripting.Dictionary
  Dim strFolder As String
  Dim i As Integer

  Set fso = New FileSystemObject
  Set tsMyFile = fso.OpenTextFile(strFile, ForReading)
  Set re = New RegExp
  
  Set rst = CurrentDb.OpenRecordset("InternetFavorites")
  
  With re
    
    [green]'set attributes[/green]
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
  
    Do Until tsMyFile.AtEndOfStream
      
      [green]'read a line[/green]
      strIn = tsMyFile.ReadLine
      
      [green]'check for nesting up (new folder)[/green]
      .Pattern = "<DT><H3 FOLDED ADD_DATE=""(.*)"">(.*)</H3>"
      If .Test(strIn) = True Then
        [green]'new list, bump up level[/green]
        intDepth = intDepth + 1
        dicFolders.Add intDepth, Trim(.Replace(strIn, "$2"))
        GoTo NextLine
      End If
      
      [green]'check for nesting down.[/green]
      .Pattern = "</DL><P>"
      If .Test(strIn) = True Then
        [green]'end of current list, bump down level[/green]
        If intDepth > 0 Then
          dicFolders.Remove intDepth
          intDepth = intDepth - 1
        End If
        GoTo NextLine
      End If
      
      [green]'build a folder path string[/green]
      strFolder = "..\"
      For i = 1 To intDepth
        strFolder = strFolder & dicFolders(i)
        If i < intDepth Then
          strFolder = strFolder & "\"
        End If
      Next i

      [green]'write the favorite info to a table[/green]
      .Pattern = "<DT><A HREF=""(.*)"" ADD_DATE=""(.*)"" LAST_VISIT=""(.*)"" LAST_MODIFIED=""(.*)"">(.*)</A>"
      If .Test(strIn) = True Then
        rst.AddNew
        rst.Fields("Folder") = strFolder
        rst.Fields("Link") = Trim(.Replace(strIn, "$1"))
        rst.Fields("Added") = Trim(.Replace(strIn, "$2"))
        rst.Fields("Visited") = Trim(.Replace(strIn, "$3"))
        rst.Fields("Modified") = Trim(.Replace(strIn, "$4"))
        rst.Fields("Title") = Trim(.Replace(strIn, "$5"))
        rst.Update
      End If
NextLine:
    Loop
  End With
  
ExitHere:
  On Error Resume Next
  tsMyFile.Close
  Set tsMyFile = Nothing
  Set fso = Nothing
  Set re = Nothing
  rst.Close
  Set rst = Nothing
  Exit Sub
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PennWhite]

Thank you very much. This looks like it will work beautifully. I have read that an XML approach might be better but I don't have the experience to do that yet. Actually, I learned a great deal about using RegExp just by studying your code. I have used them in PERL but hadn't seen how to use them in Access before.

Thanks again,

Penn

 

[PennWhite]

VBSlammer:

I tried out your code and got a lot of compile errors (couldn't find various object types - RegExp, FileSystemObject, TextStream, etc.) My guess is that I need a Reference but I don't know which one to use. I tried including Microsoft Scripting Runtime but it didn't solve the problem.

Thanks,

Penn

 

[PHV]

You need Microsoft VBScript Regular Expression too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PennWhite]

Got it. Thanks.

Penn

 

[VBslammer]

One final note, the bookmark file uses a timestamp that is converted to seconds elapsed since 1/1/1970. To make a timestamp, you can use:

? CLng(DateDiff("s","01/01/1970 00:00:00", Now()))
 1107214236

To restore the seconds to a readable date, reverse the process:

? DateAdd("s", "1107214236", "01/01/1970 00:00:00")
1/31/2005 11:30:36 PM

To convert the timestamps while importing the file, update the code as follows:

  [green]'dimension a constant for the base date/time[/green]
  Const BASE_DATE = "01/01/1970 00:00:00"

      [green]'convert timestamps before saving to file[/green]
      If .Test(strIn) = True Then
        rst.AddNew
        rst.Fields("Folder") = strFolder
        rst.Fields("Link") = Trim(.Replace(strIn, "$1"))
        rst.Fields("Added") = [blue]DateAdd("s", Trim(.Replace(strIn, "$2")), BASE_DATE)[/blue]
        rst.Fields("Visited") = [blue]DateAdd("s", Trim(.Replace(strIn, "$3")), BASE_DATE)[/blue]
        rst.Fields("Modified") = [blue]DateAdd("s", Trim(.Replace(strIn, "$4")), BASE_DATE)[/blue]
        rst.Fields("Title") = Trim(.Replace(strIn, "$5"))
        rst.Update
      End If

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PennWhite]

In the spirit of 'giving back', I have written a little database application using the above techniques. It allows you to locate the Bookmark.htm file on your machine and bring it into the database, creates 'Categories' out of the Internet Favorites Folders and SubFolders and then displays the Favorites in a form. The display can be sorted by the categories. It also includes the elimination of duplicate entries and a couple of other enhancements.

The app (ImportFavs.mdb in Acc 2000) is free and any part of it may be used by all who request it. Just contact me at pennwhite_nospam@hotmail.com (remove _nospam from the address).

Penn

 

[PennWhite]

Just discovered you can do this...

If you'd like to download the database (ACC00 ImportFavs.MDB), click here:

Download

 

[PennWhite]

Oops. Sorry.

Just discovered you can do this...

If you'd like to download the database (ACC00 ImportFavs.MDB), click here:

Download