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?

up vote 13 down vote accepted

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>

  • 1
    This is much better than CSV since it will always be opened in Excel and it is not sensitive to line breaks in the content. I believe some other HTML tags also work such as bold, etc. – Isaac Raway Nov 27 '12 at 20:19
  • Ok thanks, I'm tentatively marking this as the answer, however <p> tags are being converted into multiple rows. I'd prefer it if Excel would just maintain the markup as is. – Richard Frank Nov 27 '12 at 20:49
  • Yes, bold tags will work too. I'm not guaranteeing that some html or other code within your data won't break it but generally it should be OK. I haven't done this for some time and I seem to recall sometimes having to fiddle with delimiting the cell contents with quotes. I don't think Excel will ever maintain the markup since it's not an HTML rendering tool... or do you mean display the html tags? – foamcow Nov 27 '12 at 21:08
  • Yes I mean keep the html tags so HTML content is actually preserved. This means the content can be re-imported into another CMS for instance. But more importantly, the body content should all sit in one cell rather than having multiple merged cells which is happening in Excel 2011 for Mac. – Richard Frank Nov 27 '12 at 21:48
  • Hmm, you may need to use the same technique to output a CSV file and use a delimiter around your cell content. You can then import this into Excel telling it what the delimiting string is. It should then display the tags as text within your cell. – foamcow Nov 27 '12 at 21:56

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.

  • 2
    You can do the same thing, but set the download file's extension to .xls and return a simple HTML <table> tag (without any tags around it). This will be opened by Excel as a spreadsheet, and could allow you some more control over the cells in the result than CSV since it won't be sensitive to line breaks in your content. – Isaac Raway Nov 27 '12 at 20:18
  • @Isaac, Great tip! – Alex Kendrick Jan 31 '13 at 16:57
  • Is there a good way to escape quotes that might be output for each field? For example, in Movable Type, you can do this on a template intended to be opened as a CSV: "<$mt:AuthorName replace='"','""'$>" – Charlie Gorichanaz Aug 21 '13 at 18:49

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.

  • does is support rendering out playa related content? – Richard Frank Nov 27 '12 at 21:49
  • 1
    It looks like it support outputting Playa titles and url_titles (see change log) ... but contact the dev maybe to see what else is possible? – Derek Hogue Nov 27 '12 at 21:54

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');
>       }
>   }
>?>

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.

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>
  • My question is specifically about exporting as a spreadsheet, but thanks for your input! – Richard Frank Nov 27 '12 at 19:35
  • Maybe someone else can chime in, but I'm not sure the "proper" way of nesting matrix fields inside a CSV column. That's why I chose XML. – Siebird Nov 27 '12 at 19:41
  • 1
    I'll probably go with pipe delimitation for the matrix stuff. Unfortunately my limitation is that it has to be a spreadsheet. I know XML is a better method for exporting data. – Richard Frank Nov 27 '12 at 19:45
  • Good to know. Thanks @RichardFrank – Siebird Nov 27 '12 at 19:46

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.