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.
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) +
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].