Problem solve Get help with specific problems with your technologies, process and projects.

Accessing AS/400 data using Excel ODBC drivers

There are a couple of options for accessing AS/400 data, from the old way using Client Access ODBC drivers, to Java, to a new application from Groovy.

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:

  1. CPYTOIMPF (copy table or file data to a flat ascii csv delimited file in an IFS folder)
  2. 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.

Dig Deeper on iSeries Java programming