Re: Creating an Excel input file from an SQR?

[Stephen Keen <stephen_keen@SQR-SERVICES.COM>]

Hey Betty,

Instead of writing to a txt file have the extension as 'csv'

The following is header row procedure

  Let $comma = ','

 Write 5 from    'emplid':5
                        $comma:1
                        'First Name':10
                        $comma:1

the following is data procedure

  Let $comma = ','
Write 5 from    $emplid:10
                        $comma:1
                        $First_Name:35
                        $comma:1



Does this make sense?  When the user opens the file from excel It's already
formatted.

Hth




Stephen Keen

 -----Original Message-----
From:   Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net]  On Behalf Of Wang, Chris
Sent:   Thursday, February 28, 2002 5:44 PM
To:     SQR-USERS@list.iex.net
Subject:        Re: Creating an Excel input file from an SQR?

don't know the answer, but one suggestion


 string 'Emplid' '2011' '2013' ..... by ';' into $string

 write 2 from $string



-----Original Message-----
From: Betty Thompson [mailto:thompsob@MAIL.UWW.EDU]
Sent: Thursday, February 28, 2002 4: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