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

[James Womeldorf <jwomeldo@FASTENAL.COM>]

That would work except that I need to have the user just double-click on the
file and have it come up properly in Excel (like it used to).  I do not use
any 'receiving spreadsheet'.

-----Original Message-----
From: richard.mitchell@GSA.GOV [mailto:richard.mitchell@GSA.GOV]
Sent: Monday, January 08, 2001 10:43 AM
To: SQR-USERS@list.iex.net
Subject: Re: question: format an excel file (.xls) inside sqr program


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