· Nov 16, 2021

Export Zen Report or Query to a CSV file

Hi Guys,

How can I download or populate a query resultset or a content or DataGrid or zen report to a CSV file ?

basically, I know how to create a csv file than save it in a local path eg. C:\Temp\filename.csv but this is not convenient for the user to go then a try to find the file, I would like the CSV file to be downloaded or opened for the user to view the same way for example when you go a website where you have an option to download or open a csv file from query resultset or a zen report .



Product version: Caché 2014.1
Discussion (9)1
Log in or sign up to continue


use  %SQL.StatementResult:%DisplayFormatted()
Please find below sample code(SAMPLE namespace):
set st ##class(%SQL.Statement).%New(2,"Sample") set sql = 3 set sql(1) = "select TOP 5 %ID as id, Name, DOB, Home_State" set sql(2) = "from Person where Age > 40" set sql(3) = "order by 2" do st.%Prepare(.sql) for type="txt","pdf","csv","html","xml" {   set rs st.%Execute()   do rs.%DisplayFormatted(type,"C:\Temp\report") }

You can create a custom REST web service, use Muhammed's approach to fetch the data, and then output the raw CSV as the response.

One complication here is that %DisplayFormatted only outputs to files. You could consider outputting to the file, then read that file and output it in your response, but in a web service it would be best to output to the response directly.

In that case, you might just iterate through the result set using %Next() and either use %Print(",") to output each row or build your own row by using %Get for each column to output and adding commas between columns.

Rochdi, when you say you've figured out how to save it to C:\Temp\filename.csv, are you saying you have it saved on the client, or on the server?

If it's saving the file on the server, you can create a class that extends %CSP.StreamServer, then override the OnPreHTTP and OnPage class methods like this for simple text files:

Class fileserver Extends %CSP.StreamServer
ClassMethod OnPreHTTP() As %Boolean
do %response.SetHeader("Content-Type","text/csv")
do %response.SetHeader("Content-Disposition","attachment;filename=""myfile.csv""")
quit 1
ClassMethod OnPage() As %Status
set file = ##class(%File).%New("/path/to/file.csv")
do file.Open("R")
while file.AtEnd '= 1{
write file.ReadLine(),!
quit $$$OK

and then just link to it that page to download.

Once you get to things that aren't plain text, it gets a little more complicated, but this should work for a simple csv.

It did work for me when I set the    DEFAULTMODE ="pdf" but didn't with   DEFAULTMODE ="xlsx", I think because the server doesn't have MS office, is there an Excel runtime utility that I can install? 

the other problem is that in all my zen reports the data are presented in a datagrid and changing the defaultmode ='"xlsx" will result in out of whack result (see for example the sample ZENReports.PageLayouts and try to change the defaultmode to xlsx and you will what I mean).