Import Lotus Notes Database into Access/SQL - What about Documents?

Question

I have a few Lotus Notes 'databases' that i'd like to import into Access or SQL.

I think I have most of the steps down (install NotesSQL ODBC driver, setup ODBC connection to Lotus DB, import data into Access), but I can't figure out what to do with all the documents, eg: Word Files, PDF Docs, Excel Workbooks that were in the Lotus DB.

The Lotus Notes DB is full of them. After importing, I do notice a table in Access called 'Documents,' but I don't know what to do with it. I see a line/record for each document in the Lotus DB, but it's not like SQL where there is a column for the actual file data.

Please let me know how I can actually use the documents I pull out of the Lotus DB.

What is the schema of the documents table? Perhaps the document is stored in an OLE Object field (column)? If so, you can write the object to disk, which is probably the best place to store documents. — Fionnuala
The documents table contains the following fields: Abstract (varvhar), Category (varchar), Document (varchar), and _60 (DateTime). I don't see anything that looks like the data...in fact after importing the database into Access the total size of the Access file is only 1.63MB, so it looks like the .DOC,.PDF, and .XLS files didn't make the trip :( — Albert

Ken Pespisa Ken Pespisa · Accepted Answer · 2010-09-21T15:26:10

Your best bet is to extract the documents from the database and store them on a file share. That will give you the most flexibility. To retain the association with the original Notes documents, you may want to export them with filenames or into folders with folder names that include the ID of the associated record in Access. Or at least make sure the records include the path of the document.

I don't believe you can pull in attachments via the NotesSQL driver.

Here's an example script that you can put into an Agent to extract attachments from your database: (from http://www.notes411.com/dominosource/tips.nsf/0/4F1FF33C52F08D76802570C2003A2FD6!opendocument)

Sub Initialize 
      Dim session As New NotesSession 
      Dim db As NotesDatabase 
      Dim collection As NotesDocumentCollection 
      Dim doc As NotesDocument 
      Set db = session.CurrentDatabase 
      Set collection = db.UnprocessedDocuments 
      Set doc = collection.GetFirstDocument() 
      While Not(doc Is Nothing) 
            Call extractMyAttachment( doc ) 
            Set doc = collection.GetNextDocument(doc) 
      Wend 
End Sub 

Function extractMyAttachment (doc) 
      Dim emb As Variant 
      Dim nid As String 

      nid = doc.NoteID 

      Dim rtitem As Variant 

      Set rtitem = doc.GetFirstItem( "Body" ) 

      Dim pathName As String, fileName As String, mydir As String, 
newfilename As String 
      mydir = "Coda" 
      pathName$ = "P:\" & mydir 

      fileName$ = Dir$(pathName$, 16) 

      Dim boxType As Long, answer As Integer 
      boxType& = 36 

      If fileName$ = "" Then 
            answer% = Messagebox("Directory "& pathName$ &" does not exist, 
would you like to create it ?", boxType&, "Create" & mydir & " on P:\ ?") 
            If answer% = 6 Then 
                  Mkdir pathname$ 

                  fileName$ = Dir$(pathName$, 16) 

                  If filename$ <> "" Then 
                        If ( rtitem.Type = RICHTEXT ) Then 

                              Forall o In rtitem.EmbeddedObjects 
                                    If ( o.Type = EMBED_ATTACHMENT ) Then 
                                          newfilename$ = pathname$ & "\" & 
o.source 
                                          Call o.ExtractFile (newfilename$ 
) 
                                    End If 
                              End Forall 

                        End If 
                  End If 
            End If 

      Else 
            If ( rtitem.Type = RICHTEXT ) Then 

                  Forall o In rtitem.EmbeddedObjects 
                        If ( o.Type = EMBED_ATTACHMENT ) Then 
                              newfilename$ = pathname$ & "\" & o.source 
                              fileName$ = Dir$(NewFileName$, 0) 
                              If fileName$ <> "" Then 
                                    answer% = Messagebox("File "& 
NewFileName$ &" already exists, would you like to overwirite it ?", 
boxType&, "Overwrite" & NewFileName$ & " ?") 
                                    If answer% = 6 Then 
                                          Call o.ExtractFile (newfilename$ 
) 
                                    End If 
                              Else 
                                    Call o.ExtractFile (newfilename$ ) 
                              End If 

                        End If 
                  End Forall 

            End If 
      End If 
End Sub

Import Lotus Notes Database into Access/SQL - What about Documents?

1 Answers