SNTT : Pulling Notes data into Excel via http / XML

Just a very quick tip to show how you can dynamically pull your latest Notes / Domino data into an Excel Spreadsheet.
Note that this method will only work for domino applications which do not require authentication

Basic Idea

In Excel go to Data and click on the Data and then “From Web” options

Image:SNTT : Pulling Notes data into Excel via http / XML

Next paste in the URL of your Domino XML feed ( more on that later )

Accept all of the options and your data will be added in a nicely formatted table.
The table is also given a name so that when you reference it from pivot charts etc.. the chart will always contain all of the data from the XML feed.

Creating the XML Feed

I knocked up a simple agent that will generate XML based on a search string or a view name and some other configuration.
The code is not great and should really have been done using classes but it works well enough. I have attached a demo database to the end of this article.

The config looks like this :

selection##view##vActionsAll##
reference##field text##Reference_Tx##TBA
days_old##field delta date days##IssuedDate_Tx##@now
unid##formula##docalcres = “this bit is calculated => ” + doc.universalid##

The construction of the configuration varies. For line 2
part1 = XML property name
part2 = “the method”
part3 = the Notes filed name
part4 = the value to publish if the field name is empty

for line 3
part1 = XML property name
part2 = “the method”
part3 = the first date
part4 = the second date

for line 4
part 4 is a lotusscript expression that will be evaluated.

You can see the output here :
http://deliverytoolkit.com/public/fdt/moc/mocdemo.nsf/xmlagent?openagent&XML_Feed_Test

Numbers as text and modifying the schema

Although Excel is very clever in automatically generating a schema for the XML feed it does not allow you to edit it. This is where the new .xlsx format comes into its own.

If you change the .xlsx to .zip you can open the Excel file and modify the schema.

Image:SNTT : Pulling Notes data into Excel via http / XML

Image:SNTT : Pulling Notes data into Excel via http / XML

Note : you will need to delete the affected column in Excel and re-create it for the new schema to kick in

Image:SNTT : Pulling Notes data into Excel via http / XML

Thanks to Gary Devendorf

I have been using variations of this technique for some time. The idea originally came from Gary Devendorf who has published examples of how it can be done with earlier versions of Excel.

Download

The download link is below. You can see examples of the same database here :
http://seancull.co.uk/public/examples/SC/XML_Excel_Demo.nsf/xmlagent?openagent&XML_DEMO_View

XML_Excel_Demo_Download.zip
Book1.xlsx

Leave a Reply

Your email address will not be published. Required fields are marked *

4 + 8 =