SNTT : Export any Lotus Notes View to CSV or Excel, automagically

Posted on by — 9 Comments ↓

Here is a some very generic code which will work on any Lotus Notes View to print out the contents to a Comma Separated Value (CSV) file, or as an Excel File, with minimum formating, as a general export routines.

ViewCSVPrint.txt
ViewExcelPrint.txt

Import them to a manual agent or a shared action button, open a view and go.

The CVS export prompts you for a file Location, the Excel export (assuming you have MS Excel installed) opens a excel file (it makes it visible at the end) with the results. (you could easily modidy the Excel export to prompt for a – or just use a hard coded default – file path and name, and save to the file system.

A couple of things to note :

  • The ViewCSVPrint routine in the CVS export does not use any UI objects so it could be called by a scheduled agent and run on the server, as long as you supply a valid View object (using a notesDatabase.GetView( viewName$ ) call) and a valid FileNamePath. By giving the file a “.csv” extension the file can be auto magically opened in excel, as well as processed by other programs. (CSV and XML files are the Lingua franca‘s of computer systems.)
  • The Excel export must run on a workstation, which must have Excel installed. It is possible to use Java (or other languages) libraries that create excel files on a sever, but I’m not going there. I’m using the LSX OLE libraries available and you can figure out more by looking at Microsoft’s own excel VBA help.
  • In the Excel export I’m using an ugly hack in the intializeColumnArrayValues sub routine to map the column number to the excel column letter label. This limits it to 12 columns at presents, but would be trival to extend. Even better if I figured out some clever way to translate column # 17 to chr(“A”) + 14 – 1 = “n” or something. When I can marshal my brain cells I’ll do just that for a future SNTT.
  • The CVS also runs very very fast for very large numbers of rows, compared to the Excel export. Seconds compared to minutes.
  • Both exports works best with flat (non- categorized) views where all the columns have titles and without hidden columns. This is because I’m using a ViewEntryCollection which never contains categories. But, they will work ony any view.
  • For the Excel export, I’m splitting and freezing the header row, and doing some fit to size on the data, but that’s it. You could do some more formatting by querying the columns and depending on the type (number or data) or formating (left alignment, number of decimals) then apply that to the excel data column.

Enjoy (technorati.com tags :, )

Update : See also
SNTT : Export any Lotus Notes View to Excel from Domino Web, automagically

Update March 20th 2007 : Lotus Guru, aka Kevin Pettitt, borrowed the code here and worked it to Make Attractive Exports of Categorized Notes Views. That’a Boy Kev!

9 Replies to “SNTT : Export any Lotus Notes View to CSV or Excel, automagically”

  1. Pingback: False Positives » Blog Archive » Lotus Guru makes a more Attractive Export of Categorized Notes Views to Excel

  2. Pingback: SnTT: Notes Document Structured Text Export for Multilingual (UTF-8) | False Positives

  4. Hi Ian!

    This csv export is a wonderful piece of code. Could you tell me how I could modify the code so that the save dialog box doesn’t show up? I’d like the csv output to be saved automatically to a specific directory.

    Best Regard,
    Bob C

    Reply ↓

  6. Note that the csv code has issues with multivalue fields in a view, at least if they’re imploded with an @Newline separator. I just changed the view to implode them with a semicolon instead, and all was well.

    Very useful bit of code here.

    Reply ↓

  7. Another important safety tip: when exporting a Notes view to csv, be sure that the stuff you’re exporting doesn’t have, you know, commas in it. My export kept spreading the text from a single field across multiple columns, and I beat my head against a wall for a couple of days trying to figure out why! LOL

    The Excel code worked great with Excel 2007, though.

    Sean

    Reply ↓

  9. Hi Ian!
    This csv manually export is a wonderful piece of code. I would like to calle it by a scheduled agent and run on the server but I wasn´t to be able to do it, I´ve tried whit everything I could and nothing, At the moment I don´t know what to do . Could you tell me how I could modify the code so.

    The code I use is next:

    Sub Initialize
    Dim s As NotesSession
    Dim db As NotesDatabase
    Dim view As NotesView
    Dim filenames As Variant
    Dim timea As Variant
    Dim nombre As Variant

    Set s = New NotesSession
    Set db = s.CurrentDatabase
    Set view = db.GetView(“ExtractSudeban”)
    timea = Format(Now(), “yyyymdd_hmmss”)
    nombre = view.Name + timea

    ‘ Get filename from user using the current ViewName as the default file name

    filenames = (“D:\Lotus\Domino\Data\Aplicaciones\Reclamos\”& nombre & “.csv”)

    If Not(Isempty(filenames)) Then
    Call ViewCSVPrint (view, filenames(0))
    End If

    End Sub

    Best Regard,
    Jackson

    Reply ↓

Leave a Reply