The Copy to CSV command copies from an externally described data base
file to a stream file and adds delimiters. The intent is to make a
stream file for a spread sheet processor. Options exist to add
delimiters at the end of fields, surround fields with quotes, and to
include column headings.
The function is similar to the system CPYTOIMPF command.
CPYTOCSV uses the CPYTOSTMF default for the AUT parameter to set the
authority to a new stream file. If the stream file exists, no
authority change occurs.
A typical command to read file ABC and output to a stream file would
be:
CPYTOCSV FILE(ABC)
The stream file would be named ABC.csv and would exist in the current
directory (normally the home directory).
The default is to use the DDS column heading as the first 1-3 records
in the stream file depending on the number of column headings that
exist. In the USA, the normal default is to use a comma (',') as the
separation character between fields. The default is to add the
double quote character surrounding each non-decimal data field.
Use WRKLNK to review the data in the stream file.
Valid field types
-----------------
The following field types are valid
A Character
W Variable length
P Packed
S Zoned (Signed)
B Binary
L Date
T Time
Z Timestamp
Non-supported field types may be omitted.
If the field type is not supported and not omitted, an escape message
will occur.
Sequence of the data
--------------------
If a keyed file is specified, the data will be written in keyed
sequence.
If a non-keyed file is specified, the data is written in arrival
sequence.
Delimited file data
-------------------
Spread sheets are typically designed to work with delimited data such
as fields separated by either a comma (',') in the USA or a period
('.') in some other countries. In the following discussion, the
comma is used as a separator. No comma is placed after the last
field.
** The default for CPYTOCSV is to add a double quote character
surrounding the non-decimal data fields so the data would
appear as:
"abc","def",123
This assumes that the 123 value is from either a packed.
zoned, or binary externally described field. Had the 123
value existed in a character field, it would have been
surrounded with the double quote character.
Using either the double quote or single quote character allows data
such as
Thomas Jones, Jr.
to appear with the comma.
** If a character field is all blank, no blanks will appear in
the output so the data may appear as
"abc","",123
** If a decimal field is zero, one zero will appear in the field
such as
"def",0,"ghi",456
** There is an exception made when ADDQUOTE(*SINGLE) is specified
and a single quote also exists in the data such as:
Tom's truck
In this case, the single quote would be doubled and the data
would appear as
'Tom''s truck'
** There is an exception made when ADDQUOTE(*DOUBLE) is specified
and a double quote also exists in the data such as:
Bob"s truck
In this case, two double quotes would be inserted and the data
would appear as
"Bob""s truck"
** Decimal points in spread sheet processors are normally allowed
to exist (period in the US and comma in some other countries).
The US form is used in the following examples.
CPYTOCSV adds a decimal point as the default if a field has
decimal positions. Packed, zoned, and binary fields are
supported.
If the DDS description of a field is 9 digits with 2 decimal
positions, the leading zeros would be stripped off and a
decimal point inserted such as:
12345.l7
** A zero value in a decimal field with no decimal positions will
appear as a single zero. If two decimal positions existed, a
zero value would appear as '.00'. A negative value will
appear with a minus sign to the left such as '-123.45'.
Sample output
-------------
Assume the following DDS definitions:
Name Type Length Column headings
---- ---- ------ --------------------
FLD1 A 5 'Good' 'field' 'one'
FLD2 P 5 2 'Amount'
FLD3 P 3 0 'Qty' 'used'
Assume the data is as follows:
FLD2 FLD2 FLD3
---- ---- ----
ABC 300 22
DEF 4562 179
0 0
GH,I 24 15-
Assume the defaults were used for CPYTOCSV. The data would appear
as:
"Good","",""
"field","","Qty"
"one","Amount","used"
"ABC","3.00","22"
"DEF","45.62","179"
"",.00,0
"GH,I",".24","-15"
CPYTOCSV escape messages you can monitor for
--------------------------------------------
TAA9891 The member has no records
TAA9892 The stream file object already exists
and *REPLACE was not specified
Escape messages from based on functions will be re-sent.
CPYTOCSV Command parameters *CMD
---------------------------
FILE The qualified name of the externally described data
base file to be processed. The library value
defaults to *LIBL. A specific library or *CURLIB
may also be used.
MBR The member to be processed. *FIRST is the default.
*LAST or a specific member may be specified.
COLHDG The column headings to optionally appear as records
1-3 in the stream file.
*COLHDG is the default to use the DDS column
headings described for each field.
If any fields have 3 lines of column headings, the
first 3 lines of output will be the column headings.
If some field has 3 fields of column heading and
another field has only 2 lines of column headings,
the first line of the 3 lines of output will be
blank for that field.
If the maximum number of column headings for any
field to be converted is 2, then only 2 lines of
column headings will be output.
*FLDNAM may be specified to output one line of
column headings which will contain the name of the
field.
*NONE may be specified to prevent any column
headings in the converted data.
OMITFLD A list of up 100 fields that should be omitted for
the conversion. Omitting a field could be used when
the data is not needed in the spread sheet or the
field type is not supported.
ADDQUOTE The default is *DOUBLE meaning that the double quote
character (") will surround the data for each
character field. This allows data such as 'Tom's
truck' to remain as is.
*SINGLE may be specified to surround the fields with
single quotes. For example, 'abc','123',... would
be generated.
There is an exception made if you request to add a
quote (using *SINGLE) and a value such as 'Tom's
truck' appears. In this case the quote would be
doubled and the data would appear as 'Tom''s truck'.
ADDDECCHR A *YES/*NO value for whether to add a decimal point
to a decimal field that has decimal positions
greater than zero.
*YES is the default to add the character which is
determined by the use of the job's decimal format
(normally a period in the USA).
*NO may be specified to prevent the character from
appearing.
ADDDELIMTR The default is *DFT which will add the standard
separator as a field delimiter surrounding character
fields. This normally results in data being
generated as "abc","deg","ghi". 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.
IFSFILE The name of the IFS file to write to.
The default is *FILE which means the same name as
the data base file.
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 data base file name is FILEA.
-- IFSFILE(*FILE) IFSEXT('.csv)
'/home/usera/FILEA.csv'
Since both the IFSFILE and IFSEXT values
are the same as the defaults, they
need not have been specified.
-- IFSFILE('FILEA.csv') IFSEXT(*NONE)
'/home/usera/FILEA.csv'
-- IFSFILE('FILEA.txt') IFSEXT(*NONE)
'/home/usera/FILEA.txt'
-- IFSFILE('FILEA.txt') IFSEXT(.csv)
'/home/usera/FILEA.txt.csv'
-- IFSFILE('subdir1/FILEA') IFSEXT(.csv)
'/home/usera/subdir1.FILEA.csv'
Because no leading / exists, the 'subdir1'
directory must be within the user's
current directory.
-- IFSFILE('/subdir1/FILEA') IFSEXT(.csv)
'/subdir1/FILEA.csv'
Because a leading / exists, the directory
'subdir1' must exist in the root.
-- IFSFILE('/subdir1/subdir1a/FILEA') IFSEXT(.csv)
'/subdir1/subdir1a/FILEA.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 IFSFILE.
STMFCCSID The code page to be used when determining the the
stream file coded character set identifier (CCSID).
The value is passed thru to the same parameter on
the CPYTOSTMF command. For a full explanation, see
the CPYTOSTMF help text.
*STMF is the default. If the stream file exists,
the CCSID associated with the stream file is used.
If the stream file does not exist, the CCSID from
the data base file is used.
*PCASCII may be specified to mean the Microsoft
Windows (trademark of Microsoft Corp) encoding
scheme.
*STDASCII may be specified to mean the IBM PC data
encoding scheme.
1-65533 for a specific value that must match the
stream file if it exists.
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.
Restrictions
------------
The record size input may not exceed 9,999 bytes.
The record size of the stream file may not exceed 9,999 bytes
including the separator characters, quotes, decimal notation etc.
Only field types A (Character), W (Variable length), P (Packed), S
(Signed/Zoned), B (Binary), L (Date), T (Time), and Z (Timestamp) are
supported.
Null values are not supported.
The size of the output character field cannot exceed 2009 bytes
including any added quote and double quote characters.
The size of a decimal field cannot exceed 15 digits.
Prerequisites
-------------
The following TAA Tools must be on your system:
CHKDBFMBR Check data base file member
CHKIFSE Check IFS entry
EDTVAR Edit variable
EXTLST Extract list
EXTLST2 Extract list
HLRMVMSG HLL remove message
RSNLSTMSG Resend last message
RTVDBFA Retrieve data base file attributes
RTVFLDA Retrieve field attributes
RTVFLDARR Retrieve field array
RTVVALA Retrieve value attributes
SNDCOMPMSG Send completion message
SNDESCINF Send escape information
SNDESCMSG Send escape message
SNDSTSMSG Send status message
Implementation
--------------
None, the tool is ready to use.
Objects used by the tool
------------------------
Object Type Attribute Src member Src file
------ ---- --------- ---------- ----------
CPYTOCSV *CMD TAADBMW QATTCMD
TAADBMWC *PGM CLP TAADBMWC QATTCL
TAADBMWR *PGM RPG TAADBMWR QATTRPG
|
