Solved

Please help with extracting data from Lotus Notes database

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

773 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