Drupal Articles » SQL queries to import from a custom CMS into Drupal


By David - Posted on 25 September 2008

The attached text file contains the custom SQL queries I wrote in order to convert and import the database of a custom-written CMS into Drupal. The original site is quite large, with approximately 20,000 users, 100,000 nodes, and 800,000 comments. All nodes are Forum nodes.

If you are converting a custom database to Drupal, some of these queries might be helpful to you, or provide a starting point for your own custom queries. If you're using a more common open-source CMS, then please check for pre-written conversion scripts, which are available for most popular systems.

These queries were run on a Mac OS X system, running MAMP. Most queries are executed through the Terminal app (you should not execute huge queries with phpMyAdmin). With some modification, you should be able to adapt these commands to work with WAMP or XAMPP if you use Windows, and run commands from the cmd prompt in Windows.

I'm posting this file for others to use, in the hope that it will be useful and help people avoid some of the struggle of preparing data from another system and importing it into Drupal. By using the information in the attached file, you agree that the risk is your own and I take no responsibility. Back up all of your data thoroughly before attempting anything, and work with a duplicate of your database. I also cannot help with your database conversion, beyond the content of this file.

The steps and queries are meant for Drupal 5, however only minor changes are needed for making it work with Drupal 6 - I included a few notes about the relevant changes to the database I'm aware of in Drupal 6, but you should look up a reference of Drupal's database tables to be sure (there's likely one on drupal.org, or if not then there is definitely one in the Pro Drupal Development book, which is what I referred to). In the not-distant-future I will be revising my steps for Drupal 6, but cannot guarantee when. I've chosen to attach the file instead of including the queries in a code box on the page, since the formatting of the code on the page was a bit broken, and I wasn't confident that the original content was left unmodified.

Enjoy!

AttachmentSize
drupal-import-sql-queries.txt18.63 KB

Do you have anything for importing my Drupal database from my host to my desktop server and a way to do the reverse? I kepting getting error when I tried ro upload it and I tried the command line method too but it kept saying it can't find the database. I'm using Apache2triad and phpmyadmin version is higher than 5.

I did something quite similar (actually I started from someone else's work) to convert from bbPress. I've been successful in getting the users, the forum nodes, and the comments all imported.

Getting Drupal's forum tool to display these nodes properly has been a challenge...

Can you give me some advice on importing User data? For example, I could use a reference to how drupal stores time or data information which may be different from my old system.

My import of 50k records from a custom CMS to a drupal multisite went nice and smooth! Obviously had to make some MySQL changes but the commentary and code examples saved me hours of work. I still have much to do in customizing things but a lot of the data is imported and the 'hard stuff' is out of the way. Thanks again!

Hmm, sorry, but i personally do not see need in any MySQL changes. Works nice. Thank you too.

Thanks a lot for this. I'm tackling a similar project and I'm sure this will be extremely helpful.

I recently converted a large IP.Board database to Drupal 6 including users and content and I found this to be extremely helpful.

Thank you for sharing your hard work.

Thank you for the sql querie, but can i use it from a wordpress blog ?