CPYTOIMPF Doesn't Create Column Headers, But You Can

Published: 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