• 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
    Racksquared

    It’s time for IBM Power in the Cloud!

    Stop buying hardware and make the move to the cloud. It’s easier and more cost effective than you might think.

    • IBM Power in the Cloud
    • IBM Power Backup Solutions
    • IBM Power High Availability and DR solutions
    • IBM Power Colocation with Management and Monitoring

    Let’s talk about your business needs.

    Call: 855-380-7225
    Email: Sales@racksquared.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

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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