Problem solve Get help with specific problems with your technologies, process and projects.

Copy a file to CSV, with column headings

This command will copy any DB2/400 file into a CSV file in the IFS.

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.


  
/* ================================================================= */
/*                                                                   */
/* 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.

Dig Deeper on iSeries CL programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close