Posted on by

Importing 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:

state
OR
OK
NY

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 . If it has a link text, it’s represented as a “markdown” string like this: [Link Text](http://domain_name.tld). 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

For very large uploads, you may need to break the upload up into smaller chunks.

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.

46 thoughts on “Importing an Existing Database into Participants Database

  1. I am attempting to upload 1 .3Mb file containing 30,000 rows in CSV format. Having checked the the uploads directory I can see that the file uploaded correctly but the data import into the database never completes. Several attempts have been tried involving truncating the existing table and starting afresh. I generally reach 7 or 8000 , then reload the data again and it may go to 11,000 and so on but never seem to reach the full import of rows.

    At first I believed it to be a server timeout limit but I have tried on another server with a different hosting company and still no joy. Is there is a limit on data import rows ? or a method of getting the data into the database other than using the built in import CSV utility ( no sure if this would be a problem as regards PrivateID generation)

    Thanks

    1. The plugin is not set up to handle very large data imports. You need to break the CSV up into smaller chunks, how large depends on the server. The limitation in most cases is the “max_execution_time” php setting, lengthening that should increase the amount you can upload at once.

  2. This is what I discovered: If the value of the column is less than 5 characters in length, if the column is used as the key to look up the record, it is then generated as a random 5 character length. I have gone back through my database and updated those unique values to be 5 characters long so they are not changed when uploaded into the database.

    1. Yes, it is a 5-character code by default. It is possible to change the length of the private id using the filter ‘pdb-private_id_length’

  3. I am importing a csv file that contains a unique alphanumeric value called PrivateId. PrivateId is used as the lookup value for the client to update their record and therefore appears under the Record Info area and I am unable to change any setting which may be causing the issue. I have one record that generates a random value upon upload of the initial upload for testing. I have examined the information in the csv file using Hexplorer and the information appears valid. Regardless of how many times I upload the data or what data surrounds it, it will generate a unique and random value. Suggestions? I can upload the csv for you to work with.

    1. There is an internal field named ‘private_id’ that is used as a unique identifier, so your field must use a different name. If you named it ‘privateid’ that would work, although it might be a little confusing.

  4. I have uploaded my spreadsheet after dividing my database fields into 2 groups (to simulate multi-db) but when I upload the file that has only the fields from one group in it, random data gets populated into the fields from the other group for that record – how can I ensure these fields remain blank?

    1. It’s hard to say what is happening without seeing the file you are uploading. If a column is not present in the CSV, the import leaves the value for that column alone. What you may want to try is to include all the fields, but with blank values for the columns you want empty. However, if the upload is UPDATING existing records, blank fields will be ignored.

      1. Dear Roland, thank you for your time and for responding so quickly, I believe I have figured it out. I researched your wonderful documentation and finally found a page about the “persistence” checkbox – when I unchecked these the data uploaded beautifully. Thanks again!

  5. Hello Roland. Thanks for the answers to another question elsewhere.
    I’m just wondering, and haven’t seen it posted anywhere yet, but when I will upload a csv file from another database, will the database plugin send an email to the participant giving them a code to get back into their information for addition or correction. As if they were signing up the first time?
    Thanks,

    1. No, it won’t do this unless you configure an email using the Email Expansion Kit add-on. The way this works is once the records are imported, you can select them in the admin Participant List page and send out the “signup” email. It’s not a good idea to do it automatically because it can happen by accident, or you just won’t be certain the email went out correctly.

Leave a Reply
You have to agree to the comment policy.

46 thoughts on “Importing an Existing Database into Participants Database

  1. I am attempting to upload 1 .3Mb file containing 30,000 rows in CSV format. Having checked the the uploads directory I can see that the file uploaded correctly but the data import into the database never completes. Several attempts have been tried involving truncating the existing table and starting afresh. I generally reach 7 or 8000 , then reload the data again and it may go to 11,000 and so on but never seem to reach the full import of rows.

    At first I believed it to be a server timeout limit but I have tried on another server with a different hosting company and still no joy. Is there is a limit on data import rows ? or a method of getting the data into the database other than using the built in import CSV utility ( no sure if this would be a problem as regards PrivateID generation)

    Thanks

    1. The plugin is not set up to handle very large data imports. You need to break the CSV up into smaller chunks, how large depends on the server. The limitation in most cases is the “max_execution_time” php setting, lengthening that should increase the amount you can upload at once.

  2. This is what I discovered: If the value of the column is less than 5 characters in length, if the column is used as the key to look up the record, it is then generated as a random 5 character length. I have gone back through my database and updated those unique values to be 5 characters long so they are not changed when uploaded into the database.

    1. Yes, it is a 5-character code by default. It is possible to change the length of the private id using the filter ‘pdb-private_id_length’

  3. I am importing a csv file that contains a unique alphanumeric value called PrivateId. PrivateId is used as the lookup value for the client to update their record and therefore appears under the Record Info area and I am unable to change any setting which may be causing the issue. I have one record that generates a random value upon upload of the initial upload for testing. I have examined the information in the csv file using Hexplorer and the information appears valid. Regardless of how many times I upload the data or what data surrounds it, it will generate a unique and random value. Suggestions? I can upload the csv for you to work with.

    1. There is an internal field named ‘private_id’ that is used as a unique identifier, so your field must use a different name. If you named it ‘privateid’ that would work, although it might be a little confusing.

  4. I have uploaded my spreadsheet after dividing my database fields into 2 groups (to simulate multi-db) but when I upload the file that has only the fields from one group in it, random data gets populated into the fields from the other group for that record – how can I ensure these fields remain blank?

    1. It’s hard to say what is happening without seeing the file you are uploading. If a column is not present in the CSV, the import leaves the value for that column alone. What you may want to try is to include all the fields, but with blank values for the columns you want empty. However, if the upload is UPDATING existing records, blank fields will be ignored.

      1. Dear Roland, thank you for your time and for responding so quickly, I believe I have figured it out. I researched your wonderful documentation and finally found a page about the “persistence” checkbox – when I unchecked these the data uploaded beautifully. Thanks again!

  5. Hello Roland. Thanks for the answers to another question elsewhere.
    I’m just wondering, and haven’t seen it posted anywhere yet, but when I will upload a csv file from another database, will the database plugin send an email to the participant giving them a code to get back into their information for addition or correction. As if they were signing up the first time?
    Thanks,

    1. No, it won’t do this unless you configure an email using the Email Expansion Kit add-on. The way this works is once the records are imported, you can select them in the admin Participant List page and send out the “signup” email. It’s not a good idea to do it automatically because it can happen by accident, or you just won’t be certain the email went out correctly.

Leave a Reply
You have to agree to the comment policy.