Add column headings to a CSV file
Command ZFFDTOCSV uses information from a file's field descriptions to create a "heading" row in a CSV file.
Command ZFFDTOCSV uses information from a file's field descriptions to create a "heading" row in a CSV file. This...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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
Start the conversation
0 comments