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?
Questions and Answers
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,...
When it comes to reporting tools for the AS/400, iSeries and IBM i platform there are many products to pick from. In fact a whole cottage industry has sprang up over the years with a myriad of different data tools for reviewing and extracting data from the OS/400 integrated DB2...
An in depth look at the new MMORPG from Blizzard Entertainment known as Diablo III, a long awaited release for Diablo fans.
Most small businesses take advantage of computer technology, and it certainly can make things a lot easier for everyone within the business when you do so properly. Unfortunately, there are also a number of different problems that can occur as a result of the technology that you are using.
The remote desktop software is quite an amazing type of software with plenty of features that can come in handy at work or even at home. The feature lets you obtain remote access to an assortment of other computers, directly from the computer that you are using at the moment. This software is often used by people who work in an office or business setting.
Remote desktop software is a type of software that allows users to get access to a remote computer. Basically, if you have this software, you will have the ability to view the remote computer and also control it with the mouse you are using. A number of these software programs will also enable you to send documents to and from one computer to another.
The second selection of pc ink cartridges is the compatible low-priced cartridges.
When it comes to reporting tools for the AS/400, iSeries and IBM i platform there are many products to pick from. In fact a whole cottage industry has sprang up over the years with a myriad of different data tools for reviewing and extracting data from the OS/400 integrated DB2...
Whenever I start working on an AS/400, iSeries or Power System box at a new company there are a few key areas I like to be sure of. These areas include ensuring basic system level security, job configuration and process management. Let's take a fast look at how you can...
One of the nice features of the AS/400 and iSeries platform is it's reliability and administrative requirements. But even in light of this as a system administrator you should also be looking and monitoring key data elements to create a baseline of performance metrics. This data can then be used...
Having worked on and administered IBMs midrange AS/400, iSeries and System i platforms for the better part of ten years I have found that you can boil down the essential administrative functions to a few select commands available from one primary command. Let me explain. One of the major functions of...
Discuss this Article
