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.
Notice what I did not do.
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?