Can you offer steps or a document with instructions for how to access AS/400 data using Excel ODBC drivers, and download data to Excel?
I used the old Client Access ODBC data drivers to create a data source following IBM's documentation and tested it successfully from Excel.
I also wrote a tip for Search400.com on using Java JDBC to directly access AS/400 data using the free IBM JDBC driver in the Java toolkit (JT400.jar) that is available on every AS/400. How to use Java to integrate Excel worksheets.
Finally, you can use built-in commands to move data from an AS/400 file as ascii text csv file to Windows via an IFS shared folder from your pc. The commands are:
- CPYTOIMPF (copy table or file data to a flat ascii csv delimited file in an IFS folder)
- Use Windows Explorer to map a network drive to an IFS folder and copy the CSV file or use it in Excel.
If you were interested in a good, powerful solution, you should look at Groovy. It beats all the industry options for integrating anything I've seen. You can directly access Excel content from a simple Groovy script as well as reading AS/400 data via the JDBC driver.
This was first published in May 2008