• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • CPYTOIMPF Doesn’t Create Column Headers, But You Can

    February 17, 2010 Hey, Ted

    If I use Copy to Import File to build a CSV file in the IFS from a database file, I get data, but no column headings. Lack of column headings confuses the poor soul that opens the file in Excel. Do you have an easy yet effective way to address this omission?

    –Robert

    What you need is an easy way to stuff column headings into the stream file before you add the data. Here’s one method:

    • Create a one-record table.
    • Use a file editor–even DFU will suffice–to key in the quote-delimited, comma-separated headings.
    • Use CPYTOIMPF, specifying MBROPT(*REPLACE), to copy the one-row table to a stream file in the IFS.
    • Use CPYTOIMPF, specifying MBROPT(*ADD), to copy the database file to the stream file.

    That process works fine for one-shot interactive applications. If you want to automate the process, use SQL to load the one-record file with the column headings.

    In the following example, which would be embedded in a high-level language program, I create two temporary files, cleverly named WORK1 and WORK2. I use SQL against my favorite one-row table, QSQPTABL, to load the headings into WORK1. Next I use SQL to create WORK2 to hold the data.

    exec sql
       drop table qtemp/Work1;
    exec sql
       create table qtemp/Work1 as
         (select 'Order' as col01,
                 'Item'  as col02,
                 'Qty'   as col03,
                 'Cost'  as col04,
                 'Price' as col05
            from qsqptabl)
       with data;
    // check SQLSTATUS here and act accordingly
    
    exec sql
       drop table qtemp/Work2;
    exec sql
       create table qtemp/Work2 as
       (select cast (it.order as char(6) ccsid 37) as order,
               it.item,
               cast (it.qty as varchar(12) ccsid 37) as qty,
               cast (it.unitcost as varchar(12) ccsid 37) as cost,
               cast (it.unitprice as varchar(12) ccsid 37) as price
         from itemdata as it
         order by 1)
       with data;
    // check SQLSTATUS here and act accordingly
    

    In my example, I select data from one file only, but this is only an example. More realistic would be to pull data from two or more files, select records, etc.

    Once the two work files are ready, I use CPYTOIMPF to load stream file WORK.CSV.

    cpytoimpf fromfile(qtemp/work1) tostmf('/home/mydir/work.csv')
             mbropt(*replace) stmfcodpag(*stdascii) rcddlm(*crlf)
    cpytoimpf fromfile(qtemp/work2) tostmf('/home/mydir/work.csv')
             mbropt(*add) stmfcodpag(*stdascii) rcddlm(*crlf)     
    

    Does it work? Judge for yourself.

    "Order","Item","Qty","Cost","Price"
    "299303","AB-101","5","1.00","2.50"
    "774398","DF-820","1","7.50","7.00"
    "993829","BC-202","6","10.00","20.00"
    

    All that’s lacking is to tell the system to email the stream file to whomever.

    This is not the only way to create a CSV file with headers, but it’s an easy and effective way. And that’s what you asked for.

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Manta Technologies

    The Leader in IBM i Education!
    Need training on anything i?
    Manta is all you need.

    130 courses and competency exams on:
    · IBM i operations
    · System Management and Security
    · IBM i Programming Tools
    · Programming in RPG, COBOL, CL, Java
    · Web Development

    SQL, DB2, QueryProduct features:
    · Runs in every popular browser
    · Available 24/7/365
    · Free Student Reference Guides
    · Free Student Administration
    · Concurrent User License
    · Built-In IBM i Simulator

    You can download our 200-page catalog and take sample sessions at MantaTech.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    MaxAva:  Get a complimentary continuity assessment for *noMAX - Premium HA & DR
    ProData Computer Services:  Save the day with RDR and ProData utilities!
    COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    InfoPrint Reaches Out to Resellers The Power7 Systems Sales Pitch

    Leave a Reply Cancel reply

Volume 10, Number 6 -- February 17, 2010
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
System i Developer

Table of Contents

  • Handling Constraint Violations in RPG
  • CPYTOIMPF Doesn’t Create Column Headers, But You Can
  • Admin Alert: Six Things Power i Administrators Need to Know

Content archive

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

Recent Posts

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

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 © 2025 IT Jungle