The Copy Spooled File Data command reads a spooled file and outputs a
file intended for a spread sheet processor. Selection criteria exist
so that only columnar data may be output. Options exist to remove
delimiters within the data, add delimiters at the end of fields,
surround fields with quotes, and to include column headings. An IFS
object or a data base file may be output.
Only spooled files that can be read by the system CPYSPLF command can
be used (this excludes files created by AFP).
If the prompter is used for CPYSPLFDTA, the initial display assumes
output (OUTTYPE parameter) to the IFS (*IFS). Using the Enter key
will display the specific prompts associated with a stream file and
additional parameters. If *DBF is entered for the output type,
specific parameters for a data base file are displayed along with
other parameters.
A typical command to read the spooled output file created by WRKSBS
(spooled file name is QPDSPSBS) and select only the records that
describe the detail subsystem information would be:
CPYSPLFDTA FILE(QPDSPSBS) SPLNBR(*LAST) +
OUTTYPE(*IFS) IFSFILE(DSPSBS) +
STMFOPT(*REPLACE) +
INCLUDE((34 34 *DIGIT))
The INCLUDE parameter specifies that the only records to be included
in the output will have a digit in position 34. This is the last
digit of the job number field assigned by the system. The page
heading, column headings, and 'end of listing' lines would be
omitted.
By default, the standard separator character (comma in the USA),
would be removed from any data lines and separator characters would
be placed at the end of each data field. See the later discussion on
'Delimited file data').
Use WRKLNK to review the data in the stream file.
By default, spooled files would be output describing the omitted
lines and the included lines. The spooled files are intended to
assist you in identifying what will be omitted and included. After
you are satisfied with your omit/include criteria, specify
PRINT(*NONE) to bypass the creation of these spooled files.
A set of OMIT statements may also be made such as:
CPYSPLFDTA FILE(QPDSPSBS) SPLNBR(*LAST) +
OUTTYPE(*IFS) IFSFILE(DSPSBS) +
STMFOPT(*REPLACE) +
OMIT(1 1 *BLANK))
would omit any lines that were blank in position 1.
Type of output (OUTTYPE)
------------------------
The OUTTYPE parameter determines the type of output to be created.
The default is *IFS meaning the included data will be written to a
stream file as specified for the IFSFILE parameter. The extension of
the file defaults to '.csv'.
This allows a subsequent function to:
** Process just the columnar data.
** Move the data as a PC spread sheet object.
** A review of the data to ensure your OMIT and INCLUDE
statements are producing the desired results.
OUTTYPE(*DBF) may be specified to output to a data base file member.
Additional prompts appear for the TOFILE, TOFILEMBR, and MBROPT. The
default file assigned is TAASPLFDTA in QTEMP. This is considered a
work file. If the file does not exist, it is created. If it does
exist, the file is deleted and re-created.
A spread sheet processor may be able to import this type of file
directly from the data base file.
Delimited file data
-------------------
Some spread sheets are designed to work with delimited data such as
fields separated by commas.
Because delimiters may exist in the data such as:
1,000,000.00
the data would be misread by certain spread sheet processors.
There are two methods of avoiding this:
** By default, the standard delimiter separator will be removed
from the data. The standard delimiter separator is determined
by use of the job's DECFMT parameter. In the US, a comma is
preferred for the delimiter. In Europe and Canada, a period
is preferred.
Decimal points in spread sheet processors are normally allowed
to exist (period in the US and comma in Europe and Canada).
They are not removed by CPYSPLFDTA.
After removing any delimiters, the data is right aligned and
would appear as:
1000000.00
** A quote character ((single (') or double (")) may be inserted
surrounding each of the fields. If ADDQUOTE(*SINGLE) is
specified, the data would appear as
'1,000,000.00'
While this may be undesirable for spread sheet processing of
decimal fields, it provides a solution for a field such as:
Thomas Jones, Jr.
If the delimiters are removed and ADDQUOTE(*NONE) is
specified, the data would appear as:
Thomas Jones Jr.
If delimiters are not removed and ADDQUOTE(*DOUBLE) is
specified, the data would appear as:
"Thomas Jones, Jr."
There is an exception made when the quote character specified
exists in the data such as Jones's. See the later discussion.
Options exist as follows:
** Removal of delimiters (RMVDELIMTR). The default is *DFT which
will remove the standard delimiter (separator). A specific
one byte separator may be entered such as RMVDELIMTR(*) or
RMVDELIMTR(X'2A') To remove a blank (X'40') as a separator,
you must enter RMVDELIMTR(*BLANK). RMVDELIMTR(*NONE) may be
specified to prevent the removal of separators.
** Addition of a delimiter (ADDDELIMTR). The default is *DFT
which will add the standard delimiter (separator). A specific
one byte separator may be entered such as ADDDELIMTR(*) or
ADDDELIMTR(X'2A') To use a blank (X'40') as a separator, you
must enter ADDDELIMTR(*BLANK). ADDDELIMTR(*NONE) may be
specified to prevent the addition of separators. Note that
while *NONE may be specified, most spread sheet processors
require a separator character.
** Addition of surrounding quotes (ADDQUOTE). The default is
*NONE which means no surrounding quotes will be added. Either
*SINGLE (') or *DOUBLE (") may be used as the surrounding
characters of each field.
There is an exception made if you request to add a quote
(assume *SINGLE) and a value such as Jones's appears. In this
case the quote would be doubled and the data would appear as
Jones''s. See the later discussion of how this will offset
the data.
If ADDDELIMTR(*NONE) and ADDQUOTE(*NONE) is specified, a single pass
of the data occurs.
If either the ADDDELIMTR or ADDQUOTE parameters are other than *NONE,
a second pass of the data occurs to insert the delimiters and quotes.
On the first pass of the data, selection (INCLUDE and OMIT
parameters) removes any unwanted data (such as heading lines) and any
requested delimiter characters. An array of Xs is set up internally
for the included records that describes where columnar data exists.
For example, if your data looked like:
1 2
....+....0....+....0....+.
Jones 1000 ...
Thompson 25 ...
The array of Xs would be built internally as:
1 2
....+....0....+....1....+.
XXXXXXXX XXXX ...
The selected data is then re-read and delimiters are placed in the
data as it is written to either the data base file or the IFS.
Excess blanks are removed.
The data generated would appear as:
1 2
....+....0....+....1....
Jones ,1000,...
Thompson, 25,...
Note that data is shifted to remove blanks between fields. The data
width of the field is determined by the array of Xs.
If column headings are included (see the COLHDGFREQ parameter), they
are also considered when the array of Xs is generated.
If ADDQUOTE(*SINGLE) had been specified, the data would appear as:
1 2
....+....0....+....1....
'Jones ','1000',...
'Thompson',' 25',...
If the Jones value was actually O'Rourke, the existing quote is
doubled which may offset the data for any records containing a quote.
The data generated would be:
1 2
....+....0....+....1....
'O''Rourke','1000',...
'Thompson',' 25'...
In a rare case, the inclusion of surrounding or additional quotes may
cause the width of the print line to be expanded from its original
length. The maximum provided for is 378 (the largest spooled file
that the system supports).
Including column headings (COLHDGFREQ and COLHDGLINES)
------------------------------------------------------
An option exists to allow the column headings on the listing to be
included in the output. Spread sheets typically ignore any
non-numeric data when performing calculations.
For example, if your data looks like:
Name Amount
Jones 100.00
Smith 25.00
You can include the column headings 'Name' and 'Amount' in the
output. This can better describe the spread sheet columns. Up to 3
lines of column headings may be included.
To specify that column headings be included, you need to know where
the first set of column headings exist in the spooled data. Use
DSPSPLF and determine the print lines where the column headings exist
(blank lines do not appear using DSPSPLF).
If the DSPSPLF display of your spooled data looks like:
Nov 5, 2009 10:05:00 The Big Report Page 1
Amount
Name Due
xxxxx nn.nn
xxxxx nn.nn
The column headings appear on lines 2 and 3 (do not count any blank
lines that will appear in the actual listing).
The default for COLHDGFREQ is *NONE, meaning column headings will not
be included in the output.
If you specify *ONCE, the column headings for the lines you specified
will be written as the first output. They will appear as the first
row (or rows) in the spread sheet and will not be repeated. In the
previous example of a 2 line column heading, the first two rows would
be used. This can help identify the data in the spread sheet.
The parameters would be entered as:
COLHDGFREQ(*ONCE) COLHDGLINES(2 3)
If you specify a number such as 30, the column headings will be
repeated every 30 rows in the spread sheet output. The parameters
would be entered as:
COLHDGFREQ(30) COLHDGLINES(2 3)
For normal DP output, it would be typical to have 50+ detail lines on
a page. However, for spread sheets you would normally see a smaller
number of rows per page. Adding column headings periodically within
the data may help the spread sheet user.
Include and omit selection (INCLUDE and OMIT parameters)
--------------------------------------------------------
Up to 100 each of 'include' and 'omit' statements may be entered.
The OMIT statements are 'ored' together meaning any line that
satisfies an omit statement will be omitted.
The INCLUDE statements are 'anded' together meaning that all include
statements must be satisfied to include the line. Note that if the
spooled output is in lower case, you must place quotes around a value
such as 'Subsystem'
Special values may be entered to describe the data as digits
(*DIGIT), blank (*BLANK), or non-blank (*NONBLANK). The special
values may be used for a series of bytes such as:
INCLUDE((30 34 *DIGIT)
The record must have digits in positions 30-34 to be included.
Print option (PRINT)
--------------------
The Print option is designed to assist you in identifying whether
your 'include' and 'omit' statements achieve your requirements.
The PRINT parameter defaults to *BOTH meaning to output 2 listings.
The first is for the included records (INCLUDES) and the second is
for the omitted records (OMITS). A ruler is provided for each
listing to help determine the print positions that should be
specified. Neither listing specifies overflow lines so you will only
see the print lines for the detail information.
You may output both INCLUDES and OMITS or just one of the listings.
When you are satisfied with the selection criteria, specify
PRINT(*NONE) to bypass any listed output.
Exit program
------------
In some cases, you may need to write an exit program to perform
selection or modify data.
The order of processing is:
- Omits - Any satisfied omit excludes a record
- Print the omits
- Includes - All includes must be satisfied to include
- Exit pgm - INC or OMT must be returned
- Print if omitted
- Print any included records (original value)
- Remove delimiters
- Add delimiters and quotes
The exit program is passed 3 parameters:
- RQSTYP *CHAR LEN(3) - Blanks or FNL
- EXTRTN *CHAR LEN(3) - Pass back INC or OMT
- DATA *CHAR LEN(378) - Data passed and returned
The Exit program is only called when a record is to be included. If
any Omit criteria has rejected the record, the exit program is not
called.
The RQSTYP parameter will be passed to the Exit program as blanks
when the DATA parameter holds a record. When there are no more
spooled lines to be included, FNL will be passed to the Exit program.
The purpose of this is to allow a program (such as RPG) to do a
return until FNL appears and then the program would end normally
(such as SETON LR).
The EXTRTN is set to blanks when the Exit program is called. It must
be set in your program to either INC (include) or OMT (omit).
The DATA value is left adjusted (378 is the widest spool line
supported by the system). You may change the data value. For
example, the WRKSYSSTS printed output can show '+++++' in a field.
Your program could change this to '99999'.
The INCLUDED spooled file will show the data before the exit program
has made any changes. The exit program may rearrange the data, but
should do so in a manner that will preserve the columnar format.
Printing data base file data
----------------------------
The typical solution is to have an application program or query print
the data with the required field headings and layout.
You can also print the data with standard TAA Tools.
** The DSPDBF command is a front end to the SQL print function.
You do not have to have an existing query. Just enter the
file name you want to print. The fields are printed left to
right as they appear in the file.
** The PRTDB command supports two functions.
-- The PRTDB command allows you to prompt for the fields
you want to print and you can arrange them in a left to
right order.
-- The PRTDB2 command will print the fields left to right
as they are defined in the file (a different format
appears that used by DSPDBF).
Tips
----
** If you have decimal fields that appear in the records you
want, including those lines that have digits in certain
positions and specifying INCLUDE(nn *DIGIT) can be effective.
** If all the records you want have a decimal separator in a
certain specific position, specifying INCLUDE(nn '.') can be
effective.
** If you have formatted date or time fields that only exist in
the print lines you want, specifying an 'include' of the
formatting character (eg '/' or ':') can be effective.
** Total lines are often not needed as a spread sheet will
consider them to be additional detail records and double the
totals. Since total lines often resemble detail lines, you
need some method of identifying a total line. For example, it
might be blank in a description column, or asterisks or the
word 'Total' to identify the line.
** Reviewing both listings of INCLUDES and OMITS allows you to
determine you are selecting the correct records.
** Spread sheet processors normally require a delimiter, but
usually allow any character to be specified. Specifying
ADDDELIMTR(*NONE) may be specified, but will normally cause
problems with a spread sheet processor.
** Because the CPYSPLFDTA parameters can be complex, you will
probably want to use a CL program for each spooled file that
you need to work with on a repetitive basis. You can develop
the CPYSPLFDTA command you need interactively with trial and
error and then use the CVTJOBLOG TAA command to copy the
previous n minutes of commands entered from your job into a CL
source member. Then use a source editor to delete the CL
statements you don't need rather than re-entering a complex
set of selection criteria.
Another solution is to use the FAVCMD tool and place the final
version of CPYSPLFDTA into a FAVCMD file. For example,
CPYSPLFDTA ...
ADDFAVCMD FAVID(xxxx)
When you want to run the command, enter
EXCFAVCMD FAVID(xxxx)
or the shorthand command
FV FAVID(xxxx)
CPYSPLFDTA escape messages you can monitor for
----------------------------------------------
TAA9891 STMFOPT(*NONE) specified and the object exists
Escape messages from based on functions will be re-sent.
Command parameters *CMD
------------------
FILE The spooled file name to be processed. Only spooled
data that can be read by the system CPYSPLF command
can be used (this excludes files created by AFP).
JOB The qualified job name of the job with the spooled
file. The default is * meaning the current job.
SPLNBR The file number of the spooled file. This defaults
to *ONLY. *LAST or a specific number may be
specified.
RMVDELIMTR The default is *DFT which will remove the standard
separator that is normally used as a field
delimiter. This avoids the problem of a field such
as 1,000.00 from causing confusion in a spread sheet
processor (the data would be passed as 1000.00).
The separator that is removed is determined by use
of the job's DECFMT value (normally comma in the
USA).
A specific one byte separator may be entered such as
RMVDELIMTR(*) or RMVDELIMTR(X'2A')
To use a blank (X'40') as a separator, you must
enter RMVDELIMTR(*BLANK).
RMVDELIMTR(*NONE) may be specified to prevent the
removal of separators.
ADDDELIMTR The default is *DFT which will add the standard
separator as a field delimiter. This normally
results in data being generated as 'abc,123,...'.
The separator that is added is determined by use of
the job's DECFMT value (normally comma in the USA).
A specific one byte separator may be entered such as
ADDDELIMTR(*) or ADDDELIMTR(X'2A')
To use a blank (X'40') as a separator, you must
enter ADDDELIMTR(*BLANK).
ADDDELIMTR(*NONE) may be specified to prevent the
addition of separators. Note that while *NONE may
be specified, most spread sheet processors will
require a separator character.
ADDQUOTE The default is *NONE meaning that no quotes will
surround the fields.
*SINGLE may be specified to surround the fields with
single quotes. For example, 'abc','123',... would
be generated.
*DOUBLE may be specified to surround the fields with
a double quote character. For example,
"abc","123",... would be generated.
There is an exception made if you request to add a
quote (assume *SINGLE) and a value such as "Jones's"
appears. In this case the quote would be doubled
and the data would appear as "Jones''s". See the
previous documentation for how this will offset the
data.
OUTTYPE The output type to be used.
*IFS is the default to output to the IFS. See the
IFSFILE, and STMFOPT parameters.
*DBF may be specified to output to a data base file.
See the TOFILE, TOFILEMBR, and MBROPT parameters.
IFSFILE The name of the IFS file to write to if
OUTTYPE(*IFS) is specified.
The default is *FILE which means the same name as
the spooled file. If OUTTYPE(*DBF) is used, IFSFILE
must be either *FILE or *NONE and is ignored.
Using OUTTYPE(*IFS), either *FILE or a stream file
must be specified. The stream file name may differ
from the spooled file name and may contain directory
names.
If *FILE is used or the file name does not start
with a /, the default is to copy to the user's
current directory (normally the home directory as
specified in the HOMEDIR parameter of the current
user profile). If the IFS file name starts with a
/, it is written into the root directory. In either
case, if the stream file name includes any directory
names, those directories must exist. See the later
examples.
The user's current directory may differ from the
user profile's HOMEDIR directory if it has been
changed with a CL command (CHGCURDIR or CD) or by
the cd command in QSH command entry.
The value may include an extension in which case it
is recommended that IFSEXT(*NONE) be used. If an
extension is specified in the IFSEXT parameter, it
will be concatenated to the IFSFILE name even if the
IFSFILE value already includes an extension.
If the stream file does not exist, it will be added.
The IFSFILE parameter may be up to 5000 bytes long.
The following examples assume that the user's
current directory is '/home/usera' and describe the
resulting IFS file name that will be used. Note
that the current directory may refer to a directory
other than the user profile's HOMEDIR directory.
The spooled file name is 'report' and the stream
file to be written will be named 'report'.
-- IFSFILE(*FILE) IFSEXT('.csv)
'/home/usera/report.csv'
Since both the IFSFILE and IFSEXT values
are the same as the defaults, they
need not have been specified.
-- IFSFILE('report') IFSEXT('.csv)
'/home/usera/report.csv'
-- IFSFILE('report.csv') IFSEXT(*NONE)
'/home/usera/report.csv'
-- IFSFILE('report.txt') IFSEXT(*NONE)
'/home/usera/report.txt'
-- IFSFILE('report.txt') IFSEXT(.csv)
'/home/usera/report.txt.csv'
-- IFSFILE('subdir1/report') IFSEXT(.csv)
'/home/usera/subdir1.report.csv'
Because no leading / exists, the 'subdir1'
directory must be within the user's
current directory.
-- IFSFILE('/subdir1/report') IFSEXT(.csv)
'/subdir1/report.csv'
Because a leading / exists, the directory
'subdir1' must exist in the root.
-- IFSFILE('/subdir1/subdir1a/report') IFSEXT(.csv)
'/subdir1/subdir1a/report.csv'
Because a leading / exists, the
'subdir1/subdir1a' directory must
exist in the root.
IFSEXT The extension to be placed on the IFS name. The
default is '.csv' for a 'comma separated' file. A
different extension may be entered.
*NONE may be entered to use the IFS name only. See
the examples for IFSNAME.
STMFOPT Whether to clear the stream file if it exists.
*NONE is the default. If the IFS object exists,
escape message TAA9891 will be sent. If the IFS
object does not exist, it will be created.
*REPLACE may be specified to replace the existing
data in an IFS object. If the object does not
exist, it will be added.
TOFILE The qualified file name to write the records to that
have been selected.
The default is to use the TAASPLFDTA file in QTEMP.
If it does not exist, it will be created with a
length of 378 (the widest length spooled file
supported by the system). If the TAASPLFDTA file
does exist, it is deleted and then re-created in
QTEMP to ensure a clean version.
The library defaults to QTEMP. A specific library,
*LIBL, or *CURLIB may be specified.
If a file is named (other than TAASPLFDTA in QTEMP),
the file must exist.
TOFILEMBR The member of the TOFILE to write to. The default
is *FIRST. If a member is named and does not exist,
it will be added.
MBROPT Whether to clear the To file member before writing
records.
*NONE is the default. If the TOFILE parameter
identifies TAASPLFDTA in QTEMP, the file is always
re-created in QTEMP and the MBROPT value is ignored.
If the member is added (see TOFILEMBR parameter),
the MBROPT value is ignored.
*NONE is the default which will cause an error if
the member already exists.
*REPLACE may be specified to replace the data in the
existing member.
*ADD may be specified to add records to an existing
member.
PRINT The type of spooled output to be generated to help
identify the 'include' and 'omit' processing. The
typical use would be to use *BOTH until your
processing is correct and then specify *NONE.
*BOTH is the default meaning both the INCLUDES and
OMITS spooled files will be generated.
*OMIT may be specified to output only the OMITS
spooled file.
*INCLUDE may be specified to output only the
INCLUDES spooled file.
*NONE may be specified to prevent any spooled file
output.
COLHDGFREQ Whether column headings should be included in the
output and if so how frequently. Column headings
are identified by using the COLHDLINES parameter.
See the previous documentation for an example of
this function.
*NONE is the default meaning no column headings will
be included in the output.
*ONCE may be specified to mean that only the first
set of rows of the spread sheet will contain the
column headings. The COLHDLINES parameter
determines the number of column heading lines.
A number between 20 and 100 may be entered to
describe the frequency in which the column headings
will appear as rows in the output.
COLHDLINES The lines that identify where the first set of
column headings appear in the spooled file when
viewed by DSPSPLF. DSPSPLF will not display blank
lines. See the previous documentation for an
example of this function.
If COLHDGFREQ is *ONCE or a number, describe the
first and last heading lines of the first set. Up
to 3 lines may be specified such as COLHDLINES(4 6).
If only one line of column headings exist, enter the
same line number for both the first and last line
such as COLHDLINES(4 4).
EXITPGM An optional exit program that will allow 1)
additional checking to see if a spooled line should
be included or omitted and 2) changes to the spooled
line data before including it in the output.
The default is *NONE for no exit program. If an
exit program is named, a library qualifier must also
be entered which may include the special values
*LIBL or *CURLIB.
Three parameters are passed to the Exit program.
See the previous documentation for the detail
discussion.
OMIT The conditions to omit a record. Any conditions
entered are 'ored' together meaning that if a record
satisfies any of the OMIT conditions it will be
omitted. Up to 100 entries may be made.
Each entry is a list describing 1) the From
position, 2) the To position, and 3) the characters
(or special values) to be tested for.
To compare for digits (0-9) enter *DIGIT.
To compare for blanks, enter *BLANK.
To compare for non-blanks, enter *NONBLANK.
To compare for a unique string of characters, enter
the string. The length of the string must match the
length specified by the From/To positions. If the
spooled listing has a lower case value to be
selected you must place quotes around the value such
as 'System'.
INCLUDE The conditions to include a record. Any conditions
entered are 'anded' together meaning that a record
must satisfy all of the INCLUDE conditions to be
included. Up to 100 entries may be made.
Each entry is a list describing 1) the From
position, 2) the To position, and 3) the characters
(or special values) to be tested for.
To compare for digits (0-9) enter *DIGIT.
To compare for blanks, enter *BLANK.
To compare for non-blanks, enter *NONBLANK.
To compare for a unique string of characters, enter
the string. The length of the string must match the
length specified by the From/To positions. If the
spooled listing has a lower case value to be
selected you must place quotes around the value such
as 'System'.
Restrictions
------------
Only spooled data that can be read by the system CPYSPLF command can
be used (this excludes files created by AFP).
Prerequisites
-------------
The following TAA Tools must be on your system:
CHKIFSE Check IFS entry
CHKOBJ3 Check object 3
CHKSPLF Check spooled file
CVTSPLNBR Convert spool number
EDTVAR Edit variable
EXTLST Extract list
HLRMVMSG HLL Remove message
MOVCHRDEC Move character to decimal
RTVDAT Retrieve date
RTVDBFA Retrieve data base file attributes
RTVSPLFA Retrieve spooled file attributes
RTVSYSVAL3 Retrieve system value 3
RTVVALA Retrieve value attributes
SNDCOMPMSG Send completion message
SNDESCINF Send escape information
SNDESCMSG Send escape message
SNDSTSMSG Send status message
WRTIFS Write IFS
WRTSRC Write source
Implementation
--------------
None, the tool is ready to use.
Objects used by the tool
------------------------
Object Type Attribute Src member Src file
------ ---- --------- ---------- ----------
CPYSPLFDTA *CMD TAASPOA QATTCMD
TAASPOAC *PGM CLP TAASPOAC QATTCL
TAASPOAR *PGM RPG TAASPOAR QATTRPG
|
