This command will copy any DB2/400 file into a CSV file in the IFS and, optionally, insert a column heading row based on the file/field descriptions of the source file. As a prerequisite you must have implemented the ZFFDTOCSV command, which I have previously submitted as a tip.
The command consists of a simple CMD/CLP combination (ZCPYTOCSV) for which I have included the source.
ZCPYTOCSV parameters ...
FROMFILE
--------
Qualified name of DB2 PF/LF which is to be copied to the CSV file.
TOFILE
------
Fully qualified name (including path) of the target CSV file in the IFS. If not found, the file is created. If found, data is added or replaced according to DATAOPT parm value. Note if you wish the target file to have a "csv" extension, you must include it in the file name (this command does NOT add the ".csv").
DATAOPT
-------
Add or replace data in TOFILE. Valid values are *ADD/*REPLACE. Default is *ADD. Note: if *REPLACE is specified the target file will be deleted prior to execution of the CPYTOIMPF command (my quick fix to figuring out how to clear an IFS file).
COLHDG
------
Controls whether or not a column heading row is inserted into the target file. Valid values are the same as those in ZFFDTOCSV plus the value *NONE which is the default and will prevent a column heading row from being inserted.
Processing overview ...
1) If DATAOPT(*REPLACE) is specified the TOFILE in the IFS is deleted using the RMVLNK command.
2) If a value other that *NONE is specified for the COLHDG parm, ZFFDTOCSV is invoked to insert a column heading row into the target file.
3) CPYTOIMPF is used to copy the TOFILE into the target file as a CSV file. Note: if you have modified any of the standard parm defaults for CPYTOIMPF you will have to specify them in this CL program to make sure the proper CSV format is achieved.
That's it ... use this command to send DB2/400 data to your users in a format that's Excel friendly.
Coming soon ... a command that uses ZCPYTOCSV to allow you to dynamically select data (using SQL) and output it to a CSV file.
Code
/* ================================================================= */
/* */
/* Command : ZCPYTOCSV */
/* */
/* Description: Copy file to CSV. Optionally, add column headings */
/* */
/* ================================================================= */
ZCPYTOCSV: CMD PROMPT('Copy file to CSV')
FILE: PARM KWD(FROMFILE) TYPE(FROMFILE) MIN(1) +
PROMPT('From file name')
TOFILE: PARM KWD(TOFILE) TYPE(*CHAR) LEN(256) MIN(1) +
EXPR(*YES) PROMPT('To file')
DATAOPT: PARM KWD(DATAOPT) TYPE(*CHAR) LEN(8) RSTD(*YES) +
DFT(*ADD) VALUES(*ADD *REPLACE) +
EXPR(*YES) PROMPT('Replace/add TOFILE +
records')
COLHDG: PARM KWD(COLHDG) TYPE(*CHAR) LEN(10) RSTD(*YES) +
DFT(*NONE) VALUES(*NONE *TEXT *ALTNAME +
*COLHDGS *FLDNAME) EXPR(*YES) PROMPT('Add +
column headings')
FROMFILE: QUAL TYPE(*NAME) LEN(10)
QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) SPCVAL(*LIBL) +
PROMPT('Library name')
/* ================================================================= */
/* Program : ZCPYTOCSV */
/* */
/* Description: Copy file to CSV */
/* */
/* Parameters : FROM - Copy from this file */
/* TOFILE - Fully qualified target file name */
/* DATAOPT - Add/Replace data in target file */
/* COLHDG - Option to all column headings */
/* */
/* ================================================================= */
ZCPYTOCSV: PGM PARM(&FROM &TOFILE &DATAOPT &COLHDG)
DCL VAR(&FROM) TYPE(*CHAR) LEN(20)
DCL VAR(&FROMFILE) TYPE(*CHAR) LEN(10)
DCL VAR(&FROMLIB) TYPE(*CHAR) LEN(10)
DCL VAR(&TOFILE) TYPE(*CHAR) LEN(256)
DCL VAR(&DATAOPT) TYPE(*CHAR) LEN(8)
DCL VAR(&COLHDG) TYPE(*CHAR) LEN(8)
DCL VAR(&ERRORSW) TYPE(*LGL)
DCL VAR(&MSGID) TYPE(*CHAR) LEN(7)
DCL VAR(&MSGDTA) TYPE(*CHAR) LEN(80)
/* Global MONMSG for unexpected errors. */
MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))
/* Resolve parameters */
CHGVAR VAR(&FROMFILE) VALUE(%SST(&FROM 1 10))
CHGVAR VAR(&FROMLIB) VALUE(%SST(&FROM 11 10))
/* From file must exist */
CHKOBJ OBJ(&FROMLIB/&FROMFILE) OBJTYPE(*FILE)
/* If &DATAOPT is *REPLACE then delete the target file first. */
IF COND(&DATAOPT *EQ *REPLACE) THEN(DO)
RMVLNK OBJLNK(&TOFILE)
MONMSG MSGID(CPFA0A9)
ENDDO
/* Insert column headings if required. */
IF COND(&COLHDG *NE *NONE) THEN(ZFFDTOCSV +
FILENAME(&FROMLIB/&FROMFILE) +
TOFILE(&TOFILE) DATA(&COLHDG))
/* Copy the temporary view to the incoming IFS file name. This com- */
/* mand assumes that the target IFS file is a CSV (comma separted */
/* variable" file and that it is to be converted to ASCII format. */
CPYTOIMPF FROMFILE(&FROMLIB/&FROMFILE) TOSTMF(&TOFILE) +
MBROPT(*ADD) STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF)
RETURN
/* Standard error processing */
ERROR: IF COND(&ERRORSW) +
THEN(SNDPGMMSG MSGID(CPF9999) +
MSGF(QCPFMSG) +
MSGTYPE(*ESCAPE))
CHGVAR &ERRORSW '1'
ERROR1: RCVMSG MSGTYPE(*DIAG) +
MSGDTA(&MSGDTA) +
MSGID(&MSGID)
IF COND(&MSGID *EQ ' ') +
THEN(GOTO CMDLBL(ESCAPE))
SNDPGMMSG MSGID(&MSGID) +
MSGF(QCPFMSG) +
MSGDTA(&MSGDTA) +
MSGTYPE(*DIAG)
GOTO CMDLBL(ERROR1)
ESCAPE: RCVMSG MSGTYPE(*EXCP) +
MSGDTA(&MSGDTA) +
MSGID(&MSGID)
SNDPGMMSG MSGID(&MSGID) +
MSGF(QCPFMSG) +
MSGDTA(&MSGDTA) +
MSGTYPE(*ESCAPE)
ENDPGM
==================================
MORE INFORMATION ON THIS TOPIC
==================================
The Best Web Links: tips, tutorials and more.
Ask your programming questions--or help out your peers by answering them--in our live discussion forums.
Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.
