AS/400-to-Excel downloading

[Jeff Hoffman]

G'Day

Just to add my 2 bits worth.

If you want more then 256 chars, try this

Quiz will need to be two pass

; Pass 1
set def
can cle

def sepr char*1 = ";"
def eor_c int*2 = 13
def eor  char*1 = ascii(eor_c)[2:1] ; or [1:1] depending on platform

def rep_lym char*2000 = "col A" + sepr + "Col B" + &
                        sepr + "Col C" + sepr + eor

set subfile name xxxx keep
rep sum rep_lyn
go

;Pass 2

acc file

def sepr char*1 = ";"
def eor_c int*2 = 13
def eor  char*1 = ascii(eor_c)[2:1] ; or [1:1] depending on platform

def rep_lyn char*2000 = trunc(field_A) + sepr + &
                        ascii(field_B) + sepr + &
                        field_C + sepr + eor
set subfile name xxxx append
rep sum rep_lyn
go

U can do something similar for QTP.

Some nice transfer programs can remove the spaces at the end of the line
automatically, but if you do not have one of these the blank data will load
as a separate field at the end and can be ignored.

Jeff

                  
At 10:23 AM 12-07-02 -0400, you wrote:
>Just me being picky again, but why 2 requests?
>
>How about doing it in one request. Write the headings out "at initial" and
>append the data.
>
>access detail_file
>subfile subfile_name alias subfile_headings at intitial include &
>  <same item list>
>item field_a of subfile_headings final "Head A"
>item field_b of subfile_headings final "Head B"
>subfile subfile_name alias subfile_detail append include &
>  <same item list>
>
>Glad it's Friday,
>JWP
>
>> -----Original Message-----
>> From:	SAWYER, Anthony [SMTP:tony.sawyer@mfi.co.uk]
>> Sent:	Friday, July 12, 2002 10:12 AM
>> To:	'Pickering, John (NORBORD)'; 'Bruce Schuck'; 'David Morrison -
>> Corporate'; powerh-l@lists.swau.edu
>> Subject:	RE: AS/400-to-Excel downloading
>> 
>> 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
>> 

+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
+ Jeff Hoffman                          + e-mail: wonicon@optushome.com.au  +
+ Director                              +                                   +
+ WoniCon Pty Ltd   (ABN 93 083 502 630)+                          ,-_|\    +
+ 21 Prices Circuit                     + voice: +61 2 9542 1527  /     \   +
+ Woronora    NSW     2232              +                         \_,-._*   +
+ AUSTRALIA                             + mobile: 040 790 3929         v    +
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
+