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 printer file RCDFMT used for generating list rows
    If the spooled file was generated from a printer file, you 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) TYPE(*RCDFMT). The following record formats are available: HEADER, LINE, LINECONT, NONE.
    4. Using command DSPFFD you will easily establish that the record format uised to provide lost rows is record format LINE.
    5. In command SPLECELL specify RCDFMT(LINE).
      No BLKCOLS parameter is needed.