Browse Definitions :

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

CMD      PROMPT('Convert query output to .csv.')

PROMPT('Name of the query to copy.')


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 [email protected].

Dig Deeper on Business Software

  • OPSEC (operations security)

    OPSEC (operations security) is a security and risk management process and strategy that classifies information, then determines ...

  • smart contract

    A smart contract is a decentralized application that executes business logic in response to events.

  • compliance risk

    Compliance risk is an organization's potential exposure to legal penalties, financial forfeiture and material loss, resulting ...

  • What is risk mitigation?

    Risk mitigation is a strategy to prepare for and lessen the effects of threats faced by a business.

  • change control

    Change control is a systematic approach to managing all changes made to a product or system.

  • disaster recovery (DR)

    Disaster recovery (DR) is an organization's ability to respond to and recover from an event that affects business operations.

  • VRAM (video RAM)

    VRAM (video RAM) refers to any type of random access memory (RAM) specifically used to store image data for a computer display.

  • PCIe SSD (PCIe solid-state drive)

    A PCIe SSD (PCIe solid-state drive) is a high-speed expansion card that attaches a computer to its peripherals.

  • virtual memory

    Virtual memory is a memory management technique where secondary memory can be used as if it were a part of the main memory.