What The Software Vendors Don’t Want You To Know…
Everyone wants to get there data into Excel or spreadsheet of choice to slice dice and do whatever else you need to in a user friendly, powerful program tailor made for data manipulation and reporting.
Getting data from your AS/400 to Excel used to be a major pain but now its simple and routine process depending on what your reporting needs are. Simply replicating a table or pulling in a printed report can be done but if you need more complex functions or selecting slices of data from a large table you will want to get some third party software which will make this process easy.
Without the use of expensive third party software there are essentially two ways to pull data from an AS/400 over into your Excel spreadsheets.
The first method utilizes the data connection wizard built into Excel and an ODBC driver to query the database tables. Yes, Excel has a feature built right into it for querying databases (not just your AS/400) and storing the resulting data sets in your spreadsheets!
The AS/400 and iSeries ODBC driver comes with Client Access and is available as an option when you install Client Access on your PC. Alternatively there are some other vendors out there that sell ODBC or OLE drivers for connecting to your AS/400.
Utilizing the AS/400 ODBC driver you then use the Excel Data Wizard to open a connection to your system and pull down data. Alternatively you can use an Access database and create linked tables (again utilizing the ODBC driver) and then query and report against them.
After using Excels data connection wizard (as shown above) you simply just import the AS/400 data into your desired spreadsheet page using the data import option which will then connect to your AS/400 ODBC data connection and pull down the data tables.
The default for this type of connection is to download the _entire_ database table… unless you want this there are some additional options you can use to add query constraints to pare down the data selection.
The second method is to take an existing spool file report, download it as a text file using iSeries Navigator and then importing it right into Excel. Excel handles this perfectly except you have to add in the column breaks and then cleanup the page headings.
When using the Excel Text Import wizard to import AS/400 data contained in spool files make sure you select fixed width and not delimited field data. The alignment of the columns may take a little adjusting.