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 :Show-n-Tell+Thursday, SnTT)
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!
Pingback: False Positives » Blog Archive » Lotus Guru makes a more Attractive Export of Categorized Notes Views to Excel
Pingback: SnTT: Notes Document Structured Text Export for Multilingual (UTF-8) | False Positives
In Notes R5, if the column value is Special Text, it is not exported. Any ways to overcome this?
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
thanks for the code bro!! saved my ass 🙂
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.
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
Sean,
Thanks for the praise and the added value tips! Glad it helped
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