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) -
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
The following code will output data from this template in valid CSV format -
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 -
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 -
Hope this helps.
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.