Migrating an Existing Database into Participants Database

If you have a database already and want to adapt it for use in the Participants Database plugin, you’ll have to set up the plugin to interface with that data and then import the data itself into the plugin’s main database table.

Here’s how you get that done.

Start with the User Interface

Participants Database is essentially a user interface for a database. When you want to port an existing database into the plugin, your first task will be deciding how to best represent each field of data in your database.

Go through each field in your database and decide how it would best be managed: for instance: is it a single line of arbitrary text (like a name)? Then it should be defined in the plugin as a text-line field.

Several examples of how to translate your database field to a plugin field:

• a large set of single terms or phrases (like a state): dropdown
• a small set of single terms: radio button
• a set of terms drawn from several possibilities (like a set of qualifications): multiselect
• HTML: rich text
• image file: image upload field
• an email address: text-line (linked emails are automatic)
• a series of yes/no fields that are related? Try combining them into a multiselect checkbox.

And so forth, the idea being to find the best way to give the user the ability to add or edit the data that will preserve the integrity of the database. Don’t worry about the names of the fields as you enter them, they can be different from the column names in your existing database. Just make sure they are unambiguous so you know what goes where.

Matching the Existing Values

When you are defining dropdowns, radios, multiselects, etc. where the possible values are defined in the field, be careful to match the values to what is in the existing data. The labels for the values can be anything, but the values must match exactly.

For example, if you have this in your database:

You would have this as your values for your dropdown:

Oregon::OR,Oklahoma::OK,New York::NY

It’s important you define all the fields you want to use from the original database. Adding and/or changing this configuration later will be a headache, best to avoid that if possible. It’s easy to delete a field later, so be generous in what you decide to import to the plugin.

Groom Your Data for the Import

Now that the plugin fields have been defined,  you’ll need to prepare the data you’re going to import into the plugin database. The easiest way to do that is to get your existing database into a spreadsheet application. Typically, this would be done using a CSV file. It is very important this be done using “UTF-8” encoding at every step.

Once the data is in your spreadsheet app, edit the column heads to match the field names you established in the plugin.

Most data types will be imported into the plugin as a string, but there are a few exceptions you may have to deal with.

Multiselects

If it is a multiselect-type field in the plugin, the data in that field in the spreadsheet should be a comma-separated list of terms. Make sure those terms match what you defined in the plugin for that field.

URL’s with Linktext

“Link” fields in the plugin expect special formatting on the imported data. If it is a bare URL, it should be represented as <URL>. (replacing “URL” with the actual URL) If it has a link text, it’s represented as a “markdown” string like this: [Link Text](URL). Email addresses should be imported as a plan text-line. The plugin handles creating links on these.

Images and Files

Images and files are normally stored as filenames without paths. These are expected to be found in the uploads directory defined in the plugin settings. It is also possible to use a full absolute URI (i.e. a URL that includes the domain name) if you need to. Be careful about using relative paths for images and files, it will work, but may cause problems later.

Saving Your CSV

Once you have the headers set up and the data groomed for importing into the plugin, save the spreadsheet file (in case you run into trouble and need to try again) and then export the sheet as a CSV. Be sure to take the extra step of configuring the CSV export because the default settings are often not the right ones to use here. Use these settings for your CSV export:

Delimiter: comma (,)
Enclosure: double-quote (“)
Separator: line-feed
Encoding: UTF-8

Save it on your local computer in a place you can easily find. Be sure to give it a name that differentiates it from other similar files. Remember, this file is a temporary file only used to transport the data from the spreadsheet to the plugin. The spreadsheet file is the one to keep, and the one to go back to if changes are needed.

Importing the CSV

Using the “Import CSV File” page in the plugin, find and import your CSV file. There is no limit to the number of records you can import here, but your PHP configuration (this is in your hosting setup) will limit the file size. If you need to upload a very large CSV, you may need to change the “file_upload_size” setting on your PHP configuration, and possibly the “max_execution_time” setting as well. If you don’t know how to do this, ask your web host.

After the file is uploaded, the plugin will tell you how many records it got. If it gets hung up on one, it will let you know where that happened. In a case like that, you should look at the records around that number for something amiss. The things that can cause problems are quotes, commas, line breaks…in other words, characters that the CSV uses to organize it’s data. These characters can be imported in the data (that’s what “enclosures” are for) but not all spreadsheet apps can do this correctly, or can be confused in some circumstances.

Most of the time, just following the CSV configuration guidelines will solve the problem. If you can’t configure the CSV in the spreadsheet app, try another app or use a plain-text editor to format your CSV. A CSV file is nothing more than plain text, and so pretty much all problems can be solved in your plain-text editor. The encoding is one thing you can’t change, the exporting app must use UTF-8 encoding or you will have trouble. A good plain text editor will tell you the encoding of the file. It will allow you to change it there, but in most cases the damage will have already been done by the exporting application.

Do-Overs Allowed

With a complex data set, things won’t always come in to the plugin and behave the way you want. After uploading your data, play around with the listing and editing tools in the plugin to make sure it’s interacting with the data the way you want. If it’s not right, you can start over.

For small datasets, just go to the List Participants page in the plugin admin and delete the records. For large datasets, you’ll want to use a database management tool (such as PHPMyAdmin) to delete the records. If you want to reset the index, you can do this using the management tool…but only do that if you have deleted all records. To delete the contents of a table and reset the index, use the TRUNCATE command.

The database management tool is also a great place to check your imported data. You will want to be sure it’s all correct and all your fields are defined the way you want them before you actually start using the database. Going backwards once the plugin is in use and the database has been altered is very much more complicated and should be avoided.