By: John Andersen
One of the most common questions I get is how do I get the data from my AS/400 or iSeries onto my computer or Excel spreadsheet program so I can manipulate it using PC programs and spreadsheets. Now back in the old days this used to be a daunting task, but now in modern times with database tools and ODBC drivers that come bundled with your system downloading any data you need over to a PC can be done in a snap.
The first tool for making your data transfer life easier is the Excel Add-in that is included with your Client Access software licenses, and most systems come with Client Access. The Excel add-in tool allows you to query, sort, order and more data requests to your system using SQL like syntax and dump those results directly into your beloved Excel spreadsheet without any hassle. If you have Excel downloads you routinely use you can even save the configuration for later use.
Along the same lines as the Excel add-in is the Data Transfer portion of the 5250 emulator program included with Client Access. This program functions identically to the AS/400 Excel Add-in except that it returns the query results to a plain text data file instead of a spreadsheet.
Now if you are building an MS Access database program or coding another Windows based program that can’t use a download but will need access to your AS/400 data in real time there is an available ODBC driver that comes with your Client Access licenses, or if you are using Java you can use JDBC.
An older set of commands available from within the AS/400 allow you to copy entire physical files and tables over to the Integrated File System where you can then access and download the file using Operations Navigator or through a network share. To use this method you simply issue the one of the commands Copy to PC Document- CPYTOPCD or Copy to Import File with the command CPYTOIMPF. For either of these commands you simply specify the IFS path and file name in the stream file parameter field. It should be noted that both of these options copy the physical file over verbatim, you can’t use constraints.
Getting network shares working takes a little effort and configuration but once complete the AS/400 acts just like another file server on your Windows network so you can retrieve files. So after using the Copy to PC Document or Copy to Import File programs you simply download the file to your PC like you would from another Windows file server.
Finally you may need to copy over your printable reports and spool files to a computer and fortunately Operations Navigator has you covered as well. Simply open up operations navigator, click on basic operations then click on printer output to display a list of spool files you can download to your computer. To download you can simply click and drag the file to your desktop and it will automatically be converted to a plain text document or if you right click on the document there is an option to convert it to an Adobe pdf document.
