Solved

Please help with extracting data from Lotus Notes database

Posted on 2004-08-09
5
413 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 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