Question

AS400 RLU AFPDS Printer File Transfer to Excel

V5R2, AFPDS, RLU, Report Layout Utility

We operate on a V5R2 release. IBM 9406 820 model.
I want to transfer a RLU Printer File with AFPDS char to an excel spread sheet.

There is an example on how to do this on search400.techtarget.com contributed by Benito Abraham. It will email an AS400 spool file to anyone in excel format. If you are not a member of search400.com try the below:

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.

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:

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)