Question
Rochdi Badis · Nov 16

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 .

 

Thanks

 

Product version: Caché 2014.1
00
1 0 9 82
Log in or sign up to continue

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

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

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

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.

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

Thanks

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.

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