• 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
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.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

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

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