Using the Microsoft Excel Data Transfer-to-iSeries Server Add-In

Technote (troubleshooting)


Problem(Abstract)

This document provides step-by-step instructions for using the Microsoft Excel Data Transfer to iSeries Server add-in.

Resolving the problem

Starting with Client Access Express (V5R1), a Data Transfer Add-in upload function for use with Microsoft Excel was added. This document provides step-by-step instructions for using the Data Transfer to iSeries Add-In.

The add-in can create a new transfer request, or it can run a transfer request that was previously saved from running a request using the data transfer add-in. A saved data transfer request from the iSeries Access Data Transfer to System i system GUI interface can also be used. For the purposes of this document, a new transfer request and a new IBM System i database file is created. The database file is created based on the data in the spreadsheet.

Step 1: Open Microsoft Excel, and display the worksheet that is to be transferred. Select the data that will be transferred to the IBM System i system. Then, select the Data Transfer to iSeries icon from the toolbar as shown below. This is a Excel 2003 screen shot. If using Excel 2007/2010 the icon should be on the Add-ins tab. This opens a new Transfer to iSeries Request window.

This screenshot shows the initial display when implementing  the Excel data transfer to System i add-in.

Note: The Transfer to iSeries Request window will show the range of cells that were selected before clicking on the Data Transfer to iSeries icon.
The First row is column heading(s) option can also be selected if there are column headings in the Excel spreadsheet being uploaded.

Step 2: Click OK, and a Welcome screen is shown (this document does not include an image of the Welcome screen). Click Next on the Welcome screen. Select the option Create new file and member based on spreadsheet from the Storing Data on the iSeries screen.

This picture shows the options that are available when using the Data Transfer to iSeries add-in.

Step 3: Click Next. If this is the first time the transfer request is run, a FDF file does not exist. Therefore, type the name of the FDF file to be created. If desired, the path can be specified and the FDF name can be a name of your choice with a .fdf extension.

On this screen, a .fdf file name must be entered.

Step 4: Click Next. Click OK on the resulting screen that states that the File Description File (FDF) was not found in the path specified (this document does not include an image of this screen). The Scan Spreadsheet screen will appear. Click Start Scan.

This picture shows the Excel spreadsheet scan function.

Step 5: Click Next when the Scan completed successfully message is displayed. The Contents of Spreadsheet screen is shown. This screen shows a list of fields that the scan identified in the spreadsheet. It also shows the file type and file length of each field. The information in the scan is the 'best guess' that the scan could make based on the information in the spreadsheet. If the scan guesses incorrectly, highlight the field to change, and click on Details. This document does not include information on changing the Details of a field. Click on Details, and review the available options for changes that can be made in the currently displayed fields.

This picture shows the results of the Excel spreadsheet scan.

Step 6: If no changes are required for the displayed fields, click Next. Select the iSeries server that the data is to be transferred to on the System Name screen.

This screen is used to select the desired System i.

Step 7: Click Next. Click Properties, and review the list of customizable properties (Options, Date/Time, and Connection properties can be set here).

This screen shows they options that are available when selecting the data transfer Properties.

Step 8: If no properties must be customized, click Next from the Customize Data Transfer screen. On the iSeries Library and File screen, type in the name of the library that the new database file is to be created in and the name of the file to be created.

From this window, enter the desired library and the name of the file you want to create on the System i.

Step 9: Click Next. Type the text description for the iSeries family database file on the File Description Text screen (this document does not include an image of this screen). The text description is optional. Click Next.

This screen is a summary of entries that were made  previously in the data transfer wizard.

Step 10: Review the information on the Confirm Create Options screen. Click Back to go back and make changes if the displayed information is not correct. Click Next if everything is correct. Fill in the information on the Signon to iSeries screen if it is displayed. Click OK.

Note: Depending on how the connection for this IBM i system is configured, this screen may or may not be displayed.

This screenshot is of the System i prompt.

Step 11: The database file will be created on the iSeries family, and the FDF file will be created on the PC. The Save request to file screen is displayed. If this is the only time this Data Transfer must be run, leave the File Name field blank. If the Data Transfer is to be saved and reused later, give the transfer a file name.

On this screen you have the option to save the data transfer request.

Step 12: Click Finish. The data transfer will complete. There will not be a record count or completion message.

For step-by-step instructions for using the Microsoft Excel Data Transfer from the IBM iSeries Server add-in, refer to Rochester Support Center Technote, Using the Microsoft Excel Data Transfer from IBM iSeries Server Add-In. To link to document immediately, click here .

Historical Number

340474891

Document information


More support for:

IBM i
Data Access

Software version:

5.1.0, 5.2.0, 5.3.0, 5.4.0, 6.1.0, 7.1.0

Operating system(s):

IBM i

Reference #:

N1019202

Modified date:

2012-10-08

Translate my page

Content navigation