Posted on by

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:

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

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.

74 thoughts on “Migrating an Existing Database into Participants Database

  1. Sorry to bother you again here. I have saved my import CSV in UTF-8 but I still fail to import data that contains special characters such as ø and Ã. All my failed attempts seem to contain one or more of those characters.

    Would you please let me know how I could fix it? Thank you!

    1. Your CSV may not really be using UTF-8 encoding, because characters such as that work fine as long as the encoding is correct. Most spreadsheet applications that export CSV files need to be specifically configured to export a UTF-8 encoded file.

      1. I used Excel to save as CSV UTF-8 but it actually was UTF-8 BOM. I then used Sublime text to convert it back to just UTF-8 and everything worked! lol… Thanks again for the quick support and great plugin!

  2. Hello
    sorry to bother again but I have a ‘strange’ behaviour importing a csv file : (according to the trace)
    some fields are well parsed but just ‘fortoggen’ in the INSERT statement ..but when I updated them one by one they are well updated (UPDATE Statement) in the record.
    one out 4 ‘strange’ fields for example :

    PARSING:
    [qualification] => Libéral
    [exp?Ã?©rience_professionnelle] => Dentiste équin
    [titre_du_projet] => ME dentiste

    INSERT Statement
    qualification

     = ‘Libéral’, 

    titre_du_projet

     = ‘ME dentiste’,
    
    after updating myself 
    UPDATE Statement  

    qualification

     = ‘Libéral’, 

    expérience_professionnelle

     = ‘ghdfhdffffgtyjtgjtg’, 

    titre_du_projet` = ‘ME dentiste’,

    I am about to reinstall the plugin and re enter my 50 fields…what a pain…
    Any clue before that?
    thanks

    1. Reinstalling and redifining the fields won’t help, don’t bother with that.

      I don’t understand the specific issue you are having. Try to narrow it down to which specific fields are having a problem, then look at what kind of field it is and what the data needs to look like for that field. I think your input data is not formatted according to what the field needs.

      1. thanks for your reply.
        so far I have identified problems with 3 fields :

        – date field : I have copied the data that was not ‘taken’ to another date field and it’s ok
        => since both are date field, on can assume the data is ok but there is a problem with the intiatial date field?

        -text area : I have copied the data that was not ‘taken’ to another text area field and it’s ok
        => since both are text area field, on can assume the data is ok but there is a problem with the intiatial text area field?

        -dropdown list : I cannot copy the data because no othe fields has the same list but I have checked that the data is one of the list.

        it seems then that the problem does not come from the data but from the field itself. but what is the problem?

        still mysterious…

        1. Hello

          I have rebuild the whole database (50 fields/ 8 groups) identical to the previous one and it works fine now.

          Still I have *empty* groups that I cannot delete.(personal info and participant info). I ask for deletion then update ther groups but they are still alive…Any reasons for that?

          have a good day

  3. Hello Roland
    thanks again for your help on previous issues.
    I am having another 2 problems importing my existing database to PDB using import CSV:

    1) all the dates in my existing database have a “d/m/Y’ format and this is how I have set the date format in my WordPress general setting but it does not work . PDB seems to be expecting a ‘d/m/Y’ format.(PDb_Date_Parse::datetime_parse value: 13/01/2020 error: Array).
    What else should I do to make it work?

    2) all the imported values are well assigned to the correct fields according to the trace and they are all well visible by the pdb_list but some value are not displayed (but not for all the records) by the pdp_record (in the update mode) and by the back-end function ‘update an already recorded participant’ (it might not be the exact name of the function since I am translating from french) coming from the ‘participant list’ also in the backend.

    any clue?
    have a great day.
    Albert

    1. I have solved problem 1).
      I didn’t see the pdb setting on the date format. I had wrongly assumed the the date format in Pdb was taking from the date format setting in WordPress. Now with a d/m/Y setting in Pdb it works fine.

      I have solved problem2)
      I understand now why I had this ‘problem’ . In fact it is not a bug but a feature I didn’t expect at all.
      The missing/not displayed fields were defined as Dropdown. So if the exported value in the field did not match one element in the dropdown list it would not be displayed.
      Defining rather this field as “Dropdown/Other” when the field does not match with one element in the list, it is displayed “other” and another field is displayed on the side with the actual (not matching) value…

      a very sophisticated feature indeed. congratulations.

      1. Yes, that is good…

        But…please don’t ask a question both here and in the WordPress forum. Pease post your solution in the thread on the WordPress support forum so that other users will know the answer….thank you.

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

Would you like to be notified of followup comments via e-mail? You can also subscribe without commenting.

74 thoughts on “Migrating an Existing Database into Participants Database

  1. Sorry to bother you again here. I have saved my import CSV in UTF-8 but I still fail to import data that contains special characters such as ø and Ã. All my failed attempts seem to contain one or more of those characters.

    Would you please let me know how I could fix it? Thank you!

    1. Your CSV may not really be using UTF-8 encoding, because characters such as that work fine as long as the encoding is correct. Most spreadsheet applications that export CSV files need to be specifically configured to export a UTF-8 encoded file.

      1. I used Excel to save as CSV UTF-8 but it actually was UTF-8 BOM. I then used Sublime text to convert it back to just UTF-8 and everything worked! lol… Thanks again for the quick support and great plugin!

  2. Hello
    sorry to bother again but I have a ‘strange’ behaviour importing a csv file : (according to the trace)
    some fields are well parsed but just ‘fortoggen’ in the INSERT statement ..but when I updated them one by one they are well updated (UPDATE Statement) in the record.
    one out 4 ‘strange’ fields for example :

    PARSING:
    [qualification] => Libéral
    [exp?Ã?©rience_professionnelle] => Dentiste équin
    [titre_du_projet] => ME dentiste

    INSERT Statement
    qualification

     = ‘Libéral’, 

    titre_du_projet

     = ‘ME dentiste’,
    
    after updating myself 
    UPDATE Statement  

    qualification

     = ‘Libéral’, 

    expérience_professionnelle

     = ‘ghdfhdffffgtyjtgjtg’, 

    titre_du_projet` = ‘ME dentiste’,

    I am about to reinstall the plugin and re enter my 50 fields…what a pain…
    Any clue before that?
    thanks

    1. Reinstalling and redifining the fields won’t help, don’t bother with that.

      I don’t understand the specific issue you are having. Try to narrow it down to which specific fields are having a problem, then look at what kind of field it is and what the data needs to look like for that field. I think your input data is not formatted according to what the field needs.

      1. thanks for your reply.
        so far I have identified problems with 3 fields :

        – date field : I have copied the data that was not ‘taken’ to another date field and it’s ok
        => since both are date field, on can assume the data is ok but there is a problem with the intiatial date field?

        -text area : I have copied the data that was not ‘taken’ to another text area field and it’s ok
        => since both are text area field, on can assume the data is ok but there is a problem with the intiatial text area field?

        -dropdown list : I cannot copy the data because no othe fields has the same list but I have checked that the data is one of the list.

        it seems then that the problem does not come from the data but from the field itself. but what is the problem?

        still mysterious…

        1. Hello

          I have rebuild the whole database (50 fields/ 8 groups) identical to the previous one and it works fine now.

          Still I have *empty* groups that I cannot delete.(personal info and participant info). I ask for deletion then update ther groups but they are still alive…Any reasons for that?

          have a good day

  3. Hello Roland
    thanks again for your help on previous issues.
    I am having another 2 problems importing my existing database to PDB using import CSV:

    1) all the dates in my existing database have a “d/m/Y’ format and this is how I have set the date format in my WordPress general setting but it does not work . PDB seems to be expecting a ‘d/m/Y’ format.(PDb_Date_Parse::datetime_parse value: 13/01/2020 error: Array).
    What else should I do to make it work?

    2) all the imported values are well assigned to the correct fields according to the trace and they are all well visible by the pdb_list but some value are not displayed (but not for all the records) by the pdp_record (in the update mode) and by the back-end function ‘update an already recorded participant’ (it might not be the exact name of the function since I am translating from french) coming from the ‘participant list’ also in the backend.

    any clue?
    have a great day.
    Albert

    1. I have solved problem 1).
      I didn’t see the pdb setting on the date format. I had wrongly assumed the the date format in Pdb was taking from the date format setting in WordPress. Now with a d/m/Y setting in Pdb it works fine.

      I have solved problem2)
      I understand now why I had this ‘problem’ . In fact it is not a bug but a feature I didn’t expect at all.
      The missing/not displayed fields were defined as Dropdown. So if the exported value in the field did not match one element in the dropdown list it would not be displayed.
      Defining rather this field as “Dropdown/Other” when the field does not match with one element in the list, it is displayed “other” and another field is displayed on the side with the actual (not matching) value…

      a very sophisticated feature indeed. congratulations.

      1. Yes, that is good…

        But…please don’t ask a question both here and in the WordPress forum. Pease post your solution in the thread on the WordPress support forum so that other users will know the answer….thank you.

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

Would you like to be notified of followup comments via e-mail? You can also subscribe without commenting.