Monday, 13 March 2017

IBM Notes Tip: Export Data to Excel (CSV) using LotusScript Agents


During the past period I received several questions how to export all Notes data corresponding to a specific Notes Form to Excel (CSV) using a LotusScript Agent. In the example below a possible solution to export all data associated with a specific Notes Form to Excel using a LotusScript agent. In the second example a possible solution to export all data from all forms in the Notes database to Excel using a LotusScript agent. For each form a separate Excel file will be created.
Preview on GitHub Gist : Export Form Data to Excel(CSV) and Export All Data to Excel (CSV)

A. LotusScript Agent Export Data based on FormName

Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim fileName As String
Dim fileNum As Integer
Dim headerstring As String
Dim values As String
Dim selection As String
Dim collection As NotesDocumentCollection
Dim doc As NotesDocument
On Error Resume Next
Set db = session.CurrentDatabase
ForAll form In db.Forms
If IsEmpty(form.Fields) Then
MessageBox form.Name & " has no fields"
Else
'Export Form
Dim fieldcount As Integer
Dim msgString As String        
If form.Name = "FORMNAME" Then          
fieldCount = 0
msgString = ""
fileNum% = FreeFile()
fileName$ = "c:\temp\export\" & form.Name & ".csv"
Open FileName$ For Output As fileNum%
ForAll Field In form.Fields
msgString = msgString & Chr(10) & _
"" & Field
fieldCount = fieldCount + 1
headerstring=headerstring & |"| &Field &|",|
End ForAll
Write #fileNum%,  |",| & headerstring & |"|
headerstring=""
Else
End If
End If
selection = |Form="| & form.Name & |"|
Set collection=db.Search(selection, Nothing, 0)
Dim newvalue As Variant
Set doc = collection.GetFirstDocument
Do Until (doc Is Nothing)
values=""
ForAll formfield In form.Fields
newvalue=doc.GetItemValue(formfield)
values=values & |"| & newvalue(0) & |",|
End ForAll
Write #fileNum%,  |",| & values &|"|
values=""
Set doc = collection.GetNextDocument(doc)
Loop
'Check Aliases
ForAll aliaz In form.Aliases
If aliaz = form.Name Then
GoTo NextAliaz   'alias is same as form name
End If
selection = |Form="| & aliaz & |"|
Set collection=db.Search(selection, Nothing, 0)
Set doc = collection.GetFirstDocument
Do Until (doc Is Nothing)
values=""
ForAll formfield In form.Fields
newvalue=doc.GetItemValue(formfield)
values=values & |"| & newvalue(0) & |",|
End ForAll
Write #fileNum%,  |",| & values &|"|
values=""
NextAliaz:
Set doc = collection.GetNextDocument(doc)
Loop
End ForAll
Close fileNum%
End ForAll
End Sub

B. LotusScript Agent Export Data All Forms in Notes Database

Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim fileName As String
Dim fileNum As Integer
Dim headerstring As String
Dim values As String
Dim selection As String
Dim collection As NotesDocumentCollection
Dim doc As NotesDocument
On Error Resume Next
Set db = session.CurrentDatabase
ForAll form In db.Forms
If IsEmpty(form.Fields) Then
MessageBox form.Name & " has no fields"
Else
Dim fieldCount As Integer
Dim msgString As String
fieldCount = 0
msgString = ""
fileNum% = FreeFile()
fileName$ = "c:\temp\export\" & form.Name & ".csv"
Open FileName$ For Output As fileNum%
ForAll Field In form.Fields
msgString = msgString & Chr(10) & _
"     " & Field
fieldCount = fieldCount + 1
headerstring=headerstring & |"| &Field &|",|
End ForAll
Write #fileNum%,  |",| & headerstring & |"|
headerstring=""
End If
selection = |Form="| & form.Name & |"|
Set collection=db.Search(selection, Nothing, 0)
Dim newvalue As Variant
Set doc =collection.GetFirstDocument
Do Until (doc Is Nothing)
values=""
ForAll formfield In form.Fields
newvalue=doc.GetItemValue(formfield)
values=values & |"| & newvalue(0) & |",|
End ForAll
Write #fileNum%,  |",| & values &|"|
values=""
Set doc = collection.GetNextDocument(doc)
Loop
Close fileNum%
End ForAll
End Sub

No comments:

Post a Comment