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:

Parameter

Description

STMF

the
name of a stream file that contains CSV data

HEADING

the
text to be printed at the top of each page of the report

DEFF

the
name of a stream file that contains columnar definitions

COLDEF

a
definition (list of attributes) for each printed column

SKIP

the
number of lines at the beginning of the CSV file that should be ignored

STRDLM

the
character that surrounds string values

FLDDLM

the
character that separates the fields

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:

Parameter

Description

Default

Column
number

the
column to which the other attributes apply

Gap
before column

the
number of spaces to leave before this column

Width

the
number of print positions to allow for this column

Alignment

the
way the column is to be aligned–left, right or centered

left

Editing
mask

a
string of characters that tells how a numeric column should be formatted

Blank
if zero

whether
a zero value should print as spaces

Print
this column

whether
this column should be included in or omitted from the report

yes

Column
headings 1, 2 and 3

the
headings to print above 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:

the name of the library in which to install the utility;
a valid value of the SRCDBG parameter found in the ILE CRT commands.

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.

L, C, R = left, center, right
Anything that is not C or R is treated as L
Case does not matter

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.

SQL Quickly and Dirtily Extracts a Field from a CSV File

Table of Contents

Recent Posts

Subscribe

Pages

Search