While reading the developerWorks forums, I have noted that some tasks seem to be common, and users often have issues with it. One very common is to export data from Notes/Domino to Excel. My guess is that some manager/executive want to get the data to manipulate and analyze it in Excel, which they know better, and where reporting can be done easier.
First of all, for anyone that want to do real integration with Excel (or Word, or any other office program), I would suggest to look at the presentations given by John Head at Lotusphere and other conferences in the past:
Lotusphere 2011 – JMP208
IamLUG 2009 – Integration and Coexistance
Lotusphere 2006 – BP309
Lotusphere 2005 – JMP108
But if you just need to export raw data to Excel, there are a couple easy ways to do that. You don’t need to automate Excel, something the programmers posting in the forums seem to frequently do. That just makes things more complicated, and if you run the code in a server agent, Microsoft Office have to be installed on the server itself. Different versions of Excel also cause different issues.
The different methods I use to export Notes data to Excel are as follows:
- Create a CSV file, which can be opened in Excel
- Create a HTML table in a file with .xls extension, which can be opened in Excel
- Create a HTML table and return it through a browser, with a content type set to open in Excel.
Depending on what the requirement is, I usually choose one of those method.
Method 1 – CSV file (local)
The easiest way to get data from Notes to Excel (or any other spreadsheet program) is to simply save the data as comma-separated values (CSV). Use regular Lotusscript file operations and write the values to the file, encased in double quotes and separated by a comma. You need to build a string containing a complete line, as the Print statement adds a line break to the end automatically.
A CSV file could look like this:
FirstName,LastName,Department,StartYear "Karl-Henry","Martinsson","IT","2002" "John","Smith","Accounting","2009"
The first row is the header, with the field names listed. This helps on the receiving side, but is optional. Dates and numbers don’t need quotes around them, as long as they don’t contain commas. So make a habit to format any numbers without commas.
Method 2 – HTML file (local)
This method is very similar to the CSV method. The difference is that you create a table in HTML, matching the cells in Excel you want to create/populate. But instead of creating a file with the extension .htm or .html, you give it the extension .xls. Excel is intelligent enough to identify it as HTML and map the content correctly.
The biggest advantages of using a HTML file instead of CSV is that you get more control over the formatting. By using colspan and rowspan, you can merge cells together, for example in a header. You can also use markup to make cells bold or italic, or even set the text and background colors.
Method 3 – HTML (through browser)
This method is a variant of method 2. But instead of calling the agent inside the Notes client and creating a file somewhere in the file system, the agent is called through a URL. This means the Domino HTTP task must be enabled on the server. The URL can be called from inside the Notes client using the @URLOpen function, or the OpenURL method of the NotesUIWorkspace class.
Instead of writing to a file, use the Print statement to print to the browser. The first line printed must be the content type, so the browser realize what application to open the file in. For Excel, it looks like this:
Print "content-type:application/vnd.ms-excel"
It also works with (among other) “application/msexcel” and “application/xls”, but the one above is the official one, so I suggest using that.
After you print the content type, simply print the HTML table code to the browser. This will open the HTML in Excel after the agent is done. It’s that easy.
Although not automatic I have been a HUGE fan of the copy as a table function, requires precious little development time, and a little training time. Works everywhere.
Yes, that is a very useful function, I use it almost daily when I send out users at work links to documentation they did not read before entering IT tickets. 🙂
But the questions in teh developerWorks forums are from programmers who are trying to export Notes data into Excel, and that info might not even be in views.
I’ve found one problem with creating HTML files that are Excel via the web, namely that Office 2007 and 2010 have added security that warns they are not valid Excel files. It’s a problem bemoaned by many on the web, including Microsoft .NET developers. The only way to avoid the error is to suppress the check via a registry setting. Copy As Table will get around that, as will using something more heavyweight like Apache POI.
Method 4: Use the Apache POI library and create a real XLS(X) file.
Is it possible to export data from Notes DB using Visual Basic 6.0.(exporting the data directly to MS access or csv). if Yes, please help
Absolutely. Use COM for that. The VB code would be very similar to Lotusscript code (Lotusscript i based on an older version ov VB). Contact me though email (texasswede@gmail.com) if you are interested in some help with the development, I am available for custom development.
That’s a great news for me… Please send me some sample code or link and i don’t have any knowledge in lotusscripts but, good in vb
and how the server will react on its performance while using vb to extract data, because more than 500 employees will be accessing the data base
I sent you an email.
Hello Karl,
Regarding the Method 3 – HTML (through browser) Print method using the URL, would I iterate using the $$Return or a WebQuerySave event or elsewhere (a button using JavaScript?)
I’ve been building in Notes since ’95 but recently switched jobs and have brought up an environment where all apps are used through the browser (no fiddling with client configurations).
Any help- would be appreciated.
Thanks,
Mike
Sorry to respond so late…
I would put the code in a Lotusscript agent, and then call it directly on the server:
http://www.domain.com/database.nsf/ExcelExportAgent?OpenAgent
You could take a look at this code:
http://blog.texasswede.com/export-notes-view-to-excel-with-multi-value-fields/
Hi there,
Do you think that will be possible to schedule export all e-mails in inbox to excel?
What I mean – want every day to have my inbox on excel (export to csv, or different readable in excel way). If I have possibilities to automate export will stop to wonder if I missed to export, or lose time to export again. 🙂
Most important for me information can be structured in followed columns:
1. Sender (column Who)
2. Subject. If no subject, get all message from e-mail.
3. Receiving date (column Date)
4. Flag if e-mail was opened (read)
After that I can automatize import in excel and will have copy of important information from inbox.
Best regards
Todor Todorov
P.S. Just as detail (think not so important), but most e-mails was written on Cyrillic.
Hi again,
Just to ask if there is a way for scheduled export.
Best regards
Todor Todorov
Todor,
You can use method 1 and method 2 in a scheduled agent.
Back in 2013 I posted a class I wrote that can help you export Notes views as CSV or HTML. You can find it at http://blog.texasswede.com/export-notes-view-to-excel-with-multi-value-fields/.
If you need help creating a scheduled agent for your use, contact me at karl-henry@demandbettersolutions.com