Three Easy Methods to Get Data From AS400 to Excel

One of the more commonly asked questions I see in forums and by end users is how do you get your data from the AS400 down to the PC in a text file format or as an Excel based spreadsheet. And fortunately with modern tools and software this is a very simple problem to solve. Now a days there are many different ways to tackle it depending on what exactly your needs are and what skill-set you or your users have.

First up you can roll your own tools. RPG and CL are a couple of the native programming languages for the platform and if you can write RPG and CL programs then you can really automate the whole data download process from the AS400 environment without any additional tools. You simply create a CL program that reads a spool file or an RPG program to query a database table and write that out to another temporary physical file that can be queried.

Manipulating a spool file this way has the advantage of breaking it apart into the appropriate fields from the source data. Once you have this information put together you can simply have the program email it. One very simply method of emailing from the AS400 is the SNDDST command. But SNDDST can be a bit of a pain to get working since it takes some configuring.

From a Windows based PC you have many many options to get at the data. To download a spool file you simply launch Operations Navigator, search through the output queues for the report you want to download and click and drag it to your desktop. The software automatically converts the spool file into plain text format and downloads it to your computer. Operations Navigator will also convert spool files into Adobe pdf format.

From here it’s a very simple and straight forward process to import the plain text file directly into an Excel spreadsheet. You launch Excel and open the new file, excel will recognize the format and prompt you to add the column breaks. This method is so easy and straight forward it can even be done by end users with a little bit of training and over the shoulder help.

Now you can also query your AS400 directly from a Windows client using an ODBC or OLE driver to access the underlying database. This can be done using your favorite programming or scripting language of choice that can access ODBC, which should be just about all of them. Using this method you need to know how to construct an SQL statement that will be used to query the data set you need.

In fact using this method before I have created Perl scripts to query a specific set of information, in this case it was daily invoices using a date parameter. All the data was downloaded and dumped into a newly created spreadsheet and them subsequently emailed to a list of people. The whole thing was then automated every day by adding it into the task scheduler on the client system. And if I recall correctly this whole script was less than fifty lines of Perl code. How is that for quick and easy?

John Andersen is an IT manager and creator of the Power System Jump Start course. Check out his Midrange Jump Start web site for more info on how to quickly and easily manage your AS400, iSeries or System i.

This article was originally published at http://1367.de/2010/10/17/three-easy-methods-to-get-data-from-as400-to-excel/

An alternative solution to this problem can be found at my blog http://as400softwaredownloads.com/