iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
 spooled file mapping
the command
some examples
 PDF of this tutorial

Spooled file mapping

Command SPLECELL is the one to be used for generating an Excel spreadsheet from an SCS spooled file.
This command requires some information about the spooled file body, namely:

  1. the numbers of the starting and ending list rows in a page
  2. the field mapping of list rows
To perform this investigation, use command DSPSPLF FILE(spooled_file_name) JOB(*), see the example in Figure1.

1. List starting and ending rows

In order to find out the page list starting/ending rows, you must:
  • Display the spool file by command DSPSPLF
  • Ignore the line number displayed in the top right corner of the screen (Page/line), and give your sequence numbers to the rows on the screen.
  1. List starting row
    If you look at the first page of the spooled file QPDSPLIB in Figure1 displayed by command DSPSPLF, the list starting row appears to be row number 11.
    However, you should also check the second page. In some cases, the first page carries more header lines than the following ones. In such cases, the correct list starting row is the one showing up in the second page.
  2. List ending row
    Usually the list ending row is always the same, unless in the last page of the spooled file. In such a case, you should ignore the last page, and select as list ending row the one shown on the other pages.
    In this example, the list ending row is row number 57.
Therefore, in this example, command SPLECELL must have parameter BDYRWS(11 57).
Note 1.Command SPLECELL, when reading the spooled file, ignores
  • any row not matching the field mapping of a list row
  • any blank row.
Figure 1- DSPSPLF FILE(QPDSPLIB) JOB(*) - page 1

2. Field mapping of list rows

Command SPLECELL has to know the field mapping of list rows.
There are two ways this field mapping can be made available to command SPLECELL:
  • Using parameter BLKCOLS
  • Specifying the printer file record format name used for generating list rows

  1. Using parameter BLKCOLS

    This is the case when a spooled file is not generated from a printer file. This case applies to spool files generated by system commands, like DSPLIB.
    In such a case, parameter BLKCOLS is used to communicate to command SPLECELL the positions of the blank columns separating fields in a list row.
    In Figure 2 these columnns were given a light yellow color:
    Figure 2- List row field mapping for solled file QPDSPLIB
    In this case, the SPLECELL command parameters are:
    BLKCOLS((1 3) (14 15) (24 25) (36 37) (52 53) (110 111))

  2. Specifying the PRTF and the RCDFMT used for generating list rows

    If the spooled file was generated from an available printer file, you do not have to use parameter BLKCOLS. Do instead the following:
    1. Run command WRKSPLFA (Work with Spooled File Attr) for that spooled file
    2. Look at item "Device file"
    3. DSPFD TYPE(*RCDFMT) for that item
    4. Take a note of the Record Format name providing a list row (Command DSPFFD may help you in selecting the appropriate record format)
    5. In parameter RCDFMT of command SPLECELL, enter that record format name.
      No BLKCOLS parameter is needed.
    Run command IFSTOOL/FIND BASEDIR('/hssfcgi/html') PATTERN('*.htm') PRINT(*YES) DSPPRT(*NO).
    It generates a spool file named FINDPRT.
    2. Item "device file" specifies FINDPRT in library IFSTOOL.
    3. Run command DSPFD FILE(IFSTOOL/FINDPRT) PRTF(*FILE) RCDFMT(LINE). The following record formats are available: HEADER, LINE, LINECONT, NONE.
    4. Using command DSPFFD you will easily establish that the record format used to provide list rows is record format LINE.
    5. In command SPLECELL specify RCDFMT(LINE).
      No BLKCOLS parameter is needed.

  3. Specifying an ad-hoc user defined printer file

    When command DSPFD for the spooled file name does not provide a Record Format List (example: spooled file QPDSPLIB obtained from command DSPLIB ... OUTPUT(*PRINT)), instead of manually specifying the blank columns of the page body rows in parameter BLKCOLS of command SPLECELL, in parameter PRTF you may specify the name of some ad-hoc printer file of yours documenting the layout of the page body rows, and specify its only record format name in parameter RCDFMT.

    An example of such printer file definition is available in member SAMPLEC1 of SPLECELL/QDDSSRC and is used to map the page body rows in a QPDSPLIB spooled file:
         A          R DSPLIBROW                 SPACEB(1)                 
         A            DSPLIBOBJ     10A        4TEXT('object name')       
         A            DSPLIBTYPE     8A       16TEXT('object type')       
         A            DSPLIBATTR    10A       26TEXT('object attribute')  
         A            DSPLIBSIZE    14S 0     38TEXT('object size')       
         A            DSPLIBDESC    50A       54TEXT('object description')

    You may then mention it in command SPLECELL parameters PRTF and RCDFMT, example:
    Spooled file . . . . . . . . . . FILE         > QPDSPLIB      
    Job name . . . . . . . . . . . . JOB            *           
      User . . . . . . . . . . . . .                                      
      Number . . . . . . . . . . . .                                  
    Spooled file number  . . . . . . SPLNBR         *ONLY       
    Printer file . . . . . . . . . . PRTF         > SAMPLEC1      
      Library  . . . . . . . . . . .              >   SPLECELL  
    Printer file record format . . . RCDFMT         DSPLIBROW 
       ...   ...   ...

    An example of using this technique is provided in program SPLECELL/SAMPLEC1, source member SAMPLEC1 in SPLECELL/QCLSRC.