Using CSV Files
CSV is a common file format for exporting or importing databases and spreadsheets. You can export a CSV file from your spreadsheet or database and import the records into the plugin. It can be a tricky process, so I’ll offer a few hints here on getting it to work.
You’re going to see some words here that we should define in a specific way so it all makes sense. A “line” is a line in the CSV file that begins with a return character. The first line is the “header” and every line after that is the data for a single record. A “delimiter” is a character that goes between each value in a line. This character can be one of several characters, comma is the most common. Each line must have the same number of delimiters. An “enclosure” is a special character that is used to enclose the data for a single field so that it can contain a delimiter character that won’t be interpreted as a delimiter.
Importing Records
It is critical that the format of the file match what the plugin needs in order to successfully import your data.
To prepare a CSV file for reliable import:
- The CSV file should have it’s fields delimited by a comma and the text enclosed by double quotes. (this is strictly only required for a field that contains a delimiter character).
- The first row should be a header row containing the column names (which must match your field names). Be sure the spelling here is correct and there are no extra hidden characters.
- The encoding of the file must be UTF-8. This is very important, and many spreadsheet apps (looking at you, Excel!) need to be configured to export in this format.
In the plugin’s admin menu, on the “Import CSV File” page is a button to export a blank CSV file in the correct format to get you started. This template includes only the fields that are marked for export in the field definition, by having the “CSV” checkbox checked. This setting does not affect whether a field can be imported, the plugin will import any valid field regardless of this setting.
In some cases, you may have to open your CSV file (as exported from your spreadsheet or database) in a text editing application for further modification to get everything working. This must be a plain-text editor, don’t try this with a word processor!
In particular, if your data has both double and single quotes (which are also apostrophes!) in it, it will be very difficult to import because one of the two will need to be used as the text delimiter. This does not apply to “curly” quotes and apostrophes, which are not enclosure characters.
If your data contains both stright single quotes or apostrophes and straight double quotes, I suggest you convert straight quotes and apostrophes that are part of blocks of text into their HTML equivalents: ' for apostrophes and single quotes and " for double quotes. This way, the text delimiter can do its job without other characters interfering. You do not need to do this for “curly” quotes and apostrophes.
The “Image Upload Location” setting in the plugin is important here because that is where the plugin will place the uploaded CSV. The default setting for this is ‘wp-content/uploads/participants-database/’ If the plugin does not find the directory specified in the “Image Upload Location” setting, it will attempt to create it. It should notify you if it fails, and you may have to create the directory manually (using an FTP client, for instance) to get the CSV import to work.
As of version 2.0, CSV imports are processed in the background. This means that when you upload the CSV, the page will refresh and the number of records that were found in the file is printed to the screen. The data in the file is loaded into the database in the background. When the page is refreshed, the import status will be displayed. A large file may take several minutes to complete. This does not provide a way to avoid file size limits set by the server configuration.
Optionally, you can perform the import in the “foreground” which means it will import the whole CSV file all in one operation. The background import doesn’t work with some server setups, so you may need to use this workaround if background importing doesn’t work. You will only be able to import a limited number of records this way, usually around 3000 records or less. Larger imports can be broken up into multiple CSV files. You will find the setting for this in under the “Advanced Settings” tab in the plugin settings.
Importing in the background means that you can import a large number of records and it won’t go over the time limit imposed on operations by the php configuration. There is still the limit to the size of the file you can upload, but the most time-consuming part of the operation is performed by the server on its own. You can do other things with the site while the import is getting processed.
When the process is complete, a report is displayed when the import page is loaded again.
Overwriting/Updating Records on Import
On the Participants Database Import CSV File page, there is a preference for what to do when an importing record has a field that matches an existing record. If you want a CSV import to overwite (or update) an existing record, set this to “Update matching record with new data” and set the Duplicate Record check field to “Record ID” (this assumes you have included the id column in the CSV file you are importing).
Note that an empty value in a CSV will not overwite (blank out) a value in the record in the database, this is to prevent accidental data loss. To blank out a field’s value with an import, put the value “null” in the CSV for the field. It is also possible to use a code filter (with your own custom code) on the pdb-allow_imported_empty_value_overwrite filter if this is spomething you need to do a lot.
Server Caching
Some types of server cache (such as LiteSpeed) can cause problems with the background import. The import queue is held in the WordPress options, which is often cached by database- and object-type caches. These are often controlled by a WordPress plugin, where you can clear the cache before doing your import.
Migrating from Another Database
If you need to import records from another database, take a look at this article: Migrating an Existing Database into Participants Database
Exporting Records
The plugin can export a CSV file from the admin “List Participants” page. All fields that have the “CSV” checkbox checked in the field definition (Manage Database Fields page) will be included in the export. You can assign or unassign multiple fields from the CSV export on the Manage Database Fields page by selecting the fields and using the “with selected” control to add or remove the fields from the CSV export. This does not affect imports: any valid field in the CSV import can be imported, regardless of the CSV setting of the field.
You can choose with records to export by using the list filter: if the list is getting filtered (for instance, with a date range) only those records will be exported.
If you are planning to export records from the plugin and then re-import them (for instance, if you wanted to edit them in a spreadsheet application) you should export them with the record ID numbers. Then, when you re-import them, make sure the CSV import setting is set to overwrite matching records and that the matching field is “Record ID.”