Programmatically Import Excel Worksheets Using IBM’s ActiveX Object Library
October 10, 2007 Michael Sansoterra
Note: The code accompanying this article is available for download here.
Everybody likes to export DB2 data to an Excel spreadsheet. It’s quick and easy, and there are plenty of ways to do it. On the other hand, I’m often asked by people, including fellow developers, to assist them with importing an Excel worksheet into a DB2 table. In fact, what developer hasn’t had the experience of a bean counter asking for assistance in uploading a spreadsheet into an ERP system?
Importing native Excel worksheets has always had some measure of difficulty. There are headings to worry about, date formatting issues, empty cells, and truncation errors, to name a few potential headaches. Giving the user an automated option is a further headache.
One common solution has been to save the data as a comma delimited text file and use the CPYFRMIMPF file command to place the data into a DB2 table. This method works well and requires little programming. Another good solution is to use the File Transfer utility that comes with iSeries Access.
But what if you have a need to programmatically upload a file without user intervention from a Windows desktop or server environment? For Windows programmers that can take advantage of component object model (COM) libraries (such as VB, Delphi, or .NET developers), this can be done relatively easy by using the (get ready for this) “IBM AS/400 iSeries Access for Windows ActiveX Object Library,” hereafter referred to as the “iSeries Access ActiveX Library.”
For those new to Windows programming, an ActiveX library is a set of pre-built, reusable routines usually centered about a specific task that Windows developers can leverage in their own applications (similar in concept to an RPG service program.) IBM’s iSeries Access ActiveX library contains programming interfaces to many of the same features available in the iSeries Access product, giving developers the ability to upload or download files, change a user password, manipulate data queues, and, when necessary, convert PC character and numeric data to formats used by the System i.
The code available at the beginning of this article contains sample Visual Basic for Applications (VBA) code that automatically pushes a spreadsheet into a DB2 for i5/OS table. The code is fairly trivial but I’ll outline a few highlights here:
Here is something else to watch for. For some reason, there isn’t an “ignore heading” option, so your uploaded worksheet should not contain headings, or else the headings should be only one row and contain the column name of the destination table column (case sensitive).
One possible benefit of using the file transfer utility when uploading or downloading data between DB2 for i5/OS and Excel comes from Web sites built around Microsoft technology (classic ASP, VB or ASP.NET). Often times these Web sites create or read spreadsheets using Excel as a COM automation server, which in turn requires the Excel product to be purchased and installed on the server. This scenario can possibly have substantial memory management issues if the process crashes and leaves Excel’s heavy footprint in memory. Using the IBM data transfer utility is one way developers using Microsoft’s tools can avoid the need for Excel to be present on the server.
I see the biggest benefit for this programming technique in a Web-based scenario where users can pick a destination table in a Web form, upload the spreadsheet to the server, and let the transfer take off automatically. If there are any problems, they can be brought back to the user’s attention. This scenario will negate the need for end users to have the data transfer options installed on their PCs, for IT personnel to maintain various transfer definitions on user PCs, etc.
Incidentally, don’t forget that there are objects available to assist with downloads as well. IBM has done a great service to developers by making these interfaces available in a Windows ActiveX library. There are plenty of additional options for you to research that I didn’t have time to mention, such as ignoring truncation and rounding errors, etc. For more detailed information on the “Data Transfer Automation Object” interfaces, click here.
Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. You can contact him through our contact page.