The Four Hundred

OS/400 Edition
Volume 12, Number 5 -- February 3, 2003

Admin Alert: An Easy Way to Import OS/400 Data into Excel

by Joe Hertvik

Microsoft's Excel spreadsheet is one of the most successful and least appreciated of the company's tools. Excel is the premiere spreadsheet solution, literally eliminating its competitors (even Lotus), and it's an invaluable tool for representing information in several different formats. And when you want to import OS/400 information into Excel spreadsheets, IBM provides another valuable tool in the Client Access Express Data Transfer Excel Add-in.

The Data Transfer Excel Add-In is installed on Client Access and iSeries Access for Windows desktops. For this article, I'll discuss the Excel Add-In that comes with Client Access Express for Windows V5R1, but the same concepts apply for other Client Access versions.

The Excel Add-In is generally installed as part of a typical Express client install, but if it isn't present on your Windows desktop it can easily be added by performing a Custom Install from the Selective Setup program, found in the IBM AS/400 Client Access Express folder. You can install the Data Transfer Excel Add-In from the Data Access, Data Transfer node, on the Client Access Express Component Selection screen inside Selective Setup. It's also worth noting that, unlike the Client Access Data Transfer function, the Data Transfer Excel Add-In is not a licensed Client Access function. This means it can be installed and distributed to many different Windows workstations without worrying about your shop's Client Access licensing.

Using the Excel Add-in to work with OS/400 data inside your spreadsheets is pretty simple. Select the Data pull-down menu from the Microsoft Excel menu bar. You'll see two new menu options for working with OS/400 data inside Excel: Transfer Data from AS/400 and Transfer Data to AS/400.

To transfer OS/400 data into your spreadsheet, highlight the first spreadsheet cell that you want your data to be imported to, then choose the Transfer Data from AS/400 option. This option presents you with a Transfer Request panel, which asks if you want to create a new request or import data into your spreadsheet from a previously defined Client Access Express data transfer request file (which was created in an earlier Excel session or in the Client Access Express Data Transfer from AS/400 program). Express Data Transfer requests usually have extensions that end in *.dto, *.tto, *.dt, or *.rto. Retrieved data is returned to your program at the spreadsheet location you specified before you started the Transfer Data from AS/400 option.

If you choose to create a new Transfer Request to import data into your spreadsheet, the Transfer Data from AS/400 option will take you through a Data Transfer wizard, which prompts you to fill in all the relevant transfer parameters, including the following:

The iSeries or AS/400 system name that contains the data.
The library, file, and member name whose information you want to transfer to your spreadsheet.
Data transfer options, including SQL-like record-selection criteria, the fields to be downloaded, and how to sequence the downloaded data.
Runtime properties, including how to convert CCSID 65535 data; which libraries to include in the job's library list; and connection and startup information.
Whether you want to save the new transfer request as a Data Transfer file, and the name of the Data Transfer file to be saved (this allows you to run the request again at a later date without having to re-input the parameters).

Once the Wizard completes and the transfer runs, the field output from your data transfer is inserted into the spreadsheet at the specified cell location.

Just as you use the Excel Add-In to download OS/400 information to a spreadsheet, the Add-In also added a Transfer Data to AS/400 option on the Data pull-down menu, on the Excel menu bar. The Transfer Data to AS/400 Request screen allows you to use a Client Access Data Transfer file (*.dtt, .tfr, .dt, .rto) to send Excel data to an OS/400 file. Excel spreadsheet data is transferred by rows straight into an OS/400 database, where each row is transferred into an OS/400 record in the target file, and you specify the starting and ending Excel spreadsheet cell positions whose data will be transferred. The Excel Add-In will also transfer data from a particular spreadsheet column into specific fields in an OS/400 database by using the mapping information contained in a Client Access data transfer field description file (*.fdf).

Like the Data Transfer from AS/400 option, there is a Data Transfer to AS/400 wizard, which you can access from the Excel menu bar. This wizard will guide you through selecting all the appropriate OS/400 upload transfer parameters, including the following:

Whether the transfer should create a new AS/400 file, a new member, replace the information in an existing OS/400 database member, or append the information to an existing member.
The iSeries or AS/400 system name where the data will be transferred to.
Transfer properties, including CCSID conversion settings and connection properties.
The library name, file name, and file member where you want to transfer the data to.
What type of *PUBLIC authority other OS/400 users should have to the data (*ALL, none, read-only, or read/write).
The name of the field description file (*.fdf) you want to use for the transfer.
Whether you want to save the new transfer request as a Data Transfer file, and the name of the file to be saved (this feature allows you to run the request again at a later date without having to re-input the parameters).

Once you run the transfer, the specified spreadsheet rows and columns are transferred to the designated OS/400 file in the field locations you selected.

Microsoft Excel is a valuable tool for manipulating and representing data in a variety of formats. And with the Client Access Excel Add-In, it's easier than ever to pull OS/400 data into your spreadsheets.