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?
What you need is an easy way to stuff column headings into the stream file before you add the data. Here’s one method:
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.