Solved

Please help with extracting data from Lotus Notes database

Posted on 2004-08-09
5
411 Views
Last Modified: 2013-12-18
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.
0
Comment
Question by:km1039
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:Bozzie4
ID: 11759732
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
 

Author Comment

by:km1039
ID: 11766379
i keep getting type mismatch error on "Forall o In doc.EmbeddedObjects"
0
 
LVL 15

Accepted Solution

by:
Bozzie4 earned 500 total points
ID: 11766650
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
 

Author Comment

by:km1039
ID: 11766912
It did work.
You dont know how much I appreciate your help.  I have been struggling with this for days.  Thanks.
0
 
LVL 15

Expert Comment

by:Bozzie4
ID: 11767352
You're welcome :-)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now