Views data exports

Views is the king in Drupal land when it comes to making lists of your data, rightly so, it does a wonderful job of making it easy to choose the data you want to list. It also allows you to render your list in various forms, lists, tables. RSS feeds or even jump menus. So if you want to make a list and render it as CSV or XML, then you naturally turn to views, and find that currently you really have two options:

• The export module in Views Bonus Pack
• Views Datasource

Let me talk about Views Datasource before I return to Views Bonus. Datasource was a module that came from the Google's Summer of Code 2008, and allows exporting a view to lots of different formats including XML and JSON (though not CSV). It is geared around providing the data from views to external tools, for example a flash applet that could display a list of the latest news could get that list from views using this module. It is not geared towards downloading large exports of data. But potentially is a module to watch.

Views Bonus Pack includes an export module that provides several style plugins for the feed display, so that instead of rendering a RSS feed, views can render a CSV file. There are some really great features, like being able to inherit the parent displays sorts and once you get the hang of what the options mean, it does work really well, to a point.

If you need to export all of your data into CSV, say you want a report of all users and their profiles on your site, you may run into issues. Views bonus will cope if you have a few hundred users, and might just about manage a couple of thousand rows, but you soon hit issues with PHP's time and memory limits. Views bonus is fundamentally architected to retrieve the entire result set into memory, and render it in one go using Drupal's theme layer. This approach just doesn't scale and means that while everything starts out okay, eventually if your site grows enough, you run into this issue.

There was some discussion on drupal.org about how to solve this issue: http://drupal.org/node/805960. In which I outlined an approach that might work:

Use batch API to query and render the results of the view in managable parts.

When we needed this functionality for a client, we decided to take the plunge and try to build this. We needed to completely re-factor the way that the Views Bonus style plugins did their rendering and we created a new display that ran the whole show. Exports could now be huge, think millions of rows, and be exported succesfully in a matter of minutes. We shared our progress by working in a github repository: https://github.com/computerminds/views_bonus. However, it became clear that this new work was a major departure from the simplicity of Views bonus, and we did not have good indications from the maintainer that our work would make it back into the module, so we decided to fork.

We took our bits from Views bonus, and re-factored pretty much everything so that it was more streamlined, and completely seperate. We also took the chance to add a few more features and make things a little more readable in the code.

The result of a lot of time and hard thinking is the:

Views data export module.

In essence, it allows you to export data from your Drupal site to CSV/XLS/XML/etc. No limits on amount of data.

I just want to highlight a few of the features and inner-workings.

• Dedicated display - There is now a dedicated display for data exports, making the setup process much clearer. If you can build a normal view, you can build a view that can export a million nodes into XLS format.
• Batch API - When you follow a link to the data export view, you are shown a progress bar as your export data file is built up, and then you are automatically redirected to the download page. We even try to guess how long your export might take to complete, so you know if you have time to go make a cup of tea or not.
• Index - We use some scary looking SQL to capture the results of the views query into a temporary table. This means that if your underlying data changes, in most cases, your export will be unaffected. E.g. if you are exporting all users, and a user is deleted during the export, the user will still appear in the export.
• Drush integration - You can run the exports from drush and save the results to a file. Maybe you'll need this for reporting to some external tool, say a CSV of all users at mid-night every day.
• Migration - We have a lot of views that were set up to use Views Bonus and its style plugins, but they are getting near or over PHP resource limits, so we wrote a migration module that makes it super easy to convert from Views Bonus to Views Data Export.

Hopefully, the next time you need to export some data from a Drupal site into CSV you'll use Views Data Export, and not have to hope that your export completes in under 60 seconds using less than 128MB of memory.