Most of the Raiser’s Edge users that I speak to complain that Blackbaud only offers CSV and tab delimited files as a way of importing data. They say that this is very limiting, that CSV can be unwieldy to work with outside of The Raiser’s Edge. They are also very pleased to hear that any customisation that I write allows them to import from Excel and also writes reports to Excel too. Excel offers so much functionality to prepare your data. However there are problems working with the Excel object model from within the API

Firstly whenever you access Excel from code you load a full version of Excel in the background. This in itself would not be a problem as long as the system is sufficiently powerful. However as soon as the user opens another file in Excel it opens up the file being processed. Of course, what happens when the user then closes Excel? The file being worked on is also closed and the code crashes. There are ways around this by ensuring that the Excel always opens a new file in a new process but if Excel crashes this setting remains so it is impossible to open Excel files directly (unless you change the option back).

Then there is a further problem that prevents Excel from closing. When you work  with COM objects from .NET you need to ensure that you do not hold on to references as they will not be released otherwise. Excel seems to… (excuse the pun) excel at this. There are many articles online about this and how to prevent it but however hard you try it something slips through the net and Excel fails to close properly.

Despite these issues all of my customisations that read from or write to files use Excel. However now there is a new Excel format (xlsx) which my libraries do not handle and the above issues still persist.

Time for a change.

I stumbled across Gembox spreadsheet. It allowed me to work with the file format without having Excel present on the machine. This gave a much lower memory footprint. What is more it also allowed me to work with all the file formats (xls, xlsx, csv, tab delimited and semi-colon delimited) without changing the basic code.

I am currently working on transfering my code to use this new assembly. Watch this space for a new version of IDLookup supporting these new file formats and a lower memory footprint which should (hopefully) speed up the processing and make it easier and faster to work with larger files.