Question
Rochdi Badis · Jul 29

Export zen page to CSV

Hi guys,

I've a zen page report that I would like to export to .csv file, any ideas on how to do that?

 

Thanks

 

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

Hello Rochdi,

Have you resolved this? If not, are you using a report that is derived from %ZEN.Report.reportPage? If you are, you can use the "GenerateReport()" instance method to create an "xlsx" Excel file, which can then be exported to ".csv" after opening it from Excel.

https://docs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cls?...

Alternatively, the following code highlights how to generate a CSV file containing the results of a query:

ClassMethod ExportCSV(/* some args */) As %String [ ZenMethod ]
{
/* get a results set named "rs" from a query (sql statement, or, in this example a query class):
Set rs=##class(%ResultSet).%New("<some query class")
Do rs.Prepare()
Do rs.Execute(/*some args*/)
*/
// Define the delimeter to be used in CSV file (usually comma)
Set Delim = ","

// Define the file and its name
Set File = ##class(%FileCharacterStream).%New()
Set Filename = "filename.csv"
Set File.BOM = $C(239,187,191)
Set File.Filename = "C:\temp\"_Filename 
Set File.TranslateTable = "UTF8" 
// Define the names of the columns (should match up with the values in each row of the result 
// set. In this example, there are 3 columns
Set FileHeader = $LB("Name","Rank","","SerialNumber")

Do File.WriteLine($ZCVT($LTS(FileHeader, Delim), "O", "UTF8")) 
While rs.%Next()
{
    Set Row = ""
    For = 1:1:$LL(FileHeader) 
    {
        Set col = $LG(FileHeader, i) 
        Set Data = rs.%Get(col)
        // Replace characters that may mess up the CSV file (e.g., embedded commas in a data field)
        Set Data = $REPLACE(Data, $C(13), " ")
        Set Data = $REPLACE(Data, $C(10), " ")
        Set Data = $REPLACE(Data, """", "'")
        Set:(Data [ ",") Data = """"_Data_"""" 
        Set Data = $ZCVT(Data, "O", "UTF8") 
        // Add the datum to the row's list
        Set Row = Row_$LB(Data)
     } 
     // Each element in the "row list" is written, separated by "Delim" (comma) on one line 
     // in the file 
     Do File.WriteLine($LTS(Row, Delim))
} 
// Set attributes of the file for easier reading by the right apps later.
Do File.SetAttribute("ContentType","application/octet-stream; charset=utf-8")
Do File.SetAttribute("ContentDisposition","attachment; filename="""_Filename_"""")
Do File.SetAttribute("Expires",600)
    Do File.SetAttribute("Content-Length",File.Size)
        
    Do File.%Save()
 // Return the name of the full path to the CSV file
 Quit File.Filename
}

Please confirm if this is helpful or if you have other questions.

Thank you 

Yes thank you very much Jean

See %SQL.StatementResult:%DisplayFormatted()

Simple sample (for namespace "SAMPLES"):

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

As a result, the following files are generated:

report.csv
report.html
report.pdf
report.txt
report.xml