Re: question: format an excel file (.xls) inside sqr program

[richard.mitchell@GSA.GOV]

Format the columns of the receiving spreadsheet as 'Text' before loading
the data. This will cause the zeroes to be retained.

Hth,

Rick





"James Womeldorf" <jwomeldo@FASTENAL.COM>@list.iex.net> on 01/08/2001
08:25:00 AM

Please respond to sqr-users@list.iex.net

Sent by:  "Discussion of SQR, Brio Technology's database reporting
      language" <SQR-USERS@list.iex.net>


To:   SQR-USERS@list.iex.net
cc:    (bcc: Richard L. Mitchell/CONTRACTOR/10P/R10/GSA/GOV)

Subject:  Re: question: format an excel file (.xls) inside sqr program


I have had some trouble with tab separated files with data strings like
00010
which may represent a part number and therefore requires the retention of
the leading zeros.
Excel interprets them as numbers and chops off the leading zeros.
Does anyone know a way to get this to work properly so the leading zeros
are
retained in the spreadsheet?


-----Original Message-----
From: Bohan, Kathy [mailto:Kathy.Bohan@FMR.COM]
Sent: Monday, January 08, 2001 10:13 AM
To: SQR-USERS@list.iex.net
Subject: Re: question: format an excel file (.xls) inside sqr program


This is how I create excel files from within an SQR:

Begin-Program
 Let $_file ='c:\pen_earnings.xls'
 Open $_file as 10 for-writing record=32767:vary

    Do Main

 Close 10
End-Program

Begin-Procedure Main
Begin-Select
 encode '<009>' into $sep    !<tab>

fbpe.earn_q  &cy_pen_earn
 let #cy_pen_earn = #cy_pen_earn + &cy_pen_earn
 move #cy_pen_earn to $cy_pen_earn 99999999.99

 string $ssn $cy_pen_earn by $sep into $text
   write 10 from $text

>From  dbo.t_fbpe_part_earn fbpe
Where fbpe.clnt_id_n = 716362
 AND  fbpe.pay_prd_end_d between '01/01/2000' and '12/31/2000'
 AND  fbpe.earn_type_c = 'PEN'
 AND  fbpe.rec_upd_type_c <> 'D'

End-Select
End-Procedure



> -----Original Message-----
> From: Lambert, Jin [SMTP:JLambert@HRBLOCK.COM]
> Sent: Monday, January 08, 2001 10:32 AM
> To:   SQR-USERS@list.iex.net
> Subject:      question:  format an excel file (.xls) inside sqr program
>
> I have a question and hope some of you have accomplished such and can
> share
> your knowledge with me...
>
> We are developing a SQR program, which, generates a report for the end
> users.  We proposed the report be formatted as .csv file so the user can
> open it as an excel.  However the users insist that they want the excel
> file
> format (.xls) instead of .csv file format.  Is there anyway such request
> can
> be accomplished via a SQR program?
>
> Jin Lambert
> PeopleSoft Financial Team
> H&R Block
> voice:       (816) 932-7529
> fax:          (603) 687-4815
> e-mail:      JLambert@hrblock.com