How to export data from drupal to Excel?

Question

I've inherited a Drupal site. I need to extract the main page data out of the site to be able to import it into a different system, but I've never used Drupal and the site is live so I need to tread carefully in order to extract the data without affecting the site.

Currently the website has lots of content pages for manufacturers. Each manufacturer has a name, description, some images and other (mainly text) data. Say, for example, there are 2000 of these manufacturers. What I'd really like to be able to do is export an Excel file (or XML file, whatever) so each field has a different column in the spreadsheet, and each drupal page (i.e. each manufacturer) is a different row in the export spreadsheet. Ideally the images will be exported as full URLs in a column in the spreadsheet.

When I log into Drupal and click the 'content' button at the top, it gives me a list of every page on the site. I can filter this to only show 'Manufacturer' pages. When I edit a specific manufacturer page it gives me a form containing all of the editable fields for that manufacturer, prepopulated with the data to edit. I just need this information for each manufacturer dumped out into a spreadsheet.

So as a complete Drupal noob, where would I start in doing this? Would I just go straight to the database and try to piece it together (if so, is the data stored in a single table, or will there be some major queries involved in doing this - any pointers about which tables to look at would be greatly received) or is there a tool for getting a bulk export of this kind of thing? I guess I could even create a web page which displayed this information in an HTML table and just scrape it from there, but ideally an export would be nice.

Answer 1

You are in luck! There is a module for that. (I love saying that!)

1. Views Excel Export
2. Views

This is an extension for Views module, which pretty much can list any data from Drupal's DB; users, content, comments and any other entities. You will have to install the Views module and then install the Views Excel Export module which will export the list generated by Views to an Excel file. Without any code! Brilliant isn't it?

If you need instructions on how to install a module in Drupal, you can follow Installing modules (Drupal 7) on Drupal documentation.

Answer 2

There is also Views Data Export. It supports many data formats including Excel and CSV. It also depends on Views. It is designed to handle extremely large datasets by batching the export process but producing one file.

Views Data Export

Answer 3

Another option to consider might be the Forena module, which also comes with quite some community documentation.

Forena is built of the idea of using SQL to get data out of a database (which can be the Drupal database or an external one such as My SQL, Oracle, MS SQL, ...) and use XHTML and CSS to format it into web reports. There are quite some reasons for considering Forena. Visit the Forena HowTos page for some links (near the bottom) to some live demo / showcase links.

Any report can be saved (exported) in various file formats, such as CSV or XLS. To do so, just add .csv as an extention to the report (output) URL. That contains the CSV equivalent of that report. As a sample, consider the sample report located at /reports/sample.states (in your own site, one of the samples shipped with Forena). Change the URL to /reports/sample.states.csv (= just add .csv to it) to get the same report in .csv format. If you'd add such .csv to the URL of the Simple Table of States in the demo site, the result is like so ...

Same technique applies for other supported extensions (export formats), such as adding .html, .xls (MS Excell), .pdf or .doc (MS Word).

By the way, all data blocks are accessible directly via their url also, provided you have permission to access that block. Here are a few samples of that, for the sample report located at /reports/sample.states:

• in XML format (= demo with anonymous access).
• in JSON format (= demo with anonymous access).
• in XML format (= demo without anonymous access).
• in JSON format (= demo without anonymous access).

For short, to answer this specific answer I'd write the various (custom) SQLs I need, format it as reports that fit my needs, and then just save it as an XLS (or CSV?).

Disclosure: I'm a co-maintainer of this module,
I hope this does not violate the site's policy on self-promotion.