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.

A popular topic for questions to our experts relates to moving query and database information into Excel files. Without using Client Access this task seems to elude the abilities of some AS/400 users.

Recently Leah Rosin of Search400.com spoke with Raymond Johnson, owner of iSolutions Consulting Inc., about what his clients ask him for help with most often. She shared this example of what Search400 readers ask about, and Ray told her that he had created a CL program to accomplish this exact task elegantly for a client. Ray shared that he tells his clients if they have a have a chore that needs to be accomplished every day and they can show him how the task is performed, he can automate it. His mantra is "You have the power i. Make the power i work for you … not the other way around." One example of this is provided below.

This solution works for a scenario in which a user has a query that prints a report. However, the user would like to manipulate the output in an Excel spreadsheet. My solution is a CL program that copies the existing query to the library QTEMP and runs the query as is. The output of the copied query will always be a physical file in QTEMP. This program then converts the EBCDIC physical file that was just created in library QTEMP to an ASCII comma separated variable (csv) file, changes the file type to .csv, and then stores the file in an existing Integrated File System (IFS) directory. The end user can simply map a network drive to the appropriate IFS directory, point Excel to the mapped drive and open the .csv file in Excel.

In my example, I created an arbitrary directory on my 9406-170 AS/400 named '/xls'. In that directory there are three subdirectories, '/xls/ap', '/xls/ar', and '/xls/gl'. These subdirectories store the .csv files created from queries in the libraries QRYLIB1, QRYLIB2 and QRYLIB3 respectively.

I also created a command to call the program to make the program easier to use. The command also provides the opportunity to limit the user to specific libraries and/or queries. In my example I limited the user to three libraries, QRYLIB1, QRYLIB2 and QRYLIB3.

CL command
 

CVTQRY:
CMD      PROMPT('Convert query output to .csv.')
PARM     KWD(QRY) TYPE(*CHAR) LEN(10) MIN(1) +

PROMPT('Name of the query to copy.')
PARM     KWD(LIB) TYPE(*CHAR) LEN(10) RSTD(*YES) +

VALUES(QRYLIB1 QRYLIB2 QRYLIB3) MIN(1) +
PROMPT('QUERY LIBRARY.')

CL program
Click here for a PDF of CL program example.

ABOUT THE AUTHOR: Raymond G. Johnson is owner of and consulant at iSolutions Consulting Inc. in Eugene, Ore. He has over 30 years of experience with IBM hardware and software systems and provides technical support for i5, System i, iSeries and/or AS/400. If you have a solution that you have implemented for this or other common needs in your AS/400 shop, consider submitting a tip to editor@search400.com.
 

This was first published in September 2008

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close