Adding Column Headings to CPYTOIMPF Output Streamfiles

Resolving The Problem

IBM i Development added a new parameter, ADDCOLNAM, to the CPYTOIMPF command in V6R1 and V7R1 via PTF. No PTF is needed for V7R2 and later.

Here is the developerWorks URL describing this enhancement

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/CPYTOIMPF%20and%20CPYFRMIMPF%20commands%20%E2%80%93%20Include%20column%20headings


Copy To Import File (CPYTOIMPF) – a new (optional) parameter indicates whether the column names should be included as the first row in the target file.

ADDCOLNAM - Specifies if the column names should be added to the first record of the to file.

*NONE – (default) don’t include the columns names.
*SQL – The column SQL names will be used to populate the first row.
*SYS – The column System names will be used to populate the first row.


Copy From Import File (CPYFRMIMPF) – a new (optional) parameter indicates whether the first row should be skipped when processing the import file.

RMVCOLNAM - Specifies if the column names should be removed from the from file.

*NO – (default) don’t skip the first row. The copy will begin with the first record from the from file.
*YES – The copy will begin with the second record from th


SF99701 Level 15 & SF99601 Level 27


If you cannot get to to that Database Group PTF level, here is an as-is solution:


The following CL program is an example of how the CPYTOIMPF command can be combined with some other CL commands and QShell utilities to produce a comma separated data file with column headings.

The first step to this process is to create a view over SYSCOLUMNS which will select the column heading that you want to use. There are several options for the column heading that you might want to use including the long SQL column name, the short, or system, SQL column name, the column text, or the column heading. You could even use the comments, if you would like to. Examples of different views that can be created are in the comments at the top of the CL program.

Once you have created the view that you intend to use, the program below shows how to use CPYTOIMPF to copy the column headings into a streamfile and then change that file using QShell utilities to effectively pivot this simple table into one row with multiple columns. Then the data from the file are appended to this streamfile with the headings using the CPYTOIMPF command again. Finally, it cleans up the temporary files, leaving only the desired output file.

Caution: The following code examples are provided on an unsupported, as is basis.

Example
/*************************************************************************************************/
/* */
/* CPYTOIMPF2 - copy to import file with column headings */
/* */
/* In order for this to work you must first create a view over QSYS2/SYSCOLUMNS that will */
/* select the column names, column text, headers, etc. that you want to use for the headings */
/* in the resulting streamfile. Here are several examples: */
/* */
/* -- View with column names (SQL long name) */
/* CREATE VIEW MIKSWENS.CUSTCOLNAM AS */
/* SELECT COLUMN_NAME */
/* FROM QSYS2.SYSCOLUMNS */
/* WHERE TABLE_NAME = 'CUSTBOTH' AND TABLE_SCHEMA = 'MIKSWENS'; */
/* */
/* -- View with column headings */
/* CREATE VIEW MIKSWENS.CUSTCOLHDG AS */
/* SELECT TRIM(SUBSTR(COLUMN_HEADING,1,20)) || ' ' || */
/* TRIM(SUBSTR(COLUMN_HEADING,20,20)) || ' ' || */
/* TRIM(SUBSTR(COLUMN_HEADING,40)) AS COLUMN_HEADING */
/* FROM QSYS2.SYSCOLUMNS */
/* WHERE TABLE_NAME = 'CUSTBOTH' AND TABLE_SCHEMA = 'MIKSWENS'; */
/* */
/* -- View with column text */
/* CREATE VIEW MIKSWENS.CUSTCOLS AS */
/* SELECT COLUMN_TEXT */
/* FROM QSYS2.SYSCOLUMNS */
/* WHERE TABLE_NAME = 'CUSTBOTH' AND TABLE_SCHEMA = 'MIKSWENS'; */
/* */
/* -- View with short SQL NAME */
/* CREATE VIEW MIKSWENS.CUSTSYSCOL AS */
/* SELECT SYSTEM_COLUMN_NAME */
/* FROM QSYS2.SYSCOLUMNS */
/* WHERE TABLE_NAME = 'CUSTBOTH' AND TABLE_SCHEMA = 'MIKSWENS'; */
/* */
/* The rest of the process is copy the selected headings into a new streamfile using */
/* CPYTOIMPF, modifying that file with QSHELL utilities to essentially pivot the rows into */
/* columns and finally append the actual data to the file using CPYTOPCD. */
/* */
/* This program is hard coding the file names and paths but the concept could be extended */
/* using variables to make the program more versatile. */
/* */
/*************************************************************************************************/

PGM

CD DIR('/MIKSWENS')
/* Remove the original file and any old temporary files, monitor for file not found +
errors. */
RMVLNK OBJLNK('/MIKSWENS/CustWithColumnHeadings.csv')
MONMSG MSGID(CPF9999)
RMVLNK '/MIKSWENS/TEMPCUST*'
MONMSG MSGID(CPF9999)

/* Copy the column headings into a streamfile, one column heading per row. */
CPYTOIMPF FROMFILE(MIKSWENS/CUSTCOLHDG) TOSTMF('/MIKSWENS/TEMPCUST1.CSV') +
STMFCCSID(*PCASCII) RCDDLM(*LF)

/* Insert a comma at the end of each line */
QSH CMD('sed ''s/$/,/'' TEMPCUST1.CSV > TEMPCUST2.CSV')
/* Remove the line feeds to concatenate the records into one row. */
QSH CMD('tr ''\n'' '' '' < TEMPCUST2.CSV > TEMPCUST3.CSV')
/* Remove the comma after the last column heading */
QSH CMD('sed ''s/.\{2\}$//'' TEMPCUST3.CSV > TEMPCUST4.CSV')
/* Put a line feed back into the end of the column heading row */
QSH CMD('echo >>TEMPCUST4.CSV')
/* Convert the data back to ANSI */
CPY OBJ(TEMPCUST4.CSV) TOOBJ(TEMPCUST1.CSV) TOCCSID(1252) DTAFMT(*TEXT) +
REPLACE(*YES)
/* Append the data onto the streamfile */
CPYTOIMPF FROMFILE(MIKSWENS/CUSTBOTH) TOSTMF(TEMPCUST1.CSV) MBROPT(*ADD) +
STMFCCSID(1252) RCDDLM(*CRLF)
/* Rename the temporary file to the desired output file name. */
RNM OBJ('/MIKSWENS/TEMPCUST1.CSV') NEWOBJ(CustWithColumnHeadings.csv)
/* Delete the temporary files. */
RMVLNK '/MIKSWENS/TEMPCUST*'
MONMSG MSGID(CPF9999)

ENDPGM

Caution: The views created over the QSYS2.SYSCOLUMN file are user-defined views. Therefore, prior to any release upgrade, the views must be dropped and re-created after the upgrade completes. If the views are not dropped before they could cause unexpected issues during the installation. In case of any recovery of QSYS2, the views should also first be dropped and re-created after the recovery completes.