Solved

Please help with extracting data from Lotus Notes database

Posted on 2004-08-09
5
415 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

626 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