Exporting Drupal Taxonomy For WordPress Using MySQL

If your Drupal website has a large taxonomy and especially if there are several vocabularies and/or there is a taxonomy hierarchy used, you can really streamline your migration to WordPress by manually importing your taxonomies.

One way to accomplish this is by exporting your existing Drupal taxonomies to CSV using phpMyAdmin then importing into the WordPress wp_term_taxonomy and wp_terms tables.

This guide assumes we’re working with a fresh installation of WordPress, so importing our Drupal term ids will likely not conflict with WordPress term ids.  Either way, it’s a good idea to backup your WordPress installation before proceeding just in case you need to roll things back.

You will need to create two MySQL queries to export your taxonomy data.  Sample queries are provided below.

What About Custom Drupal Vocabularies

Drupal allows you to create unlimited Vocabularies for your node content.  If you want to retain the Drupal Vocabulary structure, you will need to create custom WordPress categories.

Step One

Run either of the following queries to get the data we need for wp_term_taxonomy.  Please note, if you are fine with importing all your taxonomies under the default WordPress taxonomy “Category”, use the first query.  Otherwise, to import your custom taxonomies using Drupal’s Vocabulary, use the second query.

Query To Assign All Taxonomies to The Default post “Category”

SELECT taxonomy_term_data.tid AS term_taxonomy_id, taxonomy_term_data.tid AS term_id, "category" AS taxonomy, taxonomy_term_hierarchy.parent FROM `taxonomy_term_data` LEFT JOIN taxonomy_vocabulary on taxonomy_vocabulary.vid = taxonomy_term_data.vid LEFT JOIN taxonomy_term_hierarchy ON taxonomy_term_hierarchy.tid = taxonomy_term_data.tid ORDER BY `term_taxonomy_id` ASC

Query To Retain Custom “Vocabularies” For All Taxonomies

This query assumes you have already created the required Custom Categories functions within WordPress.  You can do this manually by following this guide or you can create them by using a plugin such as Pods – Custom Content Types and Fields (our preferred method)

SELECT taxonomy_term_data.tid AS term_taxonomy_id, taxonomy_term_data.tid AS term_id, machine_name AS taxonomy, taxonomy_term_hierarchy.parent FROM `taxonomy_term_data` LEFT JOIN taxonomy_vocabulary on taxonomy_vocabulary.vid = taxonomy_term_data.vid LEFT JOIN taxonomy_term_hierarchy ON taxonomy_term_hierarchy.tid = taxonomy_term_data.tid ORDER BY `term_taxonomy_id` ASC

This query extracts the 4 fields we need to populate the wp_term_taxonomy table, term_taxonomy_id,term_id,taxonomy,parent

After selecting Export from Query Results Operations (which is usually located at the bottom of the row of results), select “CSV” as the format.

To import, select your WordPress wp_term_taxonomy table and then click on Import (located at the top of the page).  Select your .csv file and in the Columns Names section enter term_taxonomy_id,term_id,taxonomy,parent

Step Two

Next, we want to populate the WordPress wp_terms table with our taxonomies.  WordPress uses slugs (comparable to Drupal’s “machine-name”).  But since Drupal doesn’t use a machine-name for taxonomies, we’ll create one as we generate our export using REGEXP_REPLACE and LOWER.  The will remove all characters other than alphabetical characters and dashes and will also convert all characters to lowercase.  To get this data, we use the following query:

SELECT `tid` AS term_id, `name` AS name, LOWER(REGEXP_REPLACE(`name`, '[^0-9a-zA-Z-]', '')) AS slug FROM `taxonomy_term_data`

This query extracts the 3 fields we need to populate the wp_terms table, term_id,name,slug

After selecting Export from Query Results Operations (which is usually located at the bottom of the row of results), select “CSV” as the format.

To import, select your WordPress wp_terms table and then click on Import (located at the top of the page).  Select your .csv file and in the Columns Names section enter term_id,name,slug

Matching Drupal Nodes (aka WordPress Posts) To Taxonomy

If you are using the Ultimate CSV Importer Free version to import your Drupal Nodes into WordPress Posts, you won’t need to worry about importing the taxonomy relationships separately because the CSV Importer will match it up for you automatically as you assign fields.

If you decide to map these relationships manually, it’s best to ensure that your imported WordPress Post IDs match your Drupal Node IDs.