Forum OpenACS Q&A: csv download for Templating system, ad_table, List Builder?

Collapse
1: csv download for Templating system, ad_table, List Builder?
Posted by Andrew Piskorski on 06/11/03 06:27 PM
On many, many of the HTML tables I display via the OpenACS templating system, I would like to add a link to download everything shown in that HTML table as a CSV (comma-separated values) file. Does anyone have such functionality already working for OpenACS?

The same CSV download feature for ad_table would also be nice. (Despite the fact that ad_table is old and ugly and doesn't play nicely with the Templating system, it is often still useful...)

Collaboraid's spec. for their new List Builder specifically (which should make ad_table completely obsolete) lists CSV download as a feature, and List Builder is listed as "Currently being implemented". What's the status of List Builder? How far along is it, any idea when it'll be working well enough to use for table, when CSV download will be ready, etc.?

Collapse
2: Re: csv download for Templating system, ad_table, List Builder? (response to 1)
Posted by Dave Bauer on 06/11/03 06:52 PM

Andrew,

I was thinking of a template::util::multirow_to_csv sort of tcl proc.

Try something like this where there exists a multirow datasource called report_data. This is copied from a working page. 

if {[string equal $mode csv]} {
    upvar #[adp_level] report_data:columns columns
    set header_row ""
    set i 1
    foreach column $columns {
        append header_row "${column}"
        if {![string equal $i [llength $columns]]} {
            append header_row ","
        }
        incr i
    }
    append header_row "\r\n"
    set csv ""
    append csv "$header_row\r\n"
    template::multirow foreach report_data {
        set row ""
        set i 1
        foreach column $columns {
            append row "\"[util_escape_quotes_for_csv [subst $$column]]\""
            if {$i < [llength $columns]} {
                append row ","
            }
            incr i
        }
        append csv "${row}\r\n"
    }
#    ns_return 200 text/plain $csv
# uncomment the next two lines to download instead of view in browser
    ReturnHeaders "application/text"
    ns_write $csv"

    ad_script_abort
}

I created an index.vuh takes page_name.csv and rp_internal_redirects to page_name.tcl so that the filename shows up correctly for the downloader.

I am awating the list-builder myself. It will definitely make some tasks much easier.
Collapse
3: Re: csv download for Templating system, ad_table, List Builder? (response to 1)
Posted by Jeff Davis on 06/11/03 07:02 PM
I have a function to return a csv when given a query which was what I used with ad_table. There is some stuff I would change I think (like just uplevel rather than pass bind var names). Anyway, here it is... 
ad_proc -public query_csv {query_name query {binds {}}} {

    Build a csv file from a query with headings defined by the column names

    @author Jeff Davis (davis@arsdigita.com)

    @param query_name the name of the provided query
    @param query the actual query
    @param binds a list of bind variables to upvar from the callers environment

    @return the resulting table html fragment
} {
    foreach bind $binds {
        upvar $bind $bind
    }

    set out {}
    set row 0
    db_foreach $query_name $query -column_set results {
        if {! $row} {
            set sep {}
            for {set i 0} {$i < [ns_set size $results]} {incr i} {
            append out "$sep\"[util_escape_quotes_for_csv [string trim [ns_set key $results $i]]]\""
                set sep ","
            }
            append out "\n"
            incr row
        }

        set sep {}
        for {set i 0} {$i < [ns_set size $results]} {incr i} {
            set val [string trim [ns_set value $results $i]]
            if {[regexp {^[0-9]*\.?[0-9]*$} $val]} {
                append out "$sep$val"
            } else {
                append out "$sep\"[util_escape_quotes_for_csv $val]\""
            }
            set sep ","
        }
        append out "\n"
    }

    return $out
}