Question
Rochdi Badis · Jul 5

Exporting to Excel with %DisplayFormatted

#Ensemble

Hi Guys,

I'm using the below to export a query result to excel file and the only file type is .csv (100) but the resulting has two problems,

first the row header is including the field type and second the resulting file is a tab delimited so columns aren't properly separated (see attached below) 

set st = ##class(%SQL.Statement).%New(2,"Sample")
set sql = "select ID, name from MyClass"
do st.%Prepare(.sql)
set type="csv"
  set rs = st.%Execute()
  do rs.%DisplayFormatted(type,"C:\Temp\report")
 

 

 

Thanks

 

Product version: Caché 2014.1
0
0 83
Discussion (5)1
Log in or sign up to continue
Robert Cemper · Jul 5

reading the content of  Docs:

I''d susggest you use either

     set type=100

or the correct case as suggested by documentations

    set type="CSV"
 

0 0
Rochdi Badis · Jul 5

Thanks Robert but if you check my code above I already used csv and also tried 100 and still the same I get a tab delimited file rather than comma delimited that makes  the columns glued to each other as in all columns as in one column (see attached below)

see the file in a notepad

and also the double quotes doesn't seems to work, the heading still includes the datatype 

 Thanks

0 0
Robert Cemper · Jul 5

I took a look into Caché 2018.
%DisplayFormatted is still useless for your case.

It calls the method %Display() which allows having a different delimiter:
It just doesn't use or support it. It is forgotten until today.
So default $C(9) "TAB" is used.
You may need to open the output file yourself and then 
use it with a %Next Loop writing it line by line. 
So you may also control your headers.

It looks like this method was not foreseen to be used in real applications.
 

0 0
Robert Cemper · Jul 5

in addition,  you may force your column headers in the SQL statement

set sql = "select ID as ""row"", name as ""myname"" from MyClass"

0 0