Selective SPOOLFILE copy to CSV files and e-mail

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)          

Back to top

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.