BASS: Build A Spreadsheet

Changing a program to build a CSV file is not difficult.

• Define variables for a file handle and a status (return) code.
• Change the program to open the CSV file.
• Identify the places where the program writes to a printer file, and where appropriate, issue calls to WriteCSVData. Disable the printer operations, or redirect the report into a garbage output queue.
• Close the CSV file before shutting down the program.

Here’s an example in RPG. Assume the part in black is the original program, which builds a report. The code I added to build the CSV file is in red. (Notice that I did not include error-handling code, but only left comments to show where it goes.)

H option(*srcstmt: *nodebugio)
H dftactgrp(*no) actgrp(*caller)
H bnddir('SOMEBNDDIR')

Fqcustcdt  if   e             disk    usropn
Fqad3330p  o    e             printer usropn

D FileStatus      s             10i 0
D FileHandle      s             10i 0

 /copy prototypes,wrtcsv

 /free
     *inlr = *on;

     open qcustcdt;
     open qad3330p;
     FileStatus = OpenCSVFile ('CustBal.CSV': FileHandle);
     if FileStatus <> *zero;
        // add appropriate error handling
     endif;

     write Hdr;
     FileStatus = WriteCSVData (FileHandle:
                   'Name': 'Account': 'Balance');
     if FileStatus <> *zero;
        // add appropriate error handling
     endif;
     dow '1';
        read cusrec;
        if %eof();
           leave;
        endif;
        write Dtl;
        FileStatus = WriteCSVData (FileHandle:
                      LstNam: %char(CusNum): %editc(BalDue: 'P'));
        if FileStatus <> *zero;
           // add appropriate error handling
        endif;
     enddo;

     close *all;
     FileStatus = CloseCSVFile (FileHandle);
     if FileStatus <> *zero;
        // add appropriate error handling
     endif;

     return;

Notice that the calls to WriteCSVData follow the output operations to the printer file. This isn’t a requirement, but it’s the typical way you would convert the program.

If you want to use WRTCSV, you’re welcome to it. You’ll find the source code and service program object (compiled at V5R4) in the downloadable code.

My Second Effort

WRTCSV was not my piddling project. I wrote WRTCSV on company time because I needed it. But I never stopped thinking that there must be an even better way to build CSV files. I wanted a method that was faster to implement than embedding calls to WRTCSV routines. I also wanted a method that I could use with any report, even those produced by the Famous ERP Package used to run the factory where I work.

I finally decided to create a scripting language that would let me tell the computer how to copy data from spooled files into IFS files. I know there are products on the market that do that already, but I needed something to tinker with, and this seemed like a good challenge.

I call my scripting language BASS. (I pronounce it like a type of fish, not like a low-voiced male singer.) BASS stands for Build A Spreadsheet. Does it build a spreadsheet? No, it builds a CSV file, which users can open into a spreadsheet. But BACSVF was too hard to pronounce.

I began by writing a grammar for my language. You can read the grammar. It’s the Word document in the downloadable files. It took me about a month to define the syntax of the language. (Remember, I only worked on this outside of working hours, and I didn’t work on it every day.) I considered a lot of possibilities, and came up with a very simple language. By keeping it simple, the language would be easier to learn and easier to write an interpreter for than it would have been had I gotten too fancy in my aspirations.

Once the grammar was to my liking, I began the process of programming it. These are the objects that make up the utility:

# Convert output of Display Library List report to CSV

var $HeadersAreComplete = FALSE
var $LibraryType

/ $input == ' ' / exit                    # skip blank lines

# Generate the column headings only once
/ $input (4:10) == Library /  goto 100

# Select libraries
$LibraryType = ''
/ $input (16:3) == SYS /  $LibraryType = System
/ $input (16:3) == PRD /  $LibraryType = Product
/ $input (16:3) == CUR /  $LibraryType = Current
/ $input (16:3) == USR /  $LibraryType = User
/ $LibraryType  <> '' /  goto 200

/ $input (27:1) == * / stop     # End of listing found
exit                            # ignore other lines

100 # column header routine
    / $HeadersAreComplete == TRUE / exit
    $a = 'Library List'
    addrow
    addrow
    $a = Library
    $b = Description
    $c = Type
    addrow
    $HeadersAreComplete = TRUE
    exit

200 # extract data for one library
    $a = $input ( 4: 10)     # library name
    $b = $input (39: 50)     # description
    $c = $LibraryType
    addrow
    exit

DSPLIBL OUTPUT(*PRINT)

GOBASS FILE(QPRTLIBL) +
   SPLNBR(*LAST) +
   SCRIPT(<lib>/<srcpf> DSPLIBL) +
   STMF(DSLIBL.CSV)    

DSPF STMF(dsplibl.csv)