Exporting data from Lotus Notes databases
5,203
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
(Declarations)
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
Attachments")
If x <> 6 Then Exit Sub
sDefaultFolder = s.GetEnvironmentString
("LPP_ExportAttachments_DefaultFolder")
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)
Then
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 )
Then
Call ExportAttachment(Obj)
Call Obj.Remove
Call doc.Save( False, True )
'creates conflict doc if conflict exists
End If
End Forall
Next
'Scan all items in document
Forall i In doc.Items
If i.Type = ATTACHMENT Then
DOCNames(lngExportedCount) =
i.Values(0)
lngExportedCount = lngExportedCount + 1
End If
End Forall
For j% = 0 To lngExportedCount-1
Set attachmentObject = Nothing
Set attachmentObject =
doc.GetAttachment(DOCNames(j%))
Call ExportAttachment(attachmentObject)
Call attachmentObject.Remove
Call doc.Save( False, True )
'creates conflict doc if conflict exists
Next
Set doc = dc.GetNextDocument(doc)
Wend
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, ".")
Else
sAttachmentName =
Strleftback(sAttachmentName, ".") & _
"01." & Strrightback(sAttachmentName, ".")
End If
Wend
Print "Exporting " & sAttachmentName
'Save the file
Call o.ExtractFile( sAttachmentName )
End Sub
Related videos on Youtube
Author by
Jak
Updated on September 17, 2022Comments
-
Jak over 1 year
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 almost 14 yearsI 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 almost 14 yearsalso there is odbc connector for lotus notes!
-
-
Jak almost 14 yearsI'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 almost 14 yearsIt 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.