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

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

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.

Dig Deeper on iSeries Java programming

OpenOffice: What to know before making the transition from Microsoft Office Learn how OpenOffice Write compares to Word, as well as how OpenBase compares to Access. Get advice on how to work with departments in your company to make the switch from Microsoft Office to OpenOffice. Helpful tips are offered, like letting the administrative assistant to the President keep PowerPoint and Word to reduce friction.

An automated CL method of moving a query from AS/400 to Excel If a daily task of your AS/400 is to print a report from a query a CL program can be written to automate the task and make it easy to open the results in Excel. This CL program copies an existing query to QTEMP and runs the query. Then it converts the EBCDIC physical file that was just created in QTEMP to an ASCII comma separated variable (csv) file, and changes the file type to .csv, and stores the file in an existing Integrated File System (IFS) directory. This directory can then be mapped Excel can open the .csv file.

Use Driver Query to resolve device driver problems The Driver Query utility may not be perfect, but it can help to compile a device driver usage history.

Squirrel: The universal SQL client Jim Mason, president of ebt-now, shares his thoughts on the open-source product Squirrel. According to Jim, Squirrel is a great tool for accessing any type of data anywhere using SQL. In this tip, he explains what Squirrel is, the challenges of working with Squirrel and how this product could work as your only SQL database tool.

Connecting WebSphere to a DB2 database One user writes, "How can I connect WebSphere on my iSeries to a DB2 database on an AIX machine? We've tried a number of different methods configuring JDBC drivers, but nothing seems to work." Web development expert Jim Mason offered some advice.

Connect to a DB2/400 database from Unix

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our AS/400 experts

View all AS/400 questions and answers

Please create a username to comment.

Refresh your data center cooling lexicon

Comparing Linux distributions: Red Hat vs. Ubuntu

Get eco-friendly with LEED data center certification

Dig Deeper on iSeries Java programming

Related Q&A from Jim Mason

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.