TAA Tools
CPYTOCSV        COPY TO CSV                            TAADBMW

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

             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

If  a non-keyed  file  is specified,  the  data is  written in  arrival

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

  **   The default  for CPYTOCSV  is to  add a  double quote  character
       surrounding  the  non-decimal  data fields  so  the  data  would
       appear as:


       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


  **   If a decimal field  is zero, one zero  will appear in the  field
       such as


  **   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

       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:


  **   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


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

                 *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

   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

                 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)


                          Since both the IFSFILE and IFSEXT values
                          are the same as the defaults, they
                          need not have been specified.

                 -- IFSFILE('FILEA.csv') IFSEXT(*NONE)


                 -- IFSFILE('FILEA.txt') IFSEXT(*NONE)


                 -- IFSFILE('FILEA.txt') IFSEXT(.csv)


                 -- IFSFILE('subdir1/FILEA') IFSEXT(.csv)


                          Because no leading / exists, the 'subdir1'
                          directory must be within the user's
                          current directory.

                 -- IFSFILE('/subdir1/FILEA') IFSEXT(.csv)


                         Because a leading / exists, the directory
                         'subdir1' must exist in the root.

                 -- IFSFILE('/subdir1/subdir1a/FILEA') IFSEXT(.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

                 *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.


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

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.


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


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

Added to TAA Productivity tools July 15, 2010

Home Page Up to Top