We Rock Your Web in the Press!
Home > Tech > Web Development > Exporting and Importing Drupal Nodes

Exporting and Importing Drupal Nodes

Drupal exporting and importing nodesBefore Drupal, I used development environments where I could comfortably update snippets of content on multiple pages (at times literally hundreds) in seconds. Since delving into the CMS (Content Management Systems) world, and particularly Drupal, I have become fond of all the features, and especially the support community, that CMS users benefit from. However, I wasn’t able to find a way to easily update all my nodes. Say I have some code snippet or tag or what node embedded in nodes that I want to change; or I would like to add something quickly to the content of each node – I would need to log-in and manually edit each node within the CMS interface.

The Import Export Module

Drupal’s import_export module promised to solve all this. Alas, it is optimized for version 4.6, and despite using the form_updater module to update the forms to 4.7′s API, I was still not able to get the module working.

The Import/ Export API SOC (Summer of Code) Project

Next I excitedly read that indeed there’s a team working on core Drupal import/ export functionality (read about the Import/ Export API)! Excellent :) Unfortunately for me, it will probably not be ready until the end of summer. And there were vital changes to my site (specifically, each node) that I needed to make asap.

My System Specs and Disclaimer

Before we begin, here’s the system I’m running: Drupal 4.7.3, mySQL 4.0.27-standard, PHP 4.4.2, and phpMyAdmin 2.6.4-pl2. I encourage you to backup your databases before attempting any of the below suggestions, and to put your site offline while you make the update since other content publishers may make changes to the content nodes, and therefore the node_revisions table we will be working with, prior to you restoring it. While these steps worked for me, they may not work for you, and I do not make any guarantees as to the correctness of this information nor will I be held responsible for any problems you encounter as a result of its use. If unsure, and especially if dealing with sensitive and/or important data, do not attempt.

Backing up Drupal

Before proceeding I recommend you backup your Drupal installation. I wrote a tutorial on using phpMyAdmin to backup Drupal installations if you need it: Backing Up Drupal. Since I recently had troubles using this approach (SQL parsing errors) I’m going to recommend you download the Drupal DBA module and backup your installation via this as well (you should be able to do anything in phpMyAdmin that the module is capable of doing, since you are directly accessing the database, but for some reason I’ve had troubles backing up with phpMyAdmin recently). You could always try the phpMyAdmin approach and attempt to restore the database. If it fails, proceed with the DBA module.

Mass Updating Drupal Nodes Locally

The solution? The easiest solution is to simply export the node_revisions table. Then open the SQL file up in an html editor that allows mass updating of code snippets. My html/css editor of choice is TopStyle (made by the creator of HomeSite), but unfortunately it currently only allows updating of one line of text at a time. So I use the old, no-longer supported HomeSite, in which I can replace large, multiline snippets of code in entire directories or code projects, specifying file extensions and more.

The Beauty of Mass Content Updating in HomeSite

Simply select Extended Replace in HomeSite (Search » Extended Replace, or Shift-Ctrl-R) to open up a multi-line replace dialog that will let you copy and paste entire snippets of code to be replaced. You can also use regular expressions.

An Example of Content Updating

An example of some recent updating I did: I run divs within my content nodes that are controlled by my style sheet. I rarely change these, but recently I renamed one for compatibility purposes. While renaming the div in the stylesheet was simple enough, I wasn’t about to log-in and administer a few dozen nodes to change the divs. By downloading the node_revisions table and using HomeSite to replace the code snippets I made the update in seconds.

In a Perfect World

In a perfect world I can then simply upload the modified sql node_revisions file and voila! All my nodes have been updated.

The Problems I encountered

If the above steps worked for you, great! No need to read on. For those of you that are getting import errors (” there may be an error with the SQL parser “, etc.), here’s how I solved my dilemma. I installed the Drupal DBA module, and proceeded to backup my node_revisions table. This is where I ran into problems. Actually, it backed up fine, but when I tried re-importing it (via “run script”) I got a slew of errors and as a result the majority of my nodes got screwed up and I got the message “access denied.”

So I dive into the code to see what’s up. First I notice very strange character entries in the (0,0,0) entry of the node_revisions table – but I’m assuming that has something to do with the core installation, so I leave it alone. I don’t notice anything strange except that the nodes that failed to import tended to be longer than the others. I think maybe that’s a limitation/ problem with the DBA module, so I proceed to try to import the SQL file in phpMyAdmin. Voila! Works without any of the errors I got previously.

In a Nutshell

So that’s basically how I do it now – I’ve mass updated my nodes in seconds by exporting the node_revisions table using the Drupal database administrator. I then proceed to make changes locally using HomeSite. Once the updates are complete I import the table back via phpMyAdmin.


About Alex Schenker

Alex bring a series of in-depth articles on search marketing and content management systems as well as troubleshooting tips to We Rock Your Web's collection. He is an avid tennis player, nature enthusiast, and hiker, and enjoys spending time with his wife, friends, and dogs, Bella and Lily.
Previous:
Next:
  • Anonymous

    Check out my script here.

    You can use the forum import script as a base. This supports any database that PDO supports (so, you could use it with postres or mysql)

    Instead of looping over selects, as I did, you could loop over your CSV like this:

    $handle = fopen(“nodes.csv”, “r”);

    while (($data = fgetcsv($handle, 1000, “,”)) !== FALSE) {

    // insert info form $data here.

    }

    fclose($handle);

  • Anonymous

    I was on the Fleet Feet Fair Oaks web site and noticed you use Drupal. I’m rconstantine on Drupal.org. Anyway, I’m wondering what use cases you have where you need to modify/insert custom code into nodes at all, rather than using blocks or custom modules. With the extensibility of themes, it’s not too hard to insert a block holder (div) smack in the middle of your content. And if a theme is properly coded to collapse when a page doesn’t need to show such blocks, it’s easy to specify which pages show it and which don’t. You mentioned only briefly, above, what it is you do this all for and I’m interested to hear more. Could you elaborate on your use cases?

    • Alex

      Hi there,

      Thanks for your comment rconstantine. I completely agree with you that as few changes as possible should be made to Drupal code and existing modules/ core functionality should be used in lieu of hackish methods whenever possible.

      Back when I wrote this article I didn’t see a way of mass updating content. For instance, let’s say your site has your company name as “Example Company, Inc.” throughout and you want to change every instance of this (maybe on several hundred or thousand pages) to “EXC, Inc.,” The above method was the quickest way I found of doing this, as at the time there wasn’t a Drupal module that supported this functionality. Is there one now that does this/ or possibly a better method?

      Cheers,
      Alex

  • Anonymous

    Drupal is a great CMS. The only beef I have with it is the constant upgrading, which tends to break my site if I miss one step, and the fact that the forums blow! C’mon guys, there has to be demand for a better forum functionality out there. I’ll sponsor anyone that’s willing to come up with a forum plug-in :)

  • Anonymous

    Thanks for this info, this issue has been bugging me like crazy for the last couple of days :)