Alan’s Easy Method For Building A CSV File From A Report

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