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.
Roland Barker 
Tom 
Sergio Ferrari 
atcoo 
Karen 
Katie 
My Chosen Dropdown/Other fields are comma separated within the field, but instead of creating their proper entries, it is combining all of comma separated items into a single option.
For example, “WWOOFing” & “Festivals” & “Conferences” are normally options in the Chosen Dropdown/Other field for community events. But when uploading it, it creates a single entry “WWOOFing,Festivals,Conferences”
I did ensure that these values match exactly what is defined in the Chosen Dropdown/Other field.
Am I doing something wrong?
If I am understanding what you’re trying to do, if the field is not a “multiselect” field, then only one value can be uploaded for that field, so it is interpreting the comma-separated values as a single value.
The field is “Chosen Multi Dropdown/Other”.
So, if I change the field type to multiselect, it should work? Then I can change it back and not have any data loss?
You will need to decide whether the field should should hold multiple values or not.
Forgive me, I wrote out the wrong name. It is not the chosen dropdown/other, it is the chosen multi dropdown/other. This is the field that is combining all of the comma separated values as one. Is it supposed to import like the muliselect?
If not, since both the chosen multi dropdown/other and the multiselect allow for multiple values, can I temporarily change the field type to multiselect, perform the import, and then change it back to chosen multi dropdown/other?
When data from a multiselect field is exported, the selected values are exported as a comma-separated string. When they are imported, it is supposed to split that string into discrete values. I will do some testing to make sure that this works as expected for Chosen Multiselects.
I can confirm there is a bug when importing data to a Chosen Multiselct Other, and I’m working on a fix.
Thank you so much for looking into it. I look forward to the update.
When trying to upload a large number of records in a CSV I get a 405 error after 30 seconds or so. Is there a limit to the number of entries you can add to the CSV or is this a limitation of the hosting. I have a CSV with about 30,000 records I’m trying to upload and I get about 3,000 more or less before the error.
Hi Tom,
In it’s current form, the plugin uploads the whole CSV in a single operation. This means that it will time out on larger uploads. You can help this to some degree by changing the PHP configuration value max_execution_time.
That your server is giving a 405 when it stops is unusual, that status code usually happens when a resource(such as a read-only file) is improperly accessed.
I am working on a background queue for CSV uploads, but it’s not in yet. For now, the workaround is to send your data in in batches.
Thank you, Roland
Sorry, I typed the code backward it’s a 504… gateway time-out
Thank you for the response, I’ll try increasing the execution time. what would you recommend I increase it too?
You can set it as high as you like, but you’ll run into limits imposed by your server. You could ask your web host about this. A typical long execution time would be 60 seconds.
Hi Roland,
Are you aware of the fact that when importing a csv, if a record has a link field in which the url of the link includes a space, the value of the link field is not updated or inserted correctly (url field is empty)?
Here is an example of link-field appearing in an imported csv : “[click here](http://localhost/my files/file1.pdf)”
I am aware myself that an URL including a space is not a good idea, but I am using participants database in an intranet based on Windows, and there are many spaces in paths in this environment.
I tried, in the csv, to englobe the url with various quotes, even to replace the space by %20, with no good results.
Thanks for the plugin, it is very useful to me.
I am using the built-in PHP URL validation filter on that, and it looks like it does not validate a URL with a space. I think what I need to do is make it possible to modify or skip the URL validation. It’s meant to help people avoid putting bad URLs on their site, and serves as an important security protection since the URL is user input.
If you want to modify the behavior yourself, the validation happens in the function Participants_Db::get_link_array in the file participants-database.php This method is used to validate “link” fields in all contexts.
By the way, I tested the use of %20 to replace the space and it works fine. What issue were you having with that?
Hi Team, im trying import .csv file in a new installed plugin.
Get .csv with header and column as instructions but, when finish import, received this error:
The file filename.csv has been uploaded.
New columns imported from the CSV file.
516 matching records updated
1503 records skipped due to errors.
Where can i see error so to solve it?
Thank you so much for your plugin very helpful for my association.
Hi Sergio,
Usually, this is due to a syntax error in the CSV file. These can be hard to spot, but typically, it happens because there is a comma or a quote in the data that is not escaped. You should look at the 517th record for the problem.
Thank you very much Roland, issue solved.
Have a great day!
I am trying to import a csv file. I followed all the instructions to set it up but when I got to import, I get this message:
Target directory does not exist and could not be created. Try creating it manually.
What should I do? Thanks for your help!
If you are familiar with FTP or the hosting File Manager you can do this yourself. If you look at the plugin setting “File Upload Location” you can see where the directory needs to be created. If it’s still giving you trouble, I’d suggest talking to your web host about it, let them know the path indicated in that plugin setting.
Thank you–the site is on WordPress.com. I upgraded to the business class so I could use plugins but it is still hosted by WordPress. In the settings tab for Participants Database it gives this location:
wp-content/uploads/participants-database/
I haven’t changed anything so I don’t understand why it is not working. Thanks!
I chatted with some WordPress folks and this is what they said:
ok, thanks so much for waiting, we’ve tested the plugin several times and with different files, and we’re seeing the same error. The plugin is definitely broken so my only suggestion would be going back to the plugin developer, they might need to update/fix something on their end.
So, are you able to help me? Thanks in advance.
Honestly, I’ve never tested it on WordPress.com and so there is undoubtedly something different about that hosting environment that is causing the problem. But without knowing exactly what, there is little I can do to fix it in your case. Give me some time to look into this and I’ll get back to you with some possibilities.
Thank you, Roland. I appreciate your help.
Ini sangat cukup bagus, saya menjadi peran yang baru juga untuk menggunakan ini
Hello Roland,
How best can I add a “Download Database CSV” button at the front-end? Is it possible to have custom instances of different buttons, (with differing number of columns) to be downloaded – Such as a button that can download a CSV file with one group, and another button for another group, and may be another for all fields? Thanks so much in advance,
PHILIP.
Hi Philip,
Although this feature is on the list of enhancements, I have not taken the time to see what exactly needs to be done to make this happen, so I don’t have an answer for you at this time.
I’m new to this and I’m trying to import the database. I have set up the fields I want in Manage Database Fields, and they are showing up correctly in the database, but when I go to import the file, it shows that the correct number of records are being imported, but they are all blank.
First, make sure all the heading names of your CSV match exactly the names of the fields in the database. For instance, if you export your CSV from a spreadsheet, the top row should contain the names of the field where you want the corresponding data to go.
Hi Roland
Looking to utilise to allow volunteers to create their own tables from a data pool.
Using a one to many relationship
Data table 1 contains thousands of rows data :
DName – Type – Year – Style
Data table 2 thousands of rows
SName – Type – Style – SPerf – Owner
The 2 tables are used to create a new table with the linked data cells being Type or Style
So the new table created by each volunteer is by
using Table 1 style or type could choose any combination from table 2 being hundreds of records with that style.
and so one for about 50 rows for each new table.
Is that possible?
Hi Katie,
No, Participants Database can’t do this sort of thing, it’s pretty much a single-layer database with no relational capabilities.
It is possible to do something like what you describe using WordPress Custom Post Types and a couple of plugins to give you ways to relate the two CPTs.
Hi Roland – not sure if you are aware but the links back from the plugin via the WP dashboard go to 520 pages
eg:
https://xnau.com/wordpress-plugins/participants-database#donation-link
Thank you, the server must have been down for a moment.
If your participant’s data is stored over several DB tables – e.g., Addresses are stored in an address table and a key/pointer stored in the participant’s table, How would it be transferred?
The best way to do this is to use a spreadsheet app. Import your databases into separate sheets using a CSV. Then create another sheet that merges the other sheets together into a single sheet, then export that sheet as a CSV.