AS/400-to-Excel downloading

[SAWYER, Anthony]

This is a multi-part message in MIME format.

------=_NextPartTM-000-1b20d790-4335-4f11-bcca-d97379c83b12
Content-type: text/plain;	charset="iso-8859-1"

We managed to get around the 255 limit by using QTP to create a
'nodictionary' subfile (.DAT)

You have to include a carriage return at the end of each record otherwise
Excel will read it as one immense line though

You can create a heading line in one request and append data to it in the
next.  We use global temporaries to hold the delimiters to prevent having to
recalculate it every time

On compiling it throws up countless duplicate field name warnings for the
delimiters (we are using a semi colon below in g_separator), but these can
be ignored in this case.

I'm not certain that the method for getting the carriage return works for
the AS/400 though - this might need a little work...

HTH, Regards

Tony 


e.g. (VMS, 8.20D)

run one

glo tem g_separator char*1 init ";"
glo tem g_num int*2 init 13
glo tem g_cr char*1 init ascii(g_num)[2:1]

req one

access small_file

temp field_a char*20
temp field_b char*20
temp field_c char*20

item field_a = "Heading 1"
item field_b = "Heading 2"
item field_c = "Heading 3"

subfile subfile_name keep nodict at final include &
	field_a, &
	g_separator, &
	field_b, &
	g_separator, &
	field_c, &
	g_cr

req two

access detail_file

subfile subfile_name keep append nodict include &
	field_a, &
	g_separator, &
	field_b, &
	g_separator, &
	field_c, &
	g_cr

bui qtp_file

-----Original Message-----
From: Pickering, John (NORBORD) [mailto:PICKERIJ@norbord.com]
Sent: 12 July 2002 14:36
To: 'Bruce Schuck'; 'David Morrison - Corporate';
powerh-l@lists.swau.edu
Subject: RE: AS/400-to-Excel downloading


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

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

------=_NextPartTM-000-1b20d790-4335-4f11-bcca-d97379c83b12
Content-Type: text/plain;
	name="InterScan_Disclaimer.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="InterScan_Disclaimer.txt"

The information in this e-mail is confidential and is intended solely for the use of the individual or entity to which it is addressed. Any views or opinions presented are those of the author and do not necessarily represent those of MFI Furniture Group PLC or its associated companies.

------=_NextPartTM-000-1b20d790-4335-4f11-bcca-d97379c83b12--