Many end users of iSeries applications need specific data to analyze their business, and they require different views of this data. The development staff often provides these views in the form of reports either by creating a high-level language program (RPG or COBOL) or by writing a query.

iSeries shops have given end users the ability to write their own queries via either the standard OS/400 commands or third-party software products. However, some of the sequences that the users choose can have a detrimental effect on system performance.

Furthermore, one limitation of both the application programs and the query options is that a new program or query must be written for every different view of the data. Wouldn't it be nice if there were a tool that would allow the end user to sequence and group the data without having to write and run individual programs and/or queries? Well, there is! That tool is Microsoft Excel. Once data is transferred into an Excel spreadsheet, sort and hide functions can manipulate that data. Also, transferring the data to a PC eliminates the potential for performance degradation on the iSeries.

Here's how to use the iSeries data transfer function:

1. Open Microsoft Excel.
2. Click on the Data option in the toolbar.
3. Click on the double arrow to expand the option list.
4. Look for Transfer Data from iSeries.

If you see the Transfer Data from iSeries option, the capability to perform data transfer is available. If you don't see the option, confirm that IBM iSeries Access for Windows (V5R1 and later) or Client Access (prior to V5R1) is installed. If it is not installed, install it.

Now, enable the add-in:

1. Click on the Add-Ins option.
2. Scan the Add-Ins list for the iSeries Access Data Transfer option.
3. Click on the box to enable it.

If you don't see the iSeries Access Data Transfer option, click the Browse button. Using the Look in box, navigate to c:/program files/ibm/client access/shared/ and
select file cwbtfxla.xll.

Now, it's time to perform the data transfer:

1. Open Microsoft Excel.
2. Click on the Tools option in the toolbar.
3. Click on the iSeries Access Data Transfer option. If the iSeries Access Data Transfer option is not displayed, click on the double arrow.
4. A Transfer Data window will open.
5. Select Create New and choose whether to include column headings.
6. Click the OK button.
7. Select the iSeries server the data is to be downloaded from and click the Next button.
8. If you know the library/file (member), type it in and click the Next button; otherwise, click the Browse button. If you click the Browse button, a new window to connect to the iSeries may be displayed. If so, type in the user ID and password. A list of libraries in the library list is displayed. Click on the plus sign (+) next to the library where the file exists. The list expands to show all the files in that library. If the file contains members, the list expands to display the list of members. Double-click on the member that is to have the data transferred to the Excel spreadsheet, and click on the OK button. Click the Next button.
9. On the next screen, click the Next button.
10. If this transfer request will be used again, type in a file name and location to where it is to be saved; otherwise, leave the File name box empty.
11. Click the Finish button.

The data transfer function has now populated the Excel spreadsheet with the file records.

There is a limitation of a maximum of 65,535 records that can be transferred from an iSeries physical file to an Excel spreadsheet.

Stay tuned! Future tips will discuss..

• Other file transfer destinations, such as Microsoft Access databases
• Other file types to be transferred, such as message files

Paul Weyer is a consultant with New Resources Consulting. He has worked on the iSeries (AS/400) since 1987. His background includes both hardware configuration/installation and software development. Paul is a subject matter expert (SME) with COMMON in the areas of LPAR, IT Optimization, Performance, Availability/Business Continuity, iSeries Operations, and Managing Beyond Your Server. His language background includes both COBOL and RPG, and he has been using SQL since 1987. Email Paul at This email address is being protected from spambots. You need JavaScript enabled to view it..