Re: Creating an Excel input file from an SQR?

["Grewal, Navi A." <navi.grewal@EXELONCORP.COM>]

Hi Betty,
 Here is a code from one of my sqr that has output in excel spreadsheet.
You open the file, write and then close it. Let me know if you have any
questions.
HTH
Navi Grewal

begin-procedure Open-File
   Open 'c:\temp\qbonusn.csv' as 1 for-writing record=250
   status = #Filestat
   if #Filestat <> 0
      display 'Unsuccessful opening the output file'
   end-if
end-procedure

begin-procedure Get-Review-Info
begin-Select
E.REVIEW_RATING
E.EFFDT
     let $rating = &E.REVIEW_RATING
     let $rdate = &E.EFFDT
FROM PS_EMPLOYEE_REVIEW E
WHERE   E.EMPLID = $emplid
AND     E.EFFDT   = (SELECT MAX(EFFDT)
                       FROM PS_EMPLOYEE_REVIEW
                      WHERE EMPLID    = $emplid)
end-SELECT
end-procedure

begin-procedure Write-Employee-Record
   let $amount1 = edit(#reghours, '09999.99')
   let $amount2 = edit(#othours, '09999.99')
   let $amount3 = edit(#tothours, '09999.99')
   let $amount4 = edit(#otahours, '09999.99')
   let $amount5 = edit(#otbhours, '09999.99')
   let $amount6 = edit(#stdhours, '09999.99')
   Write 1 from $dept           -
                $comma          -
                $name           -
                $comma          -
                $emplid         -
                $comma          -
                $ssn            -
                $comma          -
                $plan           -
                $comma          -
                $amount1        -
                $comma          -
                $amount2        -
                $comma          -
                $amount3        -
                $comma          -
                $amount4        -
                $comma          -
                $amount5        -
                $comma          -
                $wc             -
                $comma          -
                $desc           -
                $comma          -
                $termdate       -
                $comma          -
                $fullpt         -
                $comma          -
                $amount6        -
                $comma          -
                $rating         -
                $comma          -
                $rdate
end-procedure

begin-procedure Close-File
   close 1
end-procedure
-----Original Message-----
From: Betty Thompson [mailto:thompsob@MAIL.UWW.EDU]
Sent: Thursday, February 28, 2002 5:34 PM
To: SQR-USERS@list.iex.net
Subject: Creating an Excel input file from an SQR?


I support financials, and I discovered my users were cutting and pasting
most of their SQR reports into spreadsheets, rather than using the hard-copy
output we were sending them.

I've begun giving them their report data in .txt files, using a semi-colon
as a field delimiter (a brief example is shown below).  My users are
delighted, but I think this is still too clumsy.

I want to actually write a file out in Excel format (like the PS Query tool
does it, passing along the column heading row).

I would appreciate any information about accomplishing this, or suggestions
as to where this might be documented.

Thanks.

Betty
Univ of WI - Whitewater

----------------------------------------------------------------------------
--------------------
Example of what I've been doing:

 ! open file and write heading labels in the text file to be loaded to Excel

    Let $Filename4 = 'C:\TEMP\PSTEST\WWSF1098_FOR_EXCEL.TXT

    OPEN $FileName4 as 4 FOR-WRITING RECORD=300:Vary status = #filestat

    Let $Excel_Header = 'Emplid'   || ';'  ||  ! $Emplid
                        '2011'     || ';'  ||  ! Spring amt
                        '2013'     || ';'  ||  ! Summer amt
                        '2017'     || ';'  ||  ! Fall amt
                        'OTHER'    || ';'  ||  ! Other amt
                        'Total'    || ';'      ! sum total
    Write  4  from $Excel_Header


   .... in loop...
 ! Produce the Excel file.

      let #New_Total = #New_2011 + #New_2013 + #New_2017 + #New_OTHER
      let $New_2011   = edit(#New_2011,'9999999.99')
      let $New_2013   = edit(#New_2013,'9999999.99')
      let $New_2017   = edit(#New_2017,'9999999.99')
      let $New_OTHER  = edit(#New_OTHER,'9999999.99')
      let $New_Total  = edit(#Total,'9999999.99')

      let $Excel_Record =    $Emplid    || ';'  ||  ! $Emplid
                             $Old_2011  || ';'  ||  ! Spring amt
                             $New_2013  || ';'  ||  ! Summer amt
                             $New_2017  || ';'  ||  ! Fall amt
                             $New_OTHER || ';'  ||  ! Other amt
                             $New_Total || ';'      !   sum total

      WRITE  4  from   $Excel_Record


************************************************************************
This e-mail and any of its attachments may contain Exelon Corporation
proprietary information, which is privileged, confidential, or subject
to copyright belonging to the Exelon Corporation family of Companies.
This e-mail is intended solely for the use of the individual or entity
to which it is addressed.  If you are not the intended recipient of this
e-mail, you are hereby notified that any dissemination, distribution,
copying, or action taken in relation to the contents of and attachments
to this e-mail is strictly prohibited and may be unlawful.  If you have
received this e-mail in error, please notify the sender immediately and
permanently delete the original and any copy of this e-mail and any
printout. Thank You.
************************************************************************