AS/400-to-Excel downloading

[Pickering, John (NORBORD)]

Rather than this messy way, how about using plain old every day Quiz?

access file
set report device disc name somename limit 111111
set page length 0 width 255
page heading keep column headings
report stuff1 stuff2 etc
go

Setting the page length to zero will result in a single page heading at the
start of the file. You'll probably want to override any dictionary headings
if they're more than a single line. Any you'll likely get a blank line
before the column headings and after.

And of course all of this assumes that you can get your data into 255
characters. I'd love to see this limit increased. 

While I'm wishing for things I'd also like to see options to create tab
delimited and/or comma separated output formats. 

Regards,
JWP

> -----Original Message-----
> From:	Bruce Schuck [SMTP:Bruce@Schuck.com]
> Sent:	Friday, July 12, 2002 12:40 AM
> To:	'David Morrison - Corporate'; powerh-l@lists.swau.edu
> Subject:	RE: AS/400-to-Excel downloading
> 
> Do you need to use subfiles?
> 
> If the data can fit in a 255 char field I do something like this in
> quiz:
> 
> Access file
> Set report name reportname
> Set report nolimit
> Set nohead
> Set rep spacing 0
> Set page length 1 width 255
> 
> Define d-Tab char*1 = char(34) ; guessing this on
> 
> Define initialhead char*255 = "Heading1" + d-tab + "heading2" + d-tab
> etc
> 
> Initial head initialhead
> 
> Report field1 d-tab field2 d-tab     etc
> 
> go
> 
> Excel likes tab delimited files.
> 
> If your records are greater than 255, in the VMS world, I would create a
> dictionary record with 1 element 2047 chars wide, variable length, and
> then
> Use qtp to append to an empty copy. The location of the file would
> Be set to sys$login:temp2047.dat and in the com file I would do a create
> To create a new copy each time the job was run.
> 
> 
> And then qtp would output lines just like the ones I used in the quiz
> example.
> 
> Or you can quickbol if you like.
> 
> 
> 
> 
> -----Original Message-----
> From: powerh-l-admin@cube.swau.edu [mailto:powerh-l-admin@cube.swau.edu]
> On Behalf Of David Morrison - Corporate
> Sent: Thursday, July 11, 2002 4:24 PM
> To: 'powerh-l@lists.swau.edu'
> Subject: AS/400-to-Excel downloading
> 
> Hello, all
> 
> We currently download several different subfiles (created by both QTP
> and
> QUIZ programs) from our AS/400, on a regular basis, for users who want
> to be
> able to "slice and dice" data in MS-Excel.  The frequency and variety of
> these downloads is trending upwords.  We use IBM's Client Access to
> perform
> the downloads (we get the same result if we use ODBC to import data into
> Excel).
> 
> We want to be able to include column headings as the first row of the
> Excel
> spreadsheets (currently, we have to hand-enter them, in some cases).
> The
> default headings for such columns are "F0000", "F0002", "F0004", etc.
> If we
> use the DDS FIELD clause on the ELEMENT statements in the data
> dictionary,
> and reload the associated data files, we can make our own DDS field
> names
> that successfully download to Excel.  However, when we create subfiles
> in
> QTP or QUIZ FROM these dictionary files, the field names are lost, and
> we
> get just the default heading names, as above.
> 
> Outside of specifying all the possible subfiles within our data
> dictionary,
> does anyone out there have an idea for how we can accomplish what we
> want to
> do?
> 
> Note that we're on an older CISC (V3R2) model AS/400.
> 
> Thanks.
> 
> David Morrison
> 
> 
>