This tip is an excerpt of the article "Getting data off your iSeries" published in the July/Augustl 2002 edition...
(volume 5, number 4) of the iSeries 400 Experts Journal. Provided courtesy of The 400 Group.
The data transfer component of Client Access has come a long way since the early years of PC Support. First and foremost it is now fronted with a GUI interface (excuse the redundancy, but "fronted with a GUI" just didn't sound right). But much more than that are the great improvements made to the data selection and output format options.
Start a transfer by selecting the Data Transfer from AS400 program in the AS400 Client Access Express program group. Note, this is a separate installable option of Client Access, so if you can't find the Data Transfer from AS400 program in the AS400 Client Access Express program group, you will probably need to install it. Once it is running, you should see a screen like the one in Figure 1. (Note: The System name has already been selected and the File name has already been filled in.)
|Figure 1: The data transfer facility of CA/400.|
|Figure 2: Data selection dialogues|
Data selection is done by way of the Data Options button. Press it and you will get a screen like the one in Figure 2. Here you will find an SQL-like field and data selection facility. If you need assistance on SQL functions or you just want to check the spelling of the field names, press the Details… button on the Change Data Options dialogue and you will get a screen like the one in Figure 2.
|Figure 3: Output format selection|
Note, there is no need to use the SQL Char function to convert numeric data to character format; the transfer function will automatically convert numeric data to the proper format depending on the type of output file you choose (next).
On the lower portion of the main transfer screen in Figure 3, you choose the output. You can run the transfer to the screen to check it, then change the Output device to File. After setting the Output device to File, press the adjacent Details… button. This will bring up a screen like the one shown in Figure 4.
Notice on the File Details dialogue that the File type drop-down list has been pulled down. The list contains over a dozen different file types from which to choose, many of which have further options you can select by pressing the adjacent Details… button. You will then be presented with different options depending on the file type you chose.
Note: A PC file description file (lower portion of File Details screen) is a file you can choose to have built that will contain a coded description of the data types and lengths of the fields of the output file.
Once you have run the transfer and the output has been put in the file of your choice, you can either import (if the exact file type wasn't available) or open it directly (e.g., in Lotus 1-2-3) in your PC application.
|Figure 4: Data Transfer Excel add-in|
Add-ins and plug-ins
Add-ins take the data transfer function to new heights. Conceptually, they are a wonderful hybrid of the transfer facility and the import/export facility. To date, add-ins are available only for the Microsoft Excel spreadsheet program, but you could make your own for any PC app that features a macro or scripting language.
Client Access Express provides two Microsoft Excel add-ins: one for downloading and one for uploading. We'll concentrate our efforts on the former. The Excel add-in allows you to run download transfer requests from the 32-bit versions of Microsoft Excel (Excel 95 and Excel 97). If Excel was installed on your PC when you installed CA Express, the typical installation option will automatically install the Excel add-in. Otherwise, you will have to install the add-in manually, which you can do anytime after installing CA Express (just do a selective install).
Once installed, get into Excel and point your cursor in a cell (e.g., A1 or the top-left cell). Then, pull down the Data menu and you should see the two add-ins at the bottom (if you don't, see the next section). Select Transfer Data From AS/400… and the initial transfer request screen will appear. You have the option of using a previously-saved transfer request or creating a new one.
For example, I created one named "custmast.dtf" which I have specified on the sample screen in Figure 4. If you choose to create a new one, a wizard will guide you through the process that will look very similar to the data transfer request facility discussed earlier. If you create a new one, remember to give it a meaningful name so you can reuse it later.
After pressing OK, your spreadsheet will start filling up with data, perfectly converted and formatted. Works like a charm!
Note: Saved transfer requests (.dtf) created in the Excel add-in are compatible with the CA Express standalone transfer request facility discussed earlier, and vice versa. Try opening a request you created using the Excel add-in in the standalone facility and see what it looks like.
What to do if the add-ins don't appear on your Excel Data menu
If both Excel and CA Express have been installed successfully on your PC and you still don't see the Transfer Data From AS/400… and the Transfer Data To AS/400… add-in on your Excel Data menu, first use CA Express' selective setup to try to get them installed properly. If that still doesn't work, try these steps:
- Start Excel.
- Pull down the Tools menu.
- Select the Add-Ins… option.
- If you see the Client Access Data Transfer entry listed in the list of Add-Ins available, make sure it is checked. If you don't see the Client Access Data Transfer entry, then press the Browse… button and continue.
- Navigate to the C:Program FilesIBMClient AccessShared directory (or the Shared subdirectory of whatever directory in which you installed CA Express).
- Select the cwbtfxla.xll file and press OK. Then follow the directions.
About the author: Ron Turull is the editor of Inside Version 5, a newsletter that provides expert advice and practical tips for OS/400 users.