• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    1

    Width

    the
    number of print positions to allow for this column

    12

    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

    no

    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.

    RELATED STORY

    SQL Quickly and Dirtily Extracts a Field from a CSV File

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Connectria:  Need help managing your IBM i? Trust us as an extension of your IT department.
    United Computer Group:  VAULT400 BaaS delivers secure cloud backup and DR solutions
    Cilasoft:  Stay on top of your most difficult IBM i security challenges with our Auditing and Security Suite.

    Vision Touts MIMIX Success Stories IBM Patches Pair of Security Flaws in iAccess for Windows 7.1

    Leave a Reply Cancel reply

Volume 15, Number 25 -- December 1, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
Connectria
United Computer Group, Inc.

Table of Contents

  • End of Year Feedback
  • How Many Interfaces Are Enough (To Print A CSV File)?
  • Detecting A “Job End” Condition in DB2 for i

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Delivers Sizable Benefits, Forrester Consulting Reports
  • SBOMs Will Come to IBM i, Eventually
  • IBM i Backup Provider Storagepipe Snapped Up By Thrive
  • Four Hundred Monitor, June 7
  • IBM i PTF Guide, Volume 25, Number 23
  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle