Exporting to Excel with %DisplayFormatted
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
reading the content of Docs:
set type=100
or the correct case as suggested by documentations
set type="CSV"
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
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.
in addition, you may force your column headers in the SQL statement
set sql = "select ID as ""row"", name as ""myname"" from MyClass"
Check this or this.