6

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.

7

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.

  • Thanks Swarad - that looks like just the thing. My concern is installing a module (having never done it before) on a live server. Is anything likely to break? Are there any gotchas, or can I confidently just 'install' and be certain that nothing is going to break? – Dan Sep 4 '12 at 12:14
  • Am I correct in saying that Views XLS isn't compatible with version 7.8 of Drupal? In which case it's not going to work? – Dan Sep 4 '12 at 12:45
  • Views XLS has a 1.0 release for 7.x, what am I missing ? – Kimi Sep 19 '14 at 12:08
  • 1
    Usually nothing breaks when you install a module, but you can never be sure. At least you want to make a database backup before you do anything. The safest way is to clone the site and run it on a local webserver, and use that to install modules and export data. Where did you read about the 7.8 version incompatibility? Seriously: if you're still running Drupal 7.8, perform a Drupal core security update NOW. – marcvangend Oct 20 '15 at 11:38
5

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

2

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:

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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