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

["Kenny Melton (CORP)" <kenny.melton@CUMMINSUTILITY.COM>]

James,

If you enclose the data string in double quotes ("00010") Excel should treat
the data as character data and not numeric, thus retaining the leading
zeroes.

Hope this helps,

Kenny Melton
Manager of Development
Cummins Utility Supply

-----Original Message-----
From: James Womeldorf [mailto:jwomeldo@FASTENAL.COM]
Sent: Monday, January 08, 2001 10:25
To: SQR-USERS@list.iex.net
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