Add column headings to a CSV file

command requires that you have implemented one of the many commands available that allow you to execute SQL from within a CL program. For this example, I use a command called ZEXECSQL to achieve this.

The command accepts parms as follows...

FILENAME
--------
Fully qualified name of the file whose field descriptions will be used to generate the column-heading row.

TOFILE
------
Fully qualified name (including path) of the target file. This file MUST exist in the IFS.

DATA
----
Specifies which data will be used to generate the column headings. Basically, this determines which field from the QADSPFFD system file description will be used. Valid values and the corresponding QADSPFFD field are ...

*FLDNAME - WHFLDI (this is the default)
*TEXT - WHFTXT
*ALTNAME - WHALIS
*COLHDG - WHCHD1/WHCHD2/WHCHD3 (concatenated together with 1 blank between each field)

Processing overview
-------------------
1. The file/field descriptions for the FILENAME parm are dumped to a temporary file via DSPFFD.

2. CL program reads each record in the temporary file and uses the data selected via the DATA parm to build a large "comma separated" character string.

3. Once all records have been processed a temporary file is created using the ZEXECSQL command.

4. The "comma separated" character string is INSERTed into the new table as 1 field.

5. The new table is then copied to the target IFS file using CPYTOIMPF.

... and voila! You now have a properly formatted CSV file containing 1 field for each field in the source file. Open it with Excel and each heading will appear in it's own cell.

This command is interesting but not much use on it's own. My next tip (ZCPYTOCSV)will show how I use ZFFDTOCSV to insert a column-heading row within a generic file to CSV tool.

   
/* ================================================================= */
/*                                                                   */
/* Command    : ZFFDTOCSV                                            */
/*                                                                   */
/* Description: Create CSV file column headings from file field      */
/*              descriptions                                         */
/*                                                                   */
/* ================================================================= */

 ZFFDTOCSV:  CMD        PROMPT('Create CSV column headings')

 FILE:       PARM       KWD(FILENAME) TYPE(FILENAME) MIN(1) +
                          PROMPT('File name')

 TOFILE:     PARM       KWD(TOFILE) TYPE(*CHAR) LEN(256) MIN(1) +
                          EXPR(*YES) PROMPT('To file')

 DATA:       PARM       KWD(DATA) TYPE(*CHAR) LEN(10) RSTD(*YES) +
                          DFT(*FLDNAME) VALUES(*FLDNAME *TEXT +
                          *ALTNAME *COLHDGS) EXPR(*YES) +
                          PROMPT('Which data to use')

 FILENAME:   QUAL       TYPE(*NAME) LEN(10)
             QUAL       TYPE(*NAME) LEN(10) DFT(*LIBL) +
                          SPCVAL((*LIBL)) PROMPT('Library name')


/* ================================================================= */
/* Program    : ZFFDTOCSV                                            */
/*                                                                   */
/* Description: Insert column headings into a CSV file.              */
/*                                                                   */
/* Parameters : FILENAME - Create column headings from this file     */
/*              TOFILE   - Fully qualified target file name          */
/*              DATA     - Type of data to be used                   */
/*                                                                   */
/* ================================================================= */

             PGM        PARM(&FILENAME &TOFILE &DATA)

             DCL        VAR(&FILENAME) TYPE(*CHAR) LEN(20)
             DCL        VAR(&TOFILE) TYPE(*CHAR) LEN(256)
             DCL        VAR(&DATA) TYPE(*CHAR) LEN(10)

             DCL        VAR(&FILE) TYPE(*CHAR) LEN(10)
             DCL        VAR(&LIB) TYPE(*CHAR) LEN(10)
             DCL        VAR(&TOFILE) TYPE(*CHAR) LEN(256)
             DCL        VAR(&FLDDLM) TYPE(*CHAR) LEN(1) VALUE(',')
             DCL        VAR(&REC) TYPE(*CHAR) LEN(3000)
             DCL        VAR(&COUNT) TYPE(*DEC) LEN(5 0)

 /* Error variables */

             DCL        VAR(&ERRORSW)   TYPE(*LGL)
             DCL        VAR(&MSGID)     TYPE(*CHAR) LEN( 7)
             DCL        VAR(&MSGDTA)    TYPE(*CHAR) LEN(80)
             DCL        VAR(&MSGF)      TYPE(*CHAR) LEN(10)
             DCL        VAR(&MSGFLIB)   TYPE(*CHAR) LEN(10)

             DCLF       FILE(QADSPFFD)

 /* Global MONMSG for unexpected errors */

             MONMSG     MSGID(CPF0000 SQL0000) EXEC(GOTO CMDLBL(ERROR))

/* Resolve parameters */

             CHGVAR     VAR(&FILE) VALUE(%SST(&FILENAME 1 10))
             CHGVAR     VAR(&LIB ) VALUE(%SST(&FILENAME 11 10))

/* Get incoming file field descriptions */

             DLTF       FILE(QTEMP/QADSPFFD)
             MONMSG     MSGID(CPF0000)
             DSPFFD     FILE(&LIB/&FILE) OUTPUT(*OUTFILE) +
                          OUTFILE(QTEMP/QADSPFFD)

/* Read thru the file field descriptions and build a string variable */
/* (&REC) containing a comma separated variable representation of the*/
/* column heading for each field in the table. For example, if the   */
/* incoming file consists of 2 fields named FLD1 and FLD2, then the  */
/* value in &REC should look like ≪ "FLD1","FLD2"≫  (if &DATA equals   */
/* *FLDNAME). Note the absence of a comma at the end of the string.  */

             OVRDBF     FILE(QADSPFFD) TOFILE(QTEMP/QADSPFFD)
 LOOP:       RCVF
             MONMSG     MSGID(CPF0000) EXEC(GOTO CMDLBL(ENDPGM))

/* Count the records so we'll know when we have tha last record.     */
/* If we're on the last record change the &FLDDLM variable to blank  */
/* so that no comma is put after the last column heading.            */

             CHGVAR     VAR(&COUNT) VALUE(&COUNT + 1)
             IF         COND(&WHFLDN = &COUNT) THEN(CHGVAR +
                          VAR(&FLDDLM) VALUE(' '))

/* Add the current record to the column heading string. The user can */
/* specify which data to use as the column heading. Valid values     */
/* are ...                                                           */
/*    Value     Data used                                            */
/*    --------  ---------------------------------------------------- */
/*    *TEXT     Field text (up to 50 bytes)                          */
/*    *ALTNAME  Alternate (alias) name (up to 30 bytes)              */
/*    *FLDNAME  Field name (up to 10 bytes)                          */
/*    *COLHDG   Column headings (3 x up to 20 bytes) - 3 fields are  */
/*              concatenated together with a blank between the 3     */
/*              values.                                              */

             IF         COND(&DATA *EQ *TEXT) THEN(CHGVAR VAR(&REC) +
                          VALUE(&REC *TCAT '"' *CAT &WHFTXT *TCAT +
                          '"' *CAT &FLDDLM))

             IF         COND(&DATA *EQ *ALTNAME) THEN(CHGVAR +
                          VAR(&REC) VALUE(&REC *TCAT '"' *CAT +
                          &WHALIS *TCAT '"' *CAT &FLDDLM))

             IF         COND(&DATA *EQ *FLDNAME) THEN(CHGVAR +
                          VAR(&REC) VALUE(&REC *TCAT '"' *CAT +
                          &WHFLDI *TCAT '"' *CAT &FLDDLM))

             IF         COND(&DATA *EQ *COLHDGS) THEN(CHGVAR +
                          VAR(&REC) VALUE(&REC *TCAT '"' *CAT +
                          &WHCHD1 *BCAT &WHCHD2 *BCAT &WHCHD3 *TCAT +
                          '"' *CAT &FLDDLM))

             GOTO       CMDLBL(LOOP)

/* At this point we have read all the records in the table and have  */
/* filled &REC with our column headings. Build a temporary table     */
/* with 1 field in it and write write this value to it. Then use     */
/* CPYTOIMPF to copy the temp table into a CSV file.                 */

 ENDPGM:     DLTF QTEMP/ZFFDTOCSV
             MONMSG CPF0000

             ZEXECSQL   SQLSTMT('CREATE TABLE QTEMP/ZFFDTOCSV (HDGS +
                          CHAR (3000 ) NOT NULL WITH DEFAULT)')

             ZEXECSQL   SQLSTMT('insert into qtemp/zffdtocsv values +
                          (''' *CAT &REC *TCAT ''')')

             CPYTOIMPF  FROMFILE(QTEMP/ZFFDTOCSV) TOSTMF(&TOFILE) +
                          STMFCODPAG(*PCASCII) RCDDLM(*CRLF) +
                          STRDLM(*NONE)

/* Clean up and bail                                                 */

             DLTF QTEMP/ZFFDTOCSV
             MONMSG CPF0000
             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(QSQLMSG)   +
                        MSGDTA(&MSGDTA) +
                        MSGTYPE(*DIAG)
             GOTO       CMDLBL(ERROR1)

 ESCAPE:     RCVMSG     MSGTYPE(*EXCP) MSGDTA(&MSGDTA) MSGID(&MSGID) +
                          MSGF(&MSGF) MSGFLIB(&MSGFLIB)
             SNDPGMMSG  MSGID(&MSGID) MSGF(&MSGFLIB/&MSGF) +
                          MSGDTA(&MSGDTA) MSGTYPE(*ESCAPE)

             ENDPGM