Re: Comma Delimited Files

[Stan Quick <stanley.quick@BRIO.COM>]

The -EH_CSV flag scrapes your report and turns it into a CSV file.
What most people really want just the data, and usually more data than
is shown on the report.

If you are using ReportBuilder, you can get a good start by choosing the
"CSV" output
type when building the report( Edit>Preferences>Navigation Bar>Export to
CSV).  This
will put the following kind of code into your report:

Begin-Procedure CreateCSV_Master_Query
 Open 'MyReport.csv' as 1 for-writing record=32767:vary
 let $brb_csv_data =  '"Column1"'
 let $brb_csv_data = $brb_csv_data || ',' ||  '"Column2"'
 let $brb_csv_data = $brb_csv_data || ',' ||  '"Column3"'
 let $brb_csv_data = $brb_csv_data || ',' ||  '"Column4"'
 let $brb_csv_data = $brb_csv_data || ',' ||  '"Column5"'
 write 1 from $brb_csv_data
End-Procedure

begin-procedure main
        do CreateCSV_Master_Query
begin-select
Column1
Column2
Column3
Column4
Column5
 let $brb_csv_data =  '"' || &Column1 || '"'
 let $brb_csv_data = $brb_csv_data || ',' ||  '"' || &Column2 || '"'
 let $Master_Query_INPRIC_num_tmp = edit(&Column3,'9')
 let $Master_Query_INPRIC_num = ltrim($Master_Query_INPRIC_num_tmp,' ')
 let $brb_csv_data = $brb_csv_data || ',' || $Master_Query_INPRIC_num
 let $Master_Query_INCOST_num_tmp = edit(&Column4,'9')
 let $Master_Query_INCOST_num = ltrim($Master_Query_INCOST_num_tmp,' ')
 let $brb_csv_data = $brb_csv_data || ',' || $Master_Query_INCOST_num
 let $Master_Query_INUOM_num_tmp = edit(&Column5,'9')
 let $Master_Query_INUOM_num = ltrim($Master_Query_INUOM_num_tmp,' ')
 let $brb_csv_data = $brb_csv_data || ',' || $Master_Query_INUOM_num
 write 1 from $brb_csv_data

etc.

You can cleanup the code generated, but you get the idea.

Stan

"He's dead, Jim"   :)


-----Original Message-----
From: Tim Wilkinson [mailto:Tim.Wilkinson@HANCORP.CO.UK]
Sent: Thursday, May 10, 2001 11:40 AM
To: SQR-USERS@list.iex.net
Subject: Comma Delimited Files


Hi all,
       I've now gone from a newbie novice to 42 SQR reports under my belt,
(well 41.75 if my boss read this ;) ) and now they are asking for one to be
output to .csv format!

Now I understand that this requires a command line flag to output to .csv
(-EH_CSV). However, what I would like to know before I kill myself
formatting and looking up how to output in tiny fonts (as its a lot of data
for one line) is does all this information have to be on one line (below a
header) as I suspect or can it be on numerous lines and if so, what are the
implications of this.

I know that with a couple of hours trial and effort I can find all this out
myself, but I would be obliged if someone who has gone through this would
enlighten me and save me a couple of hours of trial and error (I have to get
this particular report out yesterday as per usual). Any hints/tips gleaned
by battle-hardened pros would be appreciated as well.

Once again I feel like the crew of NCC-1701, boldly going where no sane
person has gone before :-)

Thanks in advance,
                  Tim


Tim Wilkinson BSc. Dip Comp.
Analyst Programmer
Hansen Corporation Europe Ltd

Email:  Tim.Wilkinson@hancorp.co.uk
Phone:  01904 566017