Hi,

There already exists a Couch addon for importing data into Couch from CSV files (viewtopic.php?f=5&t=8803).

The reverse of it (i.e. exporting data out from Couch into a CSV file), thankfully, is not that complex and so can be done without the help of an addon. The process is discussed at viewtopic.php?f=3&t=8157#p14781 (essentially just using the correct content type and then using <cms:pages> to output the rows).

There are, however, two cases where the solution as shown in the mentioned post can result in creating invalid CSV files (i.e. not RFC 4180 compliant) -
1. The data in a column happens to contain comma or newlines.
RFC 4180 mandates enclosing such data using double-quotes.

2. The data itself contains double-quotes (which, as we saw in the point above is meant to enclose contents containing the problem characters).
RFC 4180 mandates such double-quotes to be escaped (by "doubling" the double-quotes).

To make this process of making the data RFC 4180 compliant, I have created a little tag <cms:format_csv />.
Please add the following code in your 'couch/addons/kfunctions.php' file (if this file is not found, rename the 'kfunctions.example.php' to this name) -
Code: Select all
// Tag <cms:format_csv />
// formats enclosed contents to make them RFC 4180 valid for a csv file
$FUNCS->register_tag( 'format_csv', 'my_format_csv_handler' );
function my_format_csv_handler( $params, $node ){
    $enclosure = '"';
    $delimiter = ',';

    $content = '';
    if( count($node->children) ){ // if used as a tag-pair, get the enclosed contents ..
        foreach( $node->children as $child ){
            $content .= $child->get_HTML();
        }
    }
    else{ // the first parameter is the content
        $content = $params[0]['rhs'];
    }

    // format contents
    if(
        strchr($content, $delimiter) !== false ||
        strchr($content, $enclosure) !== false ||
        strchr($content, "\n") !== false ||
        strchr($content, "\r") !== false ){

        $content = str_replace( $enclosure, $enclosure.$enclosure, $content ); // escape double-quotes within contents
        $content = $enclosure . $content . $enclosure; // enclose contents in double-quotes
    }

    return $content;
}

Now, if instead of <cms:show />, we use the <cms:format_csv /> tag instead it will format the value to make it CSV compliant.

As an example, suppose we have template named 'cars.php' having the following editable regions
Code: Select all
<cms:editable name='year' label='Year' type='text' />
<cms:editable name='make' label='Make' type='text' />
<cms:editable name='model' label='Model' type='text' />
<cms:editable name='description' label='Description' type='textarea' no_xss_check='1' />
<cms:editable name='price' label='Price' type='text' search_type='decimal' />

The following code will output data from this template in valid CSV format -
Code: Select all
<?php require_once('couch/cms.php'); ?>
<cms:content_type 'text/csv' />
<cms:pages masterpage='cars.php' order='asc'>
    <cms:if k_paginated_top >
        Year,Make,Model,Description,Price
    </cms:if>
    <cms:format_csv year/>,<cms:format_csv make/>,<cms:format_csv model/>,<cms:format_csv description/>,<cms:format_csv price/>
</cms:pages>
<?php COUCH::invoke(); ?>


Writing into CSV files
Sometimes the requirement is to write the CSV data into a file (as opposed to outputting it directly through the browser, as we did above). To help with that, we can use the <cms:write /> tag discussed here - viewtopic.php?f=8&t=11377.

For example, the following code will create a CSV file named 'my.csv' (in the site's root) containing the same data as our previous code was outputting -
Code: Select all
<cms:pages masterpage='cars.php'  order='asc'>
    <cms:if k_paginated_top >
        <!-- Header. 'truncate' starts a new file -->
        <cms:write 'my.csv' add_newline='1' truncate='1'>Year,Make,Model,Description,Price</cms:write>
    </cms:if>
    <cms:write 'my.csv' add_newline='1'><cms:format_csv year/>,<cms:format_csv make/>,<cms:format_csv model/>,<cms:format_csv description/>,<cms:format_csv price/></cms:write>
</cms:pages>

Creating very large CSV files
In all our code samples above, we used <cms:pages> to iterate through all the cloned pages of our template in a single go.
For a smaller number of pages, this is perfectly fine but if the number of pages happens to be big we could run into performance issues described at
viewtopic.php?f=5&t=8803

The same thread also describes a method to iterate through the large page-set in manageable smaller chunks (in a "staggered" manner, as it terms it).
We can adapt the same staggered method to export data into CSV files.
Following is a full sample template that demonstrates that -
Code: Select all
<?php require_once( 'couch/cms.php' ); ?>

    <cms:template title='CSV exporter' hidden='1' />

    <cms:set mystart="<cms:gpc 'import' method='get' />" />
   
    <cms:if mystart >
   
        <cms:pages
            masterpage='cars.php'
            paginate='1'
            limit='100'
            order='asc'
        >
       
            <cms:if k_paginated_top >
                <cms:if k_current_page='1'>
                    <!-- Header. 'truncate' starts a new file -->
                    <cms:write 'my.csv' add_newline='1' truncate='1'>Year,Make,Model,Description,Price</cms:write>
                </cms:if>
           
                <cms:if k_paginate_link_next >
                    <script language="JavaScript" type="text/javascript">
                        var myVar;
                        myVar = window.setTimeout( 'location.href="<cms:show k_paginate_link_next />";', 100 );
                    </script>
                    <button onclick="clearTimeout(myVar);">Stop</button>
                <cms:else />
                    <cms:set write_footer='1' />
                    Done!   
                </cms:if>
               
                <h3><cms:show k_current_page /> / <cms:show k_total_pages /> pages (Total <cms:show k_total_records /> records. Showing <cms:show k_paginate_limit /> records per page)</hr>
            </cms:if>
           
                <h3><cms:show k_current_record /></h3>
               
                <!-- CSV row -->
                <cms:write 'my.csv' add_newline='1'><cms:format_csv year/>,<cms:format_csv make/>,<cms:format_csv model/>,<cms:format_csv description/>,<cms:format_csv price/></cms:write>

            <cms:if k_paginated_bottom >
                <hr>
               
                <!-- Footer -->
                <cms:if write_footer>
                    <!-- CSV does not require a footer so doing nothing here but for XML this could be used to output the document closing tags -->
                <cms:else />
                    <cms:paginator simple='1' />
                </cms:if>   
            </cms:if>
           
        </cms:pages>   
    <cms:else/>
        <button onclick='location.href="<cms:add_querystring k_page_link 'import=1' />"'>Start!</button>
    </cms:if>

<?php COUCH::invoke(); ?>

Hope this helps.