Accessing AS/400 data using Excel ODBC drivers

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.