Re: Export data format question

[CRPence <CRPbottle@xxxxxxxxx>]

  FWiW: An example of a simple SQL CREATE VIEW that easily enables the 
export of the database file member data with the stated formatting 
requirements.  The preceding action is merely setup to mimic what might 
represent an existing file, the first action following is an export 
sample naming the source of the data as the previously created view plus 
showing specifications for both record delimiter of the CRLF and the 
fixed-length field data layout, and finally an edit of the output file 
and the resulting output to show what the recipient should get.

<code>

  create table qtemp/custcdt as
  (select char(cusnum) as cusnum, lstnam from qiws/qcustcdt)
  with data
  ; /* establish a two-column table with trailing blanks  */

  create view qtemp/custcdt$ as
  (select a.*, char('$') as eol from qtemp/custcdt a
   /* only to limit data in example: */ where lstnam>'L')
  ; /* add a column with dollar sign data per requirement */

  cpytoimpf fromfile(qtemp/custcdt$) tostmf(custcdt$.fixlen)
      mbropt(*replace) RCDDLM(*CRLF) DTAFMT(*FIXED)
  /* generate fixed-length export with '$'+CRLF as suffix */

  edtf custcdt$.fixlen /* output shown here: */

      ....+....1....+....2
     ************Beginning of data**************
      392859  Vine    $
      397267  Tyron   $
      389572  Stevens $
      693829  Thomas  $
      593029  Williams$
      192837  Lee     $
     ************End of Data********************

</code>

  Note: while the data in the example matches "arrival sequence" of the 
physical data, the export utility uses SQL whereby the output set is 
unordered.

Regards, Chuck

On 10-Jan-2012 17:38 , jmmckee flinthills.com wrote:
> Kind of what I was thinking was going on. At one point, using
> CPYTOPCD, I chose *NOTEXT and had the CR/LF. Nice fixed length
> record. Rejected solely due to lack of the dollar sign. Picky,
> picky. <<SNIP>>
>
> On Tue, Jan 10, 2012 at 6:53 PM, Mark S. Waterbury wrote:
> > Sounds like they are processing the data on a PC DOS (or Windows)
> > system ... the idea for adding a "$" (or any non-blank character)
> > to the end of the record is to preserve the "fixed length" fields
> > --otherwise, some software blank trims trailing blanks when
> > reading "lines" of data from a text file (hence the requirement for
> > the CR/LF)...
> >
> > Does that help?
> >
> > On 1/10/2012 6:45 PM, jmmckee flinthills.com wrote:
> > > A vendor wants a record with fixed length fields, followed by a
> > > dollar sign AND a CR/LF.
> > >
> > > Leave off either the CR/LF or the dollar sign, and record is
> > > rejected.
> > >
> > > I don't know if the site uses SQL to parse out the data.  I'm
> > > just curious if there could be some valid reason for a record
> > > delimiter and the CR/LF sequence.