Lotus Notes View with multiline data Export to Excel

Question

I need to export a lotus notes view to excel. The problem is, I have two columns in the view which displays multiple values with "New line" as the separator. I tried the inbuilt export function as well as with a new lotus script export function with few formatting. In both the cases the multiple values cannot be made to appear in one cell. Only the first value is displayed in each row. The rest of the values are ignored. Our User wants the excel report only with Multiple values in New line and not with any other delimiter.

Kindly help me with your suggestions. I am using Lotus notes 6.5 and Microsoft office 2010.

Thank you.

Karl-Henry Martinsson Karl-Henry Martinsson · Accepted Answer · 2013-03-27T17:09:39

Write the export in Lotusscript. Not hard, and you get full control of the export. If the fields are multi-value fields, simply read the values as a variant and then write them to the output file with newline between each item.

Here is one idea of how to solve it:

%REM
    Agent View Export
    Created Mar 27, 2013 by Karl-Henry Martinsson
    Description: Code to export a specified view as CSV.
    Copyright (c) 2013 by Karl-Henry Martinsson
    This code is distributed under the terms of 
    the GNU General Public License V3. 
    See http://www.gnu.org/licenses/gpl.txt
%END REM

Option Public
Option Declare

Class RowData
    Public column List As String

    Public Sub New()
    End Sub

    Public Sub SetColumnHeader(view As NotesView)
        Dim viewcolumn As NotesViewColumn
        Dim cnt As Integer
        ForAll vc In view.Columns
            Set viewcolumn = vc
            column(CStr(cnt)) = viewcolumn.Title 
            cnt = cnt + 1
        End Forall  
    End Sub

    Public Sub SetColumnValues(values As Variant)
        Dim cnt As Integer
        Dim tmp As String 
        ForAll v In values
            If IsArray(v) Then
                ForAll c In v
                    tmp = tmp + c + Chr$(13)
                End ForAll
                column(CStr(cnt)) = Left$(tmp,Len(tmp)-1)
            Else
                column(CStr(cnt)) = v 
            End If
            cnt = cnt + 1
        End ForAll          
    End Sub
End Class

Class CSVData
    Private row List As RowData
    Private rowcnt As Long

    %REM
        Function New
        Description: Open the view and read view data 
        into a list of RowData objects.
    %END REM    
    Public Sub New(server As String, database As String, viewname As String)
        Dim db As NotesDatabase
        Dim view As NotesView
        Dim col As NotesViewEntryCollection
        Dim entry As NotesViewEntry
        Dim colcnt As Integer

        Set db = New NotesDatabase(server, database)
        If db Is Nothing Then
            MsgBox "Could not open " + database + " on " + server,16,"Error" 
            Exit Sub
        End If
        Set view = db.GetView(viewname)
        If view Is Nothing Then
            MsgBox "Could not access view " + viewname + ".",16,"Error" 
            Exit Sub
        End If
        Set col = view.AllEntries()
        rowcnt = 0
        Set entry = col.GetFirstEntry()
        Set row("Header") = New RowData()
        Call row("Header").SetColumnHeader(view)
        Do Until entry Is Nothing
            rowcnt = rowcnt + 1
            Set row(CStr(rowcnt)) = New RowData()
            Call row(CStr(rowcnt)).SetColumnValues(entry.ColumnValues)
            Set entry = col.GetNextEntry(entry) 
        Loop
    End Sub

    %REM
        Function CSVArray
        Description: Returns a string array of CSV data by row
    %END REM
    Public Function CSVArray() As Variant
        Dim rowarray() As String 
        Dim textrow As String
        Dim cnt As Long
        ReDim rowarray(rowcnt) As String

        ForAll r In row
            textrow = ""
            ForAll h In r.column 
                textrow = textrow + |"| + Replace(h,Chr$(13),"\n") + |",|
            End ForAll
            rowarray(cnt) = Left$(textrow,Len(textrow)-1)
            cnt = cnt + 1
        End ForAll  
        CSVArray = rowarray
    End Function

    %REM
        Function HTMLArray
        Description: Returns a string array of HTML data by row
    %END REM
    Public Function HTMLArray() As Variant
        Dim rowarray() As String 
        Dim textrow As String
        Dim cnt As Long
        ReDim rowarray(rowcnt) As String

        ForAll r In row
            textrow = ""
            ForAll h In r.column 
                textrow = textrow + |<td>| + Replace(h,Chr$(13),"<br>") + |</td>|
            End ForAll
            rowarray(cnt) = "<tr>" + textrow + "</tr>"
            cnt = cnt + 1
        End ForAll  
        HTMLArray = rowarray
    End Function

End Class


%REM 
     ******************************** 
     Example of how to call the class
     ********************************
%END REM
Sub Initialize
    Dim csv As CSVData
    Dim outfile As String

    Set csv = New CSVData("DominoServer/YourDomain", "names.nsf", "People\By Last Name")
    outfile = "c:\ExcelExportTest.csv"
    Open outfile For Output As #1
    ForAll row In csv.CSVArray()
        Print #1, row
    End ForAll
    Close #1

    outfile = "c:\ExcelExportTest.xls"
    Open outfile For Output As #2
    Print #2, "<table>"
    ForAll row In csv.HTMLArray()
        Print #2, row
    End ForAll
    Print #2, "</table>"
    Close #2
End Sub

Lotus Notes View with multiline data Export to Excel

1 Answers