How to import a relational database into WordPress with Toolset

Let’s assume you have an existing relational database, and for some reason you need to transfer this data into WordPress. That’s no easy task, since WordPress – although built on top of a relational database like MySQL itself – is a content management system, not a data management platform. You need additional plug-ins and some experience to achieve this – here is how.

Intended audience for this tutorial

First, let’s clarify who will benefit from this tutorial:

1. You should have a basic understanding of relational databases. You should especially be familiar with relationships (like one-to-one, one-to-many and many-to-many relationships), including the role of primary keys and foreign keys in such relationships.
2. You should be able to transfer your data from the database to Microsoft Excel (or some other spreadsheet software), to apply some formulas to it, and to export the data into UTF8-encoded CSV files.
3. You should have a good understanding of the Toolset plug-in for WordPress, its purpose, and how to build relational data structures with it.
4. You will need additional WordPress plug-ins to import and export data in the CSV format. The makers of Toolset recommend these three plug-ins. This tutorial assumes that you are using WP All Import Pro and WP All Export Pro.
5. And of course you should have a solid knowledge about WordPress in general.

For the time being, this is a very condensed tutorial, highlighting only the major issues you might be struggling with. All the basic stuff (which you will already know if you are part of our target group as described above) is not covered.

The basic challenge

The Toolset plug-in allows us to build relational data structures right within WordPress. So why is importing data from an existing relational database a challenge?

To explain this, let’s assume we have the following data structure:

So basically this database stores the following information:

• persons (including their names and phone numbers)
• events (including their names and locations)
• registrations (including the date when a person has registered for an event)

In the concept of relational databases this is typical example of two entities (persons, events) and a many-to-many relationship connecting them (registrations). To store this in a database, we need three tables. Each record in the tables for persons and events has a unique numerical ID as primary key, and these IDs are used in the registrations table as foreign keys.

If you had to migrate this data from one relational database management system to another relational database management system, you would simply export and re-import these three tables one by one. The primary and foreign keys would guarantee that the data remains correct.

And here lays the problem: Toolset (or rather WordPress) does not allow us to import these primary and foreign keys, at least not as identifiers. Why so? In WordPress, all data is basically a post, and a post is identified by its post ID. There are no separate database tables for each entity and each relationship. When we import data into WordPress, each record is stored as a post and gets an auto-generated post ID. In our example, persons, events, and registrations will equally be represented by posts in the WordPress database.

Once we understand this, it’s also obvious that we cannot use our primary and foreign keys anymore. We cannot tell WordPress during the import process to use our existing primary key as the new post ID. Why? There is a technical and a logical reason for this:

• The technical reason: WordPress simply won’t let you do this. No matter which import plug-in you are using, you won’t find the option to set the post ID based on imported data. As mentioned above, the post ID is auto-generated, and this happens on the database level, so it’s not under the control of any plug-in or even the WordPress core.
• The logical reason: Since the post ID is the identifier of a post, it must be unique. If you take our example from above, our persons ID is also unique, and so is our events ID – so far, so good. But it’s very likely that we will find a certain value used as a person ID and as an event ID at the same time, and this makes it impossible to use our existing primary keys as the new post IDs.

So the bottom line is: As soon as you need to import data from a relational database which includes relationships (no matter if these are of the one-to-one, one-to-many or many-to-many type), this is not a straight-forward process with Toolset.

How to solve this challenge

Because we cannot import our IDs from the existing database into WordPress, we need to find a way to map our primary and foreign keys to WordPress post IDs. This is a bit tricky, since the post IDs are only created during the import process, we don’t know them upfront. The process is basically as follows:

1. We import all tables representing entities (in our example: persons and evens) into WordPress, including their primary key values. This will generate the post IDs for our data. Note that we do no care about relationships at this point in time.
2. We export the data we have just imported, now including the post IDs. This gives us a mapping table, allowing us to replace every primary or foreign key in our data with the corresponding post ID.
3. We do the final import of our data, this time including the relationships. Note that we do not create new posts for our entities this time but simply update the posts we have created before. New posts are only created for data representing many-to-many relationships like our registrations table.

In case your many-to-many relationships have attributes as well (like the registration date in our example), you will need another iteration of exporting and re-importing data, but the underlying principle remains the same. We will discuss this later in this tutorial.

So now that we have outlined the basic problem and the basic solution, let’s dive into the details.

Step 1: Replicate your database structure with Toolset

The first thing you need to do is to create the exact same data structure in WordPress that you have in your existing database. For this purpose you create all necessary custom post types, custom fields, and relationships with the Toolset plugin. Assuming that you know Toolset well and that you have access to the existing database, this is not especially complicated. And since the general use of the Toolset plugin is not the focus of this tutorial, we will not go into the details here.

There is only one thing we need to discuss. Most tutorials about importing data into WordPress advise you not care about the primary and foreign keys in your existing database at all. These tutorials typically rely on the post title instead when it comes to relationships. Although it makes the import process easier, we do not recommend this approach: It only works when the field becoming the post title in WordPress contains nothing but unique values. This is often not the case, as you can see in our example from above: There could easily be several persons with the same name, and this would compromise our registrations. To follow this tutorial it’s mandatory that you replicate all ID fields containing the primary and foreign keys as well when you build the data structure in Toolset.

One more detail: When you define a many-to-many relationship with Toolset, a so-called intermediary post type is created as a result. By default, intermediary post types do not show up in the WordPress backend navigation bar and are kind of hidden from the user. We recommend that you check the option «Intermediary Post Type visible in WordPress admin menu» in the Relationship Wizard when creating these relationships. This makes it easier to see what’s going on behind the scenes.

After the data structure is ready, go and create one dummy entry for every post type. This not only helps to check if your data structure is correct and complete; it’s also a prerequiste for the import plug-in to see all fields you have defined.

Step 2: Import all entities into WordPress

The next step is quite straight forward: Export all data from your existing database in the CSV format. You should end up with one CSV file per database table – in our example this would be persons.csv, events.csv, and registrations.csv. To avoid problems, make sure these files are UTF8 encoded.

Next, import these CSV files into WordPress – but not all of them: Skip all files which do not represent an entity but a many-to-many relationship. In our example you would import persons.csv and events.csv, but skip registrations.csv for the time being.

And once again: Make sure that you import your primary key fields like any other field of your existing database.

Step 3: Export your ID mapping tables

By completing step 2 you have already created the page IDs for your existing data. And since we have imported our old primary keys as well, we now know which primary key value corresponds to which page ID. The only problem is that this information is stored somewhere in the WordPress database, and we need it outside of WordPress to process our CSV files.

But that’s the easiest part of the whole process: Simply export all records from all custom post types. You don’t necessarily need to export all the fields – just make sure that you export at least the post ID and your former primary key field. The result will be what we call a mapping table for every custom post type: A list that provides the WordPress page ID for every given primary key value in your existing database. And equipped with this mapping table we can now approach our main challenge: importing our many-to-many relationships.

Step 4: Prepare your data in Excel

To proceed we need to transform our CSV files a bit, and the most efficient tool to do this is Microsoft Excel (or another spreadsheet software). So go ahead and load all your CSV files into an Excel document – and by «all» your CSV files we mean not only the database export (step 2) but also the WordPress export (step 3).

Our next task is to replace all primary and foreign key values in your database export by the corresponding page IDs. In Excel, you can achieve this easily with a formula based on the LOOKUP or VLOOKUP function. Again, we are not going into the details here, since you well find plenty of tutorials on how to use these functions should you need it.

After this step our persons.csv will look something like this:

And our regstrations.csv will look something like this:

As you can see, we don’t actually replace our primary and foreign key values, but we simply add the corresponding page IDs as a separate columns.

Can we now finally import our registrations into WordPress as well? No, not yet. Unfortunately we cannot import data into Toolset’s intermediary post types this way. Instead, we need to append this data to either persons.csv or events.csv. Assuming that we choose persons.csv, the result will look something like this:

So what’s happening here? Basically we add the page IDs of all events a person is registered for to that person’s record, using the additional column Event_WP_Post_IDs. If a person is registered for more than one event, we simply separate the post IDs by a comma. We have this data in registrations.csv already, we simply have to reformat it. Again, we will not discuss this in detail here – if you are familiar with Excel it will be no big deal to restructure your data like this.

Step 5: Import all relationships into WordPress

As the final step we need to import the data that constitutes our relationships. In our case this data is now part of persons.csv (see step 4), so we have to import persons.csv again. Make sure that you don’t create additional posts in WordPress but that you update the existing posts. Since the post ID is now part of your data, you have a reliable identifier for this updating process. And if we map our Event_WP_Post_IDs column to the appropriate relationship, Toolset will create the necessary records in our intermediary post type.

Or doesn’t it? If you check your intermediary post type right away, then you won’t see any records. But they exist – just check one of your custom post types. And once you did that, the data in the intermediary post type will become visible as well. Hopefully the Toolset developers will fix this bug soon.

If you have a lot of records, checking each one individually just to make the intermediary post types data visible is not an option. Furtunately, there is a workaround: Find the intermediary post type under Toolset > Relationships, open it for editing,  scroll down to the custom fields and click the «Edit Fields» button. This should bring up a warning message like this one:

After a while, Toolset will tell you that all existing associations have been updated. But here comes another bug into play: Toolset updates only 50 associations at a time. Therefore, if you had more than 50 associations in the beginning, you will see the same warning again once you hit the reload button of your browser. But don’t worry about this, just hit that reload button again and again, until all associations are updated. And once you’re through, all records in your intermediary post types are visible.

Additional step for many-to-many relationships with attributes

If your database has only many-to-many relationships without any attributes, then the import process is complete by now. But what if there are custom fields in your intermediary post types, like the registration date in our registrations.csv?

In this case you have to go through another iteration:

• Export all records from your intermediary post type to retrieve their post IDs. You won’t find the foreign keys in this export as separate fields, but they are part of the auto-generated post title (example: Registrations: 286 – 291), so you can easily extract them from there.
• Add these post IDs to registrations.csv using Excel.
• Re-import registrations.csv into WordPress, making sure you are not creating new posts but updating the existing posts.

As an additional challenge you will not see our custom field Registration_Date in the Toolset Types Add-On section of the WP All Import Pro wizard. But since this is just a custom field, you can simply import it as you would any custom field, as shown in the following screenshot:

Conclusion

In this tutorial we have demonstrated how you can properly import data from an existing relational database into WordPress – even if you have many-to-many relationships with attributes, which is kind of the worst case scenario. As you can see, it’s a complex and time-consuming but not impossible task. However, we really hope that the Toolset developers will offer a simpler way to achieve this in the near future.