Please help with extracting data from Lotus Notes database

I have been asked to rewrite a web application initially developed in Lotus Notes/Domino.  The application is a web based jobs database.  I know nothing about Lotus Notes or Domino so I have been pulling my hair out trying to export the data out of Lotus Notes and into a text file or some other form I can work with.  I have been able to extract most of the data out by downloading the ODBC driver and importing it into SQL Server.  However, each document in the Lotus Notes database has a file attached to it that I also need to retrieve (resume files).  I originally thought these files might be stored in a directory on the server but it looks like thay are actually stored in the database.  I have figured out how to open the database in Lotus Notes and "detach" the attachment from the document but there are 1300 documents.  I need a way to retrieve every attachment and save it to a folder with a different file name.  Please help.
km1039Asked:
Who is Participating?
 
Bozzie4Connect With a Mentor Commented:
OK, this 1 works :

Sub Initialize
      On Error Goto handleError
      Dim s As New NotesSession
      Dim db As notesdatabase
      Dim doc As NotesDocument
      Dim rtitem As Variant
      Dim fileName As String
      Dim fromName As NotesName
      Dim Path As String
      Dim key As String
      Dim coll As notesdocumentcollection
      Const ATTR_DIRECTORY = 16
      Const EMBED_ATTACHMENT = 1454
      Const BasePath = "C:\Temp"
      Set db = s.currentdatabase
      Set coll = db.alldocuments
      Dim filecount As Long
      Dim o As NotesEmbeddedObject
      Dim nitem As notesitem
      
      fileCount = 0    
      Set doc = coll.getfirstdocument
      Do While Not doc Is Nothing
'check if document has attachments
            If doc.hasEmbedded Then
                        key = doc.Title(0) ' OR PUT THE CANDIDATES NAME HERE eg. doc.Firstname(0) + " " +doc.LastNAme(0), or the unique id, doc.universalid
                        Forall i In doc.Items
                        If i.type = Attachment Then
                              Set nitem = doc.GetFirstItem(i.name)
                              Set o = doc.GetAttachment(i.values(0))
                              
                              filename = basepath & "\" & key & "_" & o.source
                              Print "Extracting " & filename
                              Call o.extractfile(filename)
                              filecount = filecount+1
                        End If
                        
                  End Forall
            End If
            
            Set doc = coll.getnextdocument(doc)
      Loop
      Print "" & filecount & " documents processed"
      Exit Sub
handleError:
      msgbox "ERROR : " & Err & " on line " & Erl & " : " & Error$
      Resume Next
End Sub
0
 
Bozzie4Commented:
Yes, there are even several ways.

I think you can also use the ODBC driver, to get the attachments as blobs in your sql dtabase.

Else, write a little lotusscript agent to do this (or use your favourite language to do this)

Sub Initialize
on error goto handleError
     Dim s As New NotesSession
dim db as notesdatabase
     Dim doc As NotesDocument
     Dim rtitem As Variant
     Dim fileName As String
     Dim fromName As NotesName
     Dim Path As String
dim key as string
dim coll as notesdocumentcollection
     Const ATTR_DIRECTORY = 16
     Const EMBED_ATTACHMENT = 1454
     Const BasePath = "C:\Temp"
     set db = s.currentdatabase
set coll = db.alldocuments

     fileCount = 0    
     Set doc = coll.getfirstdocument
do while not doc is nothing
'check if document has attachments
if doc.hasEmbedded then
key = doc.Title(0) ' OR PUT THE CANDIDATES NAME HERE eg. doc.Firstname(0) + " " +doc.LastNAme(0), or the unique id, doc.universalid
Forall o In doc.EmbeddedObjects
     Print o.name
Call o.extractfile( basePath & "\" & key & "\" & o.Source )
    End Forall

end if

set doc = coll.getnextdocument(doc)
loop
exit sub
handleError:
msgbox "ERROR : " & err & " on line " & erl & " : " & error$
resume next
 end sub
0
 
km1039Author Commented:
i keep getting type mismatch error on "Forall o In doc.EmbeddedObjects"
0
 
km1039Author Commented:
It did work.
You dont know how much I appreciate your help.  I have been struggling with this for days.  Thanks.
0
 
Bozzie4Commented:
You're welcome :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.