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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT iSeries programmer tips Enhancing RPG with external SQL stored procedures Tracking data changes on IBM i with triggers Introduction to SQLRPGLE on IBM i: Making a report Implementing a browser interface in COBOL: Displaying database fields Taking advantage of CL advancements, starting with V5R3 TAATOOL: Useful tools for programmers on IBM i Implementing a browser interface in COBOL: Creating your graphic Web page Implementing a browser interface in COBOL: Getting started Making the most of RPG data handling on IBM i Groovy programming on IBM i Integrated File System (IFS) How to view source files in the library User being locked out by Windows share on iSeries Incorrect CCSID on FTP connection from AS/400 to Windows Trouble accessing IFS path from Win2k3 server How to use an API for adding users to EIM SSO implemenation 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 iSeries CL programming Taking advantage of CL advancements, starting with V5R3 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?

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

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

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.