• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Alan’s Easy Method For Building A CSV File From A Report

    November 20, 2013 Ted Holt

    I love simple solutions to common problems, and boy, have I got a good one for you today! Clever and faithful reader Alan Urtubia recently told me how he turned a report into a CSV file and I was impressed. Alan kindly allowed me to pass this tip along to you.

    Alan needed to create a CSV file from a report without modifying the RPG program. He found a method to do so without modifying or recompiling any of the original source code.

    To illustrate his technique, let’s begin with a simple report. Here’s the DDS for printer file QAD001P:

    A                                      REF(CUSTOMERS)
    
    A          R HEADER                    SKIPB(1)
    A                                     1'One Spiffy Customer Report'
    A                                    61DATE EDTCDE(Y)
    A                                     1'Name'    UNDERLINE SPACEB(2)
    A                                    36'Account' UNDERLINE
    A                                    45'City'    UNDERLINE
    A                                    62'Balance' UNDERLINE
    
    A          R DETAIL                    SPACEB(1)
    A            NAME          35         1
    A            CUSNUM    R             +2
    A            CITY      R             +2
    A            STATE     R             +2
    A            BALANCE   R             +2EDTCDE(J)
    
    A          R TOTAL                     SPACEB(2)
    A                                     1'** End of report **'
    A                                    +9'Customers listed:'
    A            CUSTCOUNT      3S 0     +1EDTCDE(1)
    

    Here’s RPG program QAD001R, which builds the report.

    Fcustomers if   e             disk
    Fqad001p   o    e             printer
    
     /free
         *inlr = *on;
         write header;
         dow '1';
            read customer;
            if %eof();
               leave;
            endif;
            CustCount += 1;
            if (FirstName <> *blanks);
               Name = %trimr(FirstName) + ' ' +
                         LastName;
            else;
               Name = LastName;
            endif;
            write Detail;
         enddo;
         write total;
         return;
     /end-free
    

    And here’s the report.

    One Spiffy Customer Report                                  11/09/13
    
    Name                               Account  City             Balance
    Bobby Soxx                           10100  New Yolk      CA  200.00
    Bella De Ball                        10200  Lost Angeles  MS   20.00-
    Patty Kayke                          10300  Ft. Lobotomy  MA     .00
    Lester "Stinky" Wang-Gonzalez        10320  Pong City     FL  225.00
    Jones, Jones & Jones, PA             10340  O'Neil Acres  AL  455.00-
    
    ** End of report **         Customers listed:   5
    

    The first step is to copy the printer file DDS to another source member, which I’ll call QAD001CSVP. Now I can make some changes without bothering the original source code.

    A                                      REF(CUSTOMERS)
    
    A          R HEADER                    SKIPB(1)
    A                                     1'Name'              SPACEB(2)
    A                                    +0','
    A                                    +0'Account'
    A                                    +0','
    A                                    +0'City'
    A                                    +0','
    A                                    +0'State'
    A                                    +0','
    A                                    +0'Balance'
    
    A          R DETAIL                    SPACEB(1)
    A                                     1'"'
    A            NAME          35        +0
    A                                    +0'",'
    A            CUSNUM    R             +0
    A                                    +0','
    A            CITY      R             +0
    A                                    +0','
    A            STATE     R             +0
    A                                    +0','
    A                                    +0','
    A            BALANCE   R             +0EDTCDE(P)
    
    A          R TOTAL                     SPACEB(2)
    A                                     1'Customers listed:,'
    A            CUSTCOUNT      3S 0     +0EDTCDE(1)
    

    Notice what I did.

    1. I added literal commas between the fields.
    2. I deleted literals that I did not want to include in the CSV file.
    3. I added a heading for the state column.
    4. I changed spacing between columns to +0 to keep from pushing the record formats beyond the line length specified in the printer file.
    5. I changed the edit code for the balance field from J to P in order to remove commas and to move the minus sign from the rear to the front. Some programs understand a trailing minus sign, but some don’t.
    6. I removed the UNDERLINE keyword, which can cause problems when copying a spooled file to a database file or IFS file.

    Notice what I did not do.

    1. I did not add or delete any record formats.
    2. I did not change the list of fields in any format.

    This meant that at run time, I was able to override the printer file to the modified version without causing a level check.

    ovrprtf file(qad001p) tofile(qad001csvp)
    call qad001r
    

    Did it work? Here’s the comma-separated spooled file.

    Name,Account,City,State,Balance
    Bobby Soxx                         ,10100,New Yolk    ,CA,   200.00
    Bella De Ball                      ,10200,Lost Angeles,MS,   -20.00
    Patty Kayke                        ,10300,Ft. Lobotomy,MA,      .00
    Lester "Stinky" Wang-Gonzalez      ,10320,Pong City   ,FL,  2225.00
    Jones, Jones & Jones, PA           ,10340,O'Neil Acres,AL,  -455.00
    Customers listed:,  5
    

    Alan copied the spooled file to the IFS, from which the users could access it.

    Is this method perfect? No. It’s no good for program-described printer files, of course. And if a report contains commas and/or quotation marks, the program that opens the CSV file may get a bit confused. In my example, the name of the last customer listed has two commas, and the name of the next-to-the-last customer has two quotation marks. Fortunately, many reports do not have quotation marks, and the only commas are in numeric fields, and those can be eliminated by changing the edit code. I had better luck when I surrounded the name field with quotation marks, like this:

    A          R DETAIL                    SPACEB(1)
    A                                     1'"'
    A            NAME          35        +0
    A                                    +0'",'
    

    Your kilometrage may vary. See Mike Sansoterra’s article, A Few Excel Export to CSV Tips, for more points to consider.

    Thanks and a tip of the hat to Alan Urtubia. What he did is so simple. Why didn’t I think of that?

    RELATED STORIES

    A Few Excel Export to CSV Tips

    BASS: Build A Spreadsheet

    Yet Another Way to Build CSV Files



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Two Steps Forward, No Steps Back

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    LEARN MORE

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Abacus Solutions:  IBM i Performance Assessment. Upgrade with Confidence!
    Shield Advanced Solutions:  HA4i ~ High Availablity for the IBM i. FREE 30-day trial
    ASNA:  RPG Goes Mobile! Free Webcast! Thursday, November 21.

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    Spinnaker Says Third-Party Support Biz Growing at a 30 Percent Clip Open Source Is Here To Stay On IBM i

    Leave a Reply Cancel reply

Volume 13, Number 22 -- November 20, 2013
THIS ISSUE SPONSORED BY:

WorksRight Software
ProData Computer Services
ASNA

Table of Contents

  • Allow Repeated Change With SQL Triggers
  • Alan’s Easy Method For Building A CSV File From A Report
  • Minimizing IPL Surprises With The IPL Startup Kit

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle