Solution to AS/400-to-Excel downloading problem

[David Morrison - Corporate]

> Thanks, everyone.  I received several helpful tips.  We used the ideas and
> came up with a little different solution.
> 
> This technique only works for QTP, as you can't append to an existing
> temporary subfile, in QUIZ.
> 
> 1. Have a lead-in request that puts desired column headings into temporary
> fields that are the same length as the permanent and temporary fields that
> will be output later.  Need a 1-record file as the primary (and only)
> input file:
>                                               
> TEMPORARY x_company_number  CHARACTER * 3     
>   ITEM x_company_number  = "CO#"              
>                                               
> TEMPORARY x_location_number CHARACTER * 3     
>   ITEM x_location_number = "LOC"              
>                                               
> TEMPORARY x_customer_number CHARACTER * 7     
>   ITEM x_customer_number = "CUST NO"          
>                                               
> TEMPORARY x_customer_name   CHARACTER * 30    
>   ITEM x_customer_name   = "CUSTOMER NAME"    
> 
> 2. Output these fields to a subfile:
>                                                                   
> SUBFILE SAR7106  TEMPORARY                       &   
>        INCLUDE x_company_number,                      &   
>         x_location_number,                                     &   
>         x_customer_number,                                   &   
> 	  x_customer_name
> 
> 	3. In the main request, output to this subfile in APPEND mode.  Any
> numeric fields must be first converted to alphanumerics:
> 
> 	SUBFILE SAR7106  TEMPORARY  APPEND                            &   
> 	        AT customer_number  OF sar_customer_master            &   
> 	     IF t_total_sales GE g_sales_from                         &   
> 	INCLUDE company_number      OF sar_customer_master,           &   
> 	        location_number     OF sar_customer_master,           &   
> 	        customer_number     OF sar_customer_master,           &   
> 	        customer_name       OF sar_customer_master
> 
> 	Note that the names don't need to match up from the first and second
> requests (the lengths do, of course).  They COULD, by using the data
> dictionary (and temporary) field names of the main request in the lead-in
> request OR by using ALIAS.
> 
> 	The resulting file, when downloaded to Excel, has the usual F0000,
> F0002, F0004, etc. in the first row, and our column headings in the second
> row.  The Excel user can then delete the first row, and they are ready to
> go.
> 
> 	Thanks again, and enjoy your weekend.
> 
> 	David Morrison
> 
> 
> 	> 
> 	> 
> 	> 
> 	> -----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
> 	> 
> 	> 
> 	>