LotusScript - Export Form except some fields

Question

I am using the following site as a guide to export a Lotus Notes Database Form to a csv file.

http://searchdomino.techtarget.com/tip/How-to-export-data-from-a-Lotus-Notes-database-to-a-CSV-file

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
'Specify what form you want to export           
            If form.Name = "Server Information" Then            
                fieldCount = 0
                msgString = ""
                fileNum% = Freefile()
                fileName$ = "c:\temp\LOTUS_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)

        For x = 1 To collection.count
            Set doc =collection.GetNthDocument(x)
            values=""
            Forall formfield In form.Fields
                    Forall formfield.value  != 'AdditionalDocumentation'
                newvalue=doc.GetItemValue(formfield)
                values=values & |"| & newvalue(0) & |",| 
            End Forall
            End Forall

            Write #fileNum%,  |",| & values &|"|
            values=""
        Next

'now check aliases
        If Isempty(form.Aliases) Then
'Messagebox form.Name & " has no aliases"
        Else
            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)

                For x = 1 To collection.count
                    Set doc =collection.GetNthDocument(x)
                    values=""
                    Forall formfield In form.Fields
                        newvalue=doc.GetItemValue(formfield)
                        values=values & |"| & newvalue(0) & |",| 
                    End Forall

                    Write #fileNum%,  |",| & values &|"|
                    values=""
NextAliaz:
                Next
            End Forall
        End If

        Close fileNum%
    End Forall

End Sub

Which ever is easier, I would like to specify the fields that I want to export or export the whole form except a specific set of fields.

Tell us what you have tried, and what happened when you tried it. — Richard Schwartz

Karl-Henry Martinsson Karl-Henry Martinsson · Accepted Answer · 2016-05-24T13:39:44

Another way to do what Knut Herrmann suggests in his answer is to create a list of the fields you want to export, then in the ForAll loop you test if eaxch field is a member of that list:

Dim exportField List As String

exportField("FieldOne") = "FieldOne"
exportField("FieldTwo") = "FieldTwo"
exportField("FieldFive") = "FieldFive"

And the loop:

ForAll formfield In form.Fields
    If IsElement(exportField(formfield)) Then
        newvalue=doc.GetItemValue(formfield)
        values=values & |"| & CStr(newvalue(0)) & |",| 
    End If      
End ForAll

There is a reason I use a list of strings. Instead of putting the field name there, you could put special formatting commands, or indicate the data type. Then in the loop you use that to format the CSV output accordingly:

Dim exportField List As String

exportField("FieldOne") = "T"  '*** Text
exportField("FieldTwo") = "DT"  '*** Date and Time
exportField("FieldFive") = "N" '*** Numeric
exportField("FieldSix") = "D" '*** Date only

Then you just check the value and format the output properly.

LotusScript - Export Form except some fields

2 Answers