Selective SPOOLFILE copy to CSV files and e-mail
This command allows you to send any iSeries report in CSV file format without its headings to an e-mail account.
The XCPYSPLF command lets iSeries programmers send reports in CSV file format to e-mail accounts. In order to properly explain how it works we are going to develop an example. I've included the source code for this command at the bottom of the page.
Let's say that we want to work with the AMARTINEZ'S $SYSLIST file:
We go to the selected record and type 5 > Enter.
Since we only want the records and not the headlines, we must create a selection criteria. Generally speaking, we have to choose a character that is repeated in the same position in the records we want to include in our file. Iit could be a decimal point, a dash, etc. In this example we'll choose a '/' located in position 19.
We go back to our spool (enter) and hit F11 twice:
Next, write down the marked values for the selected spool: File: $SYSLIST
Spool Number: 4
Job: QPADEV0003
User: AMARTINEZ
Job Number: 193490
Finally, we invoke the XCPYSPLF command and load the parameters along with the e-mail target:
The receiver will see something like this:
And the received file will look like this without headlines and in CSV format:
In order to work properly you should create the folder download with public authority *all and the users must be created in the distribution directory. If for some reason you can't or don't want to send a e-mail, you can find your converted report without headings in the DOWNLOAD folder were you can get it with iSeries Navigator or any other tool that you like.
Below is the source code you'll need:
Source Code XCPYSPLF Cmd
CMD PROMPT('XCopy spool file to csv')
PARM KWD(SPOOL) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('Spool file to convert')
PARM KWD(JOB) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('Job Name')
PARM KWD(USER) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('Spool file User')
PARM KWD(JOBNBR) TYPE(*CHAR) LEN(6) MIN(1) +
PROMPT('Job Number')
PARM KWD(SPOOLNBR) TYPE(*CHAR) LEN(11) MIN(1) +
PROMPT('Spool Number')
PARM KWD(POS) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('Choosen character position')
PARM KWD(SEL) TYPE(*CHAR) LEN(3) MIN(1) +
CHOICE('*EQ *NE *GT *LT, etc') +
PROMPT('Selection Criteria')
PARM KWD(VALOR) TYPE(*CHAR) LEN(1) MIN(1) +
PROMPT('Character to select/omit')
PARM KWD(MAIL) TYPE(*CHAR) LEN(64) MIN(1) +
PROMPT('Mail address')
Source Code XCPYSPLF CLP (XCPYSPLF Cpp program)
PGM PARM(&SPOOL &JOB &USER &JOBNBR &SPOOLNBR +
&POS &SEL &VALOR &MAIL)
DCL VAR(&SPOOL) TYPE(*CHAR) LEN(10) /* Spool +
File */
DCL VAR(&FILE) TYPE(*CHAR) LEN(10) VALUE(XSPOOL) +
/* Temporal File */
DCL VAR(&FIL1) TYPE(*CHAR) LEN(10) VALUE(XSPOO1) +
/* Temporal File */
DCL VAR(&LIB) TYPE(*CHAR) LEN(10) VALUE(QTEMP) +
/* Temporal Library */
DCL VAR(&JOB) TYPE(*CHAR) LEN(10) /* Spool File +
Job Name */
DCL VAR(&USER) TYPE(*CHAR) LEN(10) /* Spool File +
User */
DCL VAR(&JOBNBR) TYPE(*CHAR) LEN(6) /* Spool +
File Job Number */
DCL VAR(&SPOOLNBR) TYPE(*CHAR) LEN(11) /* Spool +
Number */
DCL VAR(&POS) TYPE(*CHAR) LEN(10) /* Position +
selected character */
DCL VAR(&SEL) TYPE(*CHAR) LEN(3) /* Criteria */
DCL VAR(&VALOR) TYPE(*CHAR) LEN(1) /* Character */
DCL VAR(&MAIL) TYPE(*CHAR) LEN(64) /* Target +
mail address */
/* Create Working File */
CRTPF FILE(&LIB/&FILE) RCDLEN(198)
MONMSG MSGID(CPF0000)
/* Be sure it is clear */
CLRPFM FILE(&LIB/&FILE)
MONMSG MSGID(CPF0000)
/* First copy from the spool file */
CPYSPLF FILE(&SPOOL) TOFILE(&LIB/&FILE) +
JOB(&JOBNBR/&USER/&JOB) SPLNBR(&SPOOLNBR)
/* Create 2nd Working File */
CRTPF FILE(&LIB/&FIL1) RCDLEN(198)
MONMSG MSGID(CPF0000)
/* Copy with selection criteria from 1st to 2nd file */
CPYF FROMFILE(&LIB/&FILE) TOFILE(&LIB/&FIL1) +
MBROPT(*REPLACE) CRTFILE(*NO) +
INCCHAR(*RCD &POS &SEL &VALOR) FMTOPT(*NOCHK)
/* Copy 2nd file to a stream file in DOWNLOAD folder */
CPYTOSTMF +
FROMMBR('/QSYS.LIB/QTEMP.LIB/XSPOO1.FILE/XS+
POO1.MBR') +
TOSTMF('/QDLS/DOWNLOAD/XSPOOL.CSV') +
STMFOPT(*REPLACE) STMFCODPAG(*PCASCII)
/* Grant authority to the stream file */
CHGAUT OBJ('/QDLS/DOWNLOAD/XSPOOL.CSV') +
USER(*PUBLIC) DTAAUT(*RWX) OBJAUT(*ALL)
/* Send the file by mail */
SNDDST TYPE(*DOC) TOINTNET((&MAIL)) DSTD('Requested +
spool file') DOC(XSPOOL.CSV) FLR(DOWNLOAD)
ABOUT THE AUTHOR: Benito Abraham is an analyst programmer at Pride International. He has over 15 years experience on AS/400, including iSeries security and RPG.
