What is the best method for exporting channel entries as a spreadsheet?

Question

My client has asked me to export all data as a spreadsheet. This includes playa and matrix data. What method would you suggest for accomplishing this?

Answer 1

For the sake of completeness you can create a spreadsheet by outputting a simple HTML table.

You can use regular {exp:channel:entries} tag loops to output your table rows and some PHP as shown in another answer to get this to download as an Excel file rather than be rendered in the browser.

<?php
    header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=myFile.xls");
header("Pragma: no-cache");
header("Expires: 0");
?>
<table>
<tbody>
{exp:channel:entries channel='myChannel' limit='500' disable="categories|member_data|pagination|trackbacks" status="not Closed"}
    <tr>
            <td>{first-field}</td>
            <td>{second-field}</td>
            <td>{third-field}</td>
            <td>{you-get-the-idea}</td>
    </tr>
{/exp:channel:entries}
</tbody>
</table>

Answer 2

How much data? I am not a programmer, so I would accomplish this with a template - here's an example. PHP is on on output.

<?php
$output_file='nameofmyexport.csv';
header("Content-type: application/download");
header('Content-Disposition: attachment;filename="' . $output_file . '"');
?>  
"Item name","Title","Date","Some data","Status"
{exp:channel:entries limit="500" channel="channel-name" disable="categories|member_data|pagination|trackbacks" status="not Closed"}"{item-name}","{title}","{entry_date format="%F %d %Y"}","{some-data}","{status}"
{/exp:channel:entries}

This is just an edited example of a template that I have in use, yours would likely have more content fields and it would output your playa and matrix data. For the CSV, you have to have all of your tags squished together so that the output is in one row. Your hard returns have significance. This always requires a bit of trial and error for me.

If I needed a very large quantity of data, I'd need to paginate this into multiple files. I have a limit here of 500 but that may be too much for some situations.

Answer 3

I've done with just using a template and the native channels tags. You can configure it to any output you'd like. Then just saves the results to a file. I used XML since you can nest nodes (ie. matrix fields) and it's easy to import back in with DataGrab

Use the limit and offset params as needed

<?xml version="1.0" encoding="UTF-8"?>
<products>
{exp:channel:entries
   channel="products"
   disable="category_fields|member_data|pagination|trackbacks"
   dynamic="no"
   limit="150"
   offset="450"
   require_entry="yes"
}
   <product>
      <name>{title}</name>
      <categories>{categories backspace="2"}{category_name}, {/categories}</categories>
      <model_num>{product_ci_model}</model_num>
      <product_description>{if product_description}<![CDATA[{product_description}]]>{/if}</product_description>
      <product_sizes>
         {product_sizes}
         <item>
            <model>{model_num}</model>
            <length>{model_length}</length>
            <width>{model_width}</width>
            <height>{model_height}</height>
         </item>
         {/product_sizes}
      </product_sizes>
      <colorchart_file>{product_color_chart}</colorchart_file>
      <images>
      {product_images}
         <item>
            <alt>{img_alt}</alt>
            <file>{img_file}</file>
         </item>
      {/product_images}
      </images>
      <product_size_headings>
         {product_size_headings}
         <item>
            <length>{heading_length}</length>
            <width>{heading_width}</width>
            <height>{heading_height}</height>
         </item>
         {/product_size_headings}
      </product_size_headings>
      <youtube_url>{youtube_video}</youtube_url>
   </product>
{/exp:channel:entries}
</products>

Answer 4

I've had great success with Export It. It handles all manner of third party fieldtypes, and support is phenomenal. The cost is worth the time it will save you in trying to construct an error-free export template.

Answer 5

In addition to foamcow answer (which is exactly how I do it). Here's some extra fruity code.

• Add a date to the file name.
• IE can cause problems.
• As it's just HTML, use the segment as a 'switch' to output as a page or download file. Handy when building your export.

>    $date = date('Y-m-d_G-i');
>    $export=$IN->fetch_uri_segment('3');
>       
>   if($export)
>   {       
>       if (strstr($_SERVER['HTTP_USER_AGENT'], "MSIE"))
>       {
>           header('Content-Type: application/octet-stream');
>           header('Content-Disposition: inline; filename="custom_name_'.$date.'.xls"');
>           header('Expires: 0');
>           header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
>           header('Pragma: public');
>       } 
>       else 
>       {
>           header('Content-Type: application/octet-stream');
>           header('Content-Disposition: attachment; filename="custom_name_'.$date.'.xls"');
>           header('Expires: 0');
>           header('Pragma: no-cache');
>       }
>   }
>?>

Answer 6

Is this just channel data?

I've always been a fan of using Zenbu for displaying channel data in the control panel, and now Zenbu Studios has released Hokuku (http://zenbustudio.com/software/hokoku) which allows for exporting saved Zenbu searches in different formats, including CSV.