Exporting and Importing Drupal Nodes

The Hassle of Updating Mass Content

Before 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 and 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.