Export Zen Report or Query to a CSV file

Question

Question

Rochdi Badis · Nov 16, 2021

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 .

Thanks

Product version: Caché 2014.1

Discussion (9)1

Log in or sign up to continue

Robert Cemper · Nov 16, 2021

you may try this approach: https://openexchange.intersystems.com/package/Light-weight-EXCEL-download

using separators instead or <tr><td> tags should not be a big issue,
same for the appropriate document type.

0 0

David Hockenbroch · Nov 16, 2021

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.

0 0

Marc Mundt · Nov 16, 2021

If you already have a Zen report, you can change it to output as an Excel spreadsheet. This will work if the report is called from a web browser just as it does for PDFs.

If you don't have an existing Zen report, then I don't recommend creating a new Zen report. Use one of the approaches others are suggesting.

0 0

score 0 · Answer 1 · 2021-11-16T06:32:51-05:00

Hi,

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") }

score 0 · Answer 2 · 2021-11-16T06:35:51-05:00

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")
}

score 0 · Answer 3 · 2021-11-16T06:38:58-05:00

I've seen this sample, but this one will save the file to a specific path, I'm looking for a way to popup and open the file in my screen, or have the option to Open/Save file in my browser, the same way you get in websites?

Thanks

score 0 · Answer 4 · 2021-11-16T09:37:36-05:00

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.

score 0 · Answer 5 · 2021-11-17T06:16:56-05:00

Rochdi Badis · Nov 17, 2021

Thanks David, I'll try this and let you know.

Thanks

0 0

score 0 · Answer 6 · 2021-11-17T08:20:44-05:00

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).

Thanks