AS/400-to-Excel downloading

[Bruce Schuck]

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


= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to
powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.