Ask the Expert

Accessing AS/400 data using Excel ODBC drivers

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 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.

This was first published in May 2008

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: