BASS: Build A Spreadsheet
December 12, 2012 Ted Holt
Note: The code accompanying this article is available for download here. This code was updated on 12/22/14.
Everybody needs a project–something to putter with in order to relieve stress and deal with the vicissitudes of life. I would like to tell you about a project that I’ve been working on for the past few months. I only wrote it for the fun of it, but it has already come in handy.
It seems that not a week goes by without someone asking me to stop providing them a report as a PDF and give it to them in Excel format instead. It would be nice if I could flip a switch and an RPG program would quit building a spool file and build something they could open in Excel, but of course, such a change requires a good deal of reprogramming.
My First Effort
My first effort to address this void was to write a service program that would create an IFS file of data in comma-separated values form. I call the service program Write CSV (WRTCSV). We’ve retrofitted it into several programs in my shop, and it works well.
WRTCSV consists of the following subprocedures:
Changing a program to build a CSV file is not difficult.
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:
Did you notice that BASS uses WRTCSV? No sense re-inventing the wheel, eh?
Not that it matters, but I will also mention that BASSMAIN is built from six modules, each of which has one task. For example, one module handles conditions. Another one handles assignment statements. Modular programming is wonderful.
Here’s a simple BASS script. You could put it into member DSPLIBL in some source physical file member.
# 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
What does this mean?
Here are the commands to make it run. (Fill in the library and source physical file names with appropriate values.)
DSPLIBL OUTPUT(*PRINT) GOBASS FILE(QPRTLIBL) + SPLNBR(*LAST) + SCRIPT(<lib>/<srcpf> DSPLIBL) + STMF(DSLIBL.CSV) DSPF STMF(dsplibl.csv)
If you use this utility, please tell me about your experience with it.
So far BASS seems to be adequate for the job, but I plan to make two more enhancements. (I may think of other enhancements later, but I’ll cross that bridge when I come to it.) I am going to make the enhancements for the same reason I wrote BASS–for the fun of it. If they work out, I’ll tell you about them later.
That’s my project. What’s yours?