Re: Creating an Excel input file from an SQR?

[Michael Lee <homestoremike@YAHOO.COM>]

Hi Betty,
All my reports are written out in CSV format.  The
users I support do not like .LIS files and want the
ability to be able to copy, paste, edit, and sort
online... so Excel-readable files are the preferred
method for output.

I write all my files the same way as Stephen Keen's
method, however, I do add one additional item.  Since
some text fields contain commas, that affects CSV
files.

So I would suggest adding a double-quote at the
beginning and end of text fields which may contain
commas.

   let $Double_Quote = '"'

   write 2 from $Double_Quote:1
                $BA
                $Double_Quote:1
                $Comma:1
                $Double_Quote:1
                &A.JOBTITLE
                $Double_Quote:1
                $Comma:1
                $Double_Quote:1
                &A.NAME
                $Double_Quote:1

Also, most users like to have a header at the top to
describe the columns.  You can write the header at the
beginning of your problem, outside your select loop.
However, if you want it bolded or underlined, you
probably have to open the file and bold/underline it
yourself... then save in .XLS format.  I haven't been
able to find a way to send a file from SQR to EXCEL
and include tags to bold or underline.

Michael K. Lee
Senior HRIS Systems Analyst
Homestore

--- Stephen Keen <stephen_keen@SQR-SERVICES.COM>
wrote:
> 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


__________________________________________________
Do You Yahoo!?
Yahoo! Greetings - Send FREE e-cards for every occasion!
http://greetings.yahoo.com