Drupal - How to export data from drupal to Excel?
You are in luck! There is a module for that. (I love saying that!)
- Views Excel Export
- 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.
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
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.