Alright,
The first thing I will say the Participant Database plugin has a specific purpose, and is most likely not your solution. The Participant Database plugin is pretty awesome, but if you are looking for something that can fit a wider set of solutions look into PODS Framework plugin.
With that said here is how I setup this up.
I am using this plugin to simply display an employee schedule. The software that is used here to schedule employees is beyond limited, but we wanted a way to display the schedule online. Luckily the software was capable of exporting the schedule to CSV every time the schedule was saved.
Make sure the CSV you have is setup the way you want it setup. The CSV file that I got from the scheduling software had a lot of extra data that was not needed. So, I created a PHP script that read in the CSV, stripped out the data not needed, and then wrote the data to a new CSV.
Once your CSV is set go to the “Manage Database Fields” under the Participant Database plugin. You need to delete whatever fields are under Participant Info Fields, and put in the same fields that are in your CSV.
The website I maintain is hosted on-site, and runs on Ubuntu 12.04. On the server I setup a chron job that looks like this:
cp -f /path/to/file /place/to/copy/to && php5 /path/to/script && mysql -uusername -ppassword database name < path/to/import.sql
The first command is copying the csv off the network onto the server.
The second command is running the php script I created to create the stripped version of the csv.
The third command is logging into sql and into the wordpress database and telling it to run the commands in the import.sql file.
There is probably a much more secure way to do this than putting your sql password in a scheduled task . . . I just haven’t taken the time to improve the process.
Here is a copy of my import.sql
truncate table wp_participants_database;
LOAD DATA LOCAL INFILE ‘/path/to/csv’ INTO TABLE wp_participants_database FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (field_1, field_2, field_3, field_4);
Explanation of import.sql:
truncate table – this deletes all data in table
LOAD DATA LOCAL INFILE – This tells sql to import the csv
INTO TABLE – this tells it what table to import the csv to
FIELDS TERMINATED BY ‘,’ and ENCLOSED BY ‘”‘ and LINES TERMINATED BY ‘\n’ – just keep these in here to be safe
IGNORE 1 LINES – if your csv has a header (first row is the name of your fields) then include this.
(field_1, field_2, field_3, field_4) – the name of these fields are the same as the field names you listed in Manage Database Fields and your csv.
I don’t know if I provided enough detail. Let me know if this helps or not. If you have a specific question let me know.
Here are some of the things I read up on to help me understand what I was trying to do.
mySQL
mysql 5.5 LOAD DATA INFILE syntax
PHP – if you plan on making a script
fgetcsv
fputcsv
I can share my script if anyone is interested. It probably doesn’t follow best practice but it gets the job done.
