Yesterday I had a requirement to export a couple of views to csv files. We’ve done similar exports before, usually specific to the particular functions, so have used the traditional file handling functions that have been around for years – Open, Input (for reading), Write, Close.
On this occasion, because I was exporting two views and to minimise the risk of columns being added to the export views but not included in the export agent, I wanted to build a more scalable solution. I knew that the NotesView class has a Columns property and from that I could get the column titles. I also knew I could iterate through the NotesViewEntries and write out the values. I also knew I had to handle commas in any of those values. What I didn’t know was how to write the values to the text file.
The usual method for writing using the above file handling functions is:
Write #fileNum%, strOne, strTwo
I couldn’t find a way to write multiple values to the same line within a loop, and concatenating all values into a comma-delimited string output just that – a comma delimited string of “value1, value2” instead of what I needed, value1, value2
After an amount of head-scratching a colleague encouraged me to go back to something I had tried to get my head around earlier in the day: the NotesStream class. This effectively enabled me to write to a file, defining the character set and where the line feeds and carriage returns came myself. Very soon afterwards, I had a nice little function that will export all entries from any view to a csv file, handling commas and multi-value fields. I couldn’t find anything similar on OpenNTF, so I’ve posted it up there. The code snippet is here and just requires a filepath and a view. I would recommend (as I have in my agent) setting AutoUpdate to false for your view before passing it in, just to avoid the well-documented issues with 7.0.4 and 8.x.
I tend to use: output$ = strOne & strTwo print #fileNum% , output$ Allows a bit more control then write. Or maybe it is a bad habit from VB days. 🙂 For an easier method you can just export the view as CSV. More information here.
Thanks, Simon. It’s always useful to get input (pardon the pun!). It’s one of those cases where I tried so much, I confused myself.
I realise the one thing I forgot to include in my article was that the export was a scheduled agent rather than a user-triggered action from the UI. I agree the in-built FileExport is best for user-triggered exports, if possible – there’s no point re-inventing the wheel.
This is by far the best export to excel project. I use this in every database I build. It gives you the ability to format the export.
Devin Olsen has done a bang up job on this:
{ Link }