How Many Interfaces Are Enough (To Print A CSV File)?
December 1, 2015 Ted Holt
|
Note: The code accompanying this article is available for download here. CSV (comma-separated values) files have been part of my working world for years. I like them because they’re easy to build. Users like them because they can easily open them in Microsoft Excel. I’ve written about this before. Let’s end the year with a utility I wrote to print CSV files. I had fun writing it. Maybe you’ll find it useful in your shop. When I need to read a CSV file on an IBM i system, I typically FTP the file from the IFS to a temporary folder on my PC, where I can open it with Excel, as the users do. Repeating this process gets old quickly. It occurred to me that it would be nice to have a native program that could print CSV files in a columnar format. It’s much easier to read such a report than to try to make sense of jammed-together values. And so in odd moments, I developed the Print CSV File (PRTCSV) command. Does it work? You’d better believe it! PRTCSV has the following parameters:
In its simplest form, PRTCSV can run with only the STMF parameter.
PRTCSV STMF('customer-data.csv')
This gives you a report in default format. All columns are 12 positions wide, all values are left-aligned, etc. Sometimes this is all you need. If you need more horsepower, use the COLDEF parameter, which allows you to specify the following attributes of each column:
You may change some of these defaults by editing the GLOBALS source member. Let’s look at an example. Here’s a CSV file: CUSNUM,LSTNAM,INIT,STREET,CITY,STATE,ZIPCOD,CDTLMT,CHGCOD,BALDUE,CDTDUE 192837,"Lee","F L","5963 Oak St","Hector","NY",14841,700,2,489.50,.50 389572,"Stevens","K L","208 Snow Pass","Denver","CO",80226,400,1,58.75,1.50 392859,"Vine","S S","PO Box 79","Broton","VT",5046,700,1,439.00,.00 397267,"Tyron","W E","13 Myrtle Dr","Hector","NY",14841,1000,1,.00,.00 475938,"Doe","J W","59 Archer Rd","Sutter","CA",95685,700,2,250.00,100.00 583990,"Abraham","M T","392 Mill St","Isle","MN",56342,9999,3,500.00,.00 593029,"Williams","E D","485 SE 2 Ave","Dallas","TX",75218,200,1,25.00,.00 693829,"Thomas","A N","3 Dove Circle","Casper","WY",82609,9999,2,.00,.00 839283,"Jones","B D","21B NW 135 St","Clay","NY",13041,400,1,100.00,.00 846283,"Alison","J S","787 Lake Dr","Isle","MN",56342,5000,3,10.00,.00 938472,"Henning","G K","4859 Elm Ave","Dallas","TX",75217,5000,3,37.00,.00 938485,"Johnson","J A","3 Alpine Way","Helen","GA",30545,9999,2,3987.50,33.50 To build this file, I ran the following command:
CPYTOIMPF FROMFILE(QIWS/QCUSTCDT) +
TOSTMF('customer-data.csv') +
MBROPT(*REPLACE) RCDDLM(*CRLF)+
RMVBLANK(*TRAILING) +
ORDERBY(cusnum) ADDCOLNAM(*SYS)
To build a nicely formatted report requires a command like this one:
PRTCSV STMF('customer-data.csv') +
HEADING('Our Most Important Customers') +
COLDEF((1 *N 7 RIGHT 999999 *N *N *N Account Number)
(2 2 18 *N *N *N *N *N *N Name) +
(3 *N 3) +
(4 4 20 *N *N *N *N *N *N Street) +
(5 *N 15 *N *N *N *N *N *N City) +
(6 *NONE 5 RIGHT *N *N *N *N *N State) +
(7 3 5 *N 99999 *N *N *N ZIP Code) +
(8 *N *N RIGHT 000,009 *N *N *N Credit Limit) +
(9 *N *N *N *N *N *OMIT) +
(10 *N *N RIGHT '000,000.99' *YES *N *N Balance Due) +
(11 *N *N RIGHT '000,000.99' *YES *N *N Credit Due)) +
SKIP(1)
Yes, it’s ugly, but I didn’t have to type it. I used F4 to prompt and I filled in the blanks. The report looks like this: Our Most Important Customers 2015-13-01 22:52:48 Page 1 Account ZIP Credit Balance Credit Number Name Street City State Code Limit Due Due 192837 Lee F L 5963 Oak St Hector NY 14841 700 489.50 .50 389572 Stevens K L 208 Snow Pass Denver CO 80226 400 58.75 1.50 392859 Vine S S PO Box 79 Broton VT 05046 700 439.00 397267 Tyron W E 13 Myrtle Dr Hector NY 14841 1,000 475938 Doe J W 59 Archer Rd Sutter CA 95685 700 250.00 100.00 583990 Abraham M T 392 Mill St Isle MN 56342 9,999 500.00 593029 Williams E D 485 SE 2 Ave Dallas TX 75218 200 25.00 693829 Thomas A N 3 Dove Circle Casper WY 82609 9,999 839283 Jones B D 21B NW 135 St Clay NY 13041 400 100.00 846283 Alison J S 787 Lake Dr Isle MN 56342 5,000 10.00 938472 Henning G K 4859 Elm Ave Dallas TX 75217 5,000 37.00 938485 Johnson J A 3 Alpine Way Helen GA 30545 9,999 3,987.50 33.50 *** End of report *** This functionality was acceptable, but I decided to take it a step further. I reasoned that if I were to store the column definitions in a file, I wouldn’t have to fill in the blanks afresh when I came to work in the morning and resumed work on my project, nor when I needed to print a freshly received version of a regularly received file. I added the DEFF (Definitions file) parameter to the PRTCSV command. The definitions file is also a stream file of free-format text stored in the IFS. It consists of keyword parameters of the format keyword=value. To produce the report given above requires a stream file like this one, which I named customer-data.txt:
# List data from QIWS/QCUSTCDT
# Junior J. Programmer, December 1, 2015
column=1 width=7 align=right editmask=999999
heading2=Account heading3=Number
column=2 gap=2 width=18 heading3=Name
column=3 width=3
column=4 gap=4 width=20 heading3=Street
column=5 width=15 heading3=City
column=6 gap=0 width=5 align=right heading3=State
column=7 gap=3 width=5 editmask=99999 heading2=ZIP heading3=Code
column=8 align=right editmask=000,009 heading2=Credit heading3=Limit
column=9 include=n
column=10 align=right editmask=000,000.99 blankifzero=y
heading2=Balance heading3=Due
column=11 align=right editmask=000,000.99 blankifzero=y
heading2=Credit heading3=Due
Here’s the same command with the column descriptions in the DEFF parameter instead of the COLDEF parameter.
PRTCSV STMF('customer-data.csv')
HEADING('Our Most Important Customers')
DEFF('customer-data.txt')
SKIP(1)
You may use both COLDEF and DEFF parameters if you want. COLDEF settings override those in DEFF. For more details about the DEFF parameter, see the PRTCSV DEFF Instructions document, which is included with the downloadable code. To install PRTCSV, do the following: 1. Download the PRTCSVF.ZIP file that accompanies this article to your PC. 2. Unzip the file into a folder of your choice. 3. Using a binary transfer, FTP the PRTCSVSAVF.SAVF file to your IBM i system. 4. Use the Restore Object (RSTOBJ) command to copy the objects from the save file to a library of your choosing.
RSTOBJ OBJ(*ALL) SAVLIB(THOLT1) DEV(*SAVF) +
SAVF(PRTCSVSAVF) RSTLIB(MYLIB)
5. Modify the defaults in the GLOBALS member of the PRTCSV source physical file. You may want to increase the value of constant InputDim, which tells the maximum number of columns to be read from the CSV file. European readers may want to change the value of the gDecPt constant from a period to a comma. (N.B. I have not tested PRTCSV with that change.) 6. Compile the INSTALL program from the source code in source physical file PRTCSV. 7. Call the INSTALL program, passing two parameters:
CALL INSTALL (MYLIB *ALL) Just a few more comments and I’m done. 1. Numeric editing strings consist of the digits 0 (zero) and 9 and insertion characters. A zero indicates a digit that may be suppressed. A 9 indicates a digit that is not to be suppressed. Insertion characters are non-numeric characters that are to be inserted into the edited string as they are. 2. If one period is found in the edit string, it is assumed to be a decimal point. If more than one period is found, the periods are treated as insertion characters 3. A leading or trailing minus is allowed. 4. PRTCSV prints all octothorpes (#) in a column when high-order truncation of significant (non-zero) digits occurs. 5. The first character of the align value determines alignment.
6. A yes/no value is considered to be “yes” if the value is a single digit 1 (one), begins with the letter y, or begins with *y. Case does not matter. The following are equivalent: blankifzero=1 blankifzero=y blankifzero=*YES blankifzero=YellowSubmarine So there’s another tool for your toolbox. If you find PRTCSV useful, please let me know. Look for more CSV-related articles soon in Four Hundred Guru. RELATED STORY SQL Quickly and Dirtily Extracts a Field from a CSV File
|
