Exporting data from Lotus Notes databases


Here's a free tool I wrote years ago for exporting data to Excel: Export to Excel 2.0

Also, I wrote some code to extract attachments from email, and this code could be adapted to work on other databases

Dim sDir As String
Dim s As NotesSession
Dim w As NotesUIWorkspace
Dim db As NotesDatabase
Dim dc As NotesDocumentCollection
Dim doc As NotesDocument

Sub Initialize

 Set s = New NotesSession
 Set w = New NotesUIWorkspace 
 Set db = s.CurrentDatabase
 Set dc = db.UnprocessedDocuments
 Set doc = dc.GetFirstDocument
 Dim rtItem As NotesRichTextItem
 Dim RTNames List As String
 Dim DOCNames List As String
 Dim itemCount As Integer
 Dim sDefaultFolder As String
 Dim x As Integer
 Dim vtDir As Variant
 Dim iCount As Integer
 Dim j As Integer
 Dim lngExportedCount As Long
 Dim attachmentObject As Variant

 x = Msgbox("This action will extract all
 attachments from the " & Cstr
(dc.Count) & _
 " document(s) you have selected, 
and place them into the folder of your 
choice." & _
 Chr(10) & Chr(10) & "Would you like 
to continue?", 32 + 4, "Export 
 If x <> 6 Then Exit Sub 

 sDefaultFolder = s.GetEnvironmentString
 If sDefaultFolder = "" Then sDefaultFolder = "F:"
 vtDir = w.SaveFileDialog( False, "Export 
attachments to which folder?", "All 
files|*.*", sDefaultFolder, "Choose 
Folder and Click Save")
 If Isempty(vtDir) Then Exit Sub
 sDir = Strleftback(vtDir(0), "\")
 Call s.SetEnvironmentVar
("LPP_ExportAttachments_DefaultFolder", sDir) 

 While Not (doc Is Nothing)

  iCount = 0
  itemCount = 0
  lngExportedCount = 0
  Erase RTNames
  Erase DocNames

  'Scan all items in document
  Forall i In doc.Items

   If i.Type = RICHTEXT Then
    Set rtItem = doc.GetfirstItem(i.Name)
    If Not Isempty(rtItem.EmbeddedObjects) 
     RTNames(itemCount) = Cstr(i.Name)
     itemCount = itemCount +1
    End If
   End If

  End Forall  

  For j = 0 To itemCount-1 
   Set rtItem = Nothing
   Set rtItem = doc.GetfirstItem(RTNames(j))
   Forall Obj In rtItem.EmbeddedObjects
    If ( Obj.Type = EMBED_ATTACHMENT ) 
     Call ExportAttachment(Obj)
     Call Obj.Remove
     Call doc.Save( False, True ) 
 'creates conflict doc if conflict exists
    End If 
   End Forall 

  'Scan all items in document
  Forall i In doc.Items

   If i.Type = ATTACHMENT Then

    DOCNames(lngExportedCount) = 
    lngExportedCount = lngExportedCount + 1

   End If

  End Forall

  For j% = 0 To lngExportedCount-1 
   Set attachmentObject = Nothing
   Set attachmentObject = 
   Call ExportAttachment(attachmentObject)   
   Call attachmentObject.Remove   
   Call doc.Save( False, True ) 
'creates conflict doc if conflict exists

  Set doc = dc.GetNextDocument(doc)

 Msgbox "Export Complete.", 16, "Finished"

End Sub

Sub ExportAttachment(o As Variant)

 Dim sAttachmentName As String
 Dim sNum As String
 Dim sTemp As String

 sAttachmentName = sDir 
& "\" & o.Source
 While Not (Dir$(sAttachmentName, 0) = "")
  sNum = Right
(Strleftback(sAttachmentName, "."), 2)
  If Isnumeric(sNum) Then
   sTemp = Strleftback(sAttachmentName, ".")
   sTemp = Left(sTemp, Len(sTemp) - 2)
   sAttachmentName = sTemp
 & Format$(Cint(sNum) + 1, "##00") & _
   "." & Strrightback(sAttachmentName, ".")
   sAttachmentName = 
Strleftback(sAttachmentName, ".") & _
   "01." & Strrightback(sAttachmentName, ".")
  End If

 Print "Exporting " & sAttachmentName
 'Save the file
 Call o.ExtractFile( sAttachmentName ) 

End Sub

Related videos on Youtube

Author by


Updated on September 17, 2022


  • Jak
    Jak 3 months

    I'm looking for a tool, hopefully low cost or free, to help me export Lotus Notes databases (not Lotus Notes mail files!) to any kind of format. CSV/Excel etc..

    I also need to be able to extract attachments in the database. They're all Word documents.

    I've found one called DetachIt - but it's high license cost puts it quite far out of reach.

    Thank you!

    • tony roth
      tony roth over 12 years
      I think MS has some tools to do this look up lotus notes transporter suite and they are free. Also if you open a database within notes it has export options.
    • tony roth
      tony roth over 12 years
      also there is odbc connector for lotus notes!
  • Jak
    Jak over 12 years
    I'm very unfamilar with LotusScript - usually a C# programmer. Will this remove the attachments? I simply want to copy them.. I'm a little concerned by the Obj.Remove line - but I honestly don't know what it's actually doing.
  • Ken Pespisa
    Ken Pespisa over 12 years
    It does, but you can comment out the two lines calling the Remove method, and the Save method that follows them, and then the code will simply extract without removing the attachments.