how to export data from sql query to excel? as400 iSeries

sapbucket used Ask the Experts™

  I would like to know how to export data returned from a sql query to something that I can work with, such as a text file or anything I can bring over to my windows environment.

Steps to reproduce:
1.) at command line type "strsql" + enter
2.) at "Enter SQL Statements" screen type "select * from <library>/<datafile>" + enter  (you pick the library and datafile)
3.) A "display data" screen will appear showing rows and columns filled with data.

To have an option available to save the data to a human-usable file (such as a text file).

The screen shown titled "Display Data" displays the data but does not provide an option to output the data to a human-usable file.

Can someone please let me know how to work with sql results? I'm open minded in terms of how it gets done; ultimately I would like to output the results to excel by any means necessary.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
We commonly setup SQL queries in Excel using Microsoft Query with an ODBC connection to the iSeries.    MS Query isn't a great tool for building queries but it does have an SQL button so you can enter SQL statements directly.

In Excel 2003 and older, select Data from the menu bar, then Get External Data, then New Database Query
In Excel 2007 select Data ribbon, then select from other sources, then From Microsoft Query
Dave FordSoftware Developer / Database Administrator


Depending on the situation, I use a couple different methods to accomplish this.

The easest way is to run the query from the GUI "Run SQL Scripts" window. Then, you can simply cut and paste into Excel. In fact, in 6.1 version of the GUI, you can save your result-window to a CSV.

Alternately, if there's A LOT of data, I use MS Access as an intermediary. I can set up "linked tables" to point at the '400 data, aand I write a query to insert into an Access table. Then, exporting from Access to Excel is trivial.

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Gary PattersonVP Technology / Senior Consultant

I like to create queries in the green-screen STRSQL utility.  It has nice prompt support, it very fast, and it saves my sessions so I can pull up my recent wueries - even weeks later.

If you are just looking for en easy interactive way, you can just use F13 from STRSQL, select option 1, and set your SELECT output to "3" for a database file.  then use a Client Access File Transfer to download the output file to your PC as BIFF (BIFF is excel format) with an XLS extension.

If you are looking to automate a routine task, then that is a different story.  If that is the case, post back.  For example, do you need to create an AS/400-side process to automatically create Excel spreadsheets, or can it be a macro in Excel, or a java program, or VB/VBScript/VB.NET/C#.NET, etc.?

There are many,many different ways to make this happen, and the "best way" depends on a lot of factors.  Tell us a little more about your functional requirements and we cna provide better advice.

- Gary Patterson
Other alternatives include the STRQM (Query Manager) which allows you to preview the results and then you can select whether to output to a file or not.
Once the query is written you can save and re-run at a later date (including prompt functions to allow you to save a file or not).

Personally I use the Client Access ODBC conector with Excel to retrieve the files from the iSeries.  It saves having to mess about with the Client Access File Transfer program.  The ODBC connector can be installed from the Client Access CD if it is not already installed on your PC.

IBMs ODBC configuration :

The beauty of using the Excel query with the ODBC is that if the data changes on your file on the iSeries you can just refresh the sheet in Excel and you get the latest data.  Makes it easy to record a macro that can run every time the sheet is opened.



Very nice...worked perfect


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial