AS/400-to-Excel downloading

[Glenn Waters]

Another approach that I've used successfully  on various platforms(HP3000, 
UNIX etc..) I have NOT worked on the 400 in years, so this may not be 
appropriate there..

Similar to the response below

Access file
set report device disc name reportname
set nohead
set page width (platform dependent number, just something big like 255)
define spaces char*10 = "          " (ie 10 spaces)
...other defines as needed for your info

page heading "heading one" spaces "heading two" spaces (and so on....). You 
could also use "initial heading" if you only want one set of headings!
report    item1 spaces item2 spaces etc...
(this may require tweaking to get this right, but basically just use the 
spaces to insure the headings and data have at least some white space 
between each column)

Get the file to your PC, then open the file up in Excel. Everything should 
be at that point in column one, as long text strings. Go to data menu, use 
the "text to columns" choice. If you have the white space right just hit 
the finish button. This should move everything to its own column with the 
headings of your choice.

Hope this helps..


At 09:40 PM 7/11/2002 -0700, you wrote:
>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.
>
>
>
>
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>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.

Glenn Waters
Application Development Team Lead
NC State University- CVM

glenn_waters@ncsu.edu
(919)513-6652