An automated CL method of moving a query from AS/400 to Excel

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT iSeries programmer tips Groovy programming on IBM i EGL Rich UI on IBM i: Do you Dojo? Running PHP open source applications: NOBODY needs authority Programming for the Web on the IBM i, what is possible Using geocoding on AS/400 to enhance your Web presence The best technologies and tools for System i programmers in 2009 Seven IBM i project lessons learned in 2008 Documenting nested program structures on the AS/400 What is an integrated database? Using SQL on System i to create multicolored comment lines Integrated File System (IFS) Transfer files from one environment to another without closing all other AS/400 sessions Securing the integrated file system on IBM System i Generically send a text file from the IFS via FTP Setting ILE and C+++ compilers to runtime Use a virtual directory to move a .bmp file from the IFS to a remote server to run a software package Backing up PDFs within the IFS on AS/400 FTP from AS/400 to PC folder Copying the spool file stream to IFS IFS folder error Accessing IFS files iSeries CL programming Checking in on your IBM i authorization lists Running PHP open source applications: NOBODY needs authority Simplify the process of converting a spool file from iSeries into an Excel spreadsheet CL program for daily backups Changing user password expiration Eight steps for creating program documentation using AS/400 utilities DAYSPAST CLLE program for AS/400: Compares object creation date with today's date Advanced Job Scheduler help How do I retrieve the source for an output queue description to put in to a CL program? Top 10 programmer tips YTD

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

anges 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

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.

Rate this Tip To rate tips, you must be a member of Search400.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.