Programmatically Import Excel Worksheets Using IBM’s ActiveX Object Library

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:

1. First of all, make sure you reference the “IBM AS/400 iSeries Access for Windows ActiveX Object Library” in whatever development environment you’re using. If you’re going to use VBScript or JScript, you’ll need to use CreateObject to instantiate each object type.
2. The AS/400 System Object is used to establish a connection to your System i, i5, or iSeries.
3. The DatabaseUploadRequest and DatabaseUploadBIFFOptions are used together to create a request that will upload an Excel sheet into a DB2 table. (BIFF is the internal format that Excel worksheets use prior to version 2007.) Note that the file transfer library can’t yet handle the new XML file format (.XLSX) employed by Excel 2007. However, I’m guessing IBM will take care of this soon. Also note that in addition to Excel, other formats such as text, XML, and Lotus 1-2-3 are available.
4. When coding the upload request, about the only curve ball thrown our way is specifying the .FDF file. An .FDF file is a “PC File Description” file that informs the transfer utility how data is stored in the local PC file to be uploaded. I usually create my .FDF files by using the “Data Transfer From” iSeries Access utility. Simply use this program to create a “dummy” download of a blank copy of the file you want to upload into. When the download runs it will create a blank PC file and also create an .FDF file that will describe how data should be stored in the PC file. (To do this using “Data Transfer From,” specify an output device of “File,” click the “details” button, make sure the “Save File Transfer Description” box is checked and a file path and name for the .FDF file is filled in.) You will need to reference the location of the .FDF file in your code. If you use the File Transfer utility to create your FDF file, make sure you specify “Biff8 (MS Excel8)” as the file type. If you do not do this, you will receive a “Type Mismatch” error in the accompanying code.
5. The next step is to express how certain conversions will be performed. The one option that usually vexes people is the date formatting. Note that the DatabaseUploadBIFFOptions object has a ConvertDateAndTime property. When this property is set to true, date columns within the worksheet should use Excel’s date formatting (which is internally stored as a number.) When this value is false, the date columns should be formatted as text in the ISO format (yyyy-mm-dd). I’m not sure why IBM has been so stringent with the rules respecting dates as it seems like a little extra programming could make the upload utility far more forgiving than it is.
6. For the last step, we execute the Upload method of the DatabaseUploadRequest object and we’re done. Of course your code should contain error handling and other “what not” suitable for a production environment.

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.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot