Our nonprofit organization is using Buddypress for our Student Program’s online community. We are also using it to gather all of the student members’ vital information—contact info, university, major, etc.—using many extended profile fields, some required, some not. We’ve put many hours/weeks/months into creating and promoting this online community, but have come to realize that there is apparently no way to export the data we collected into a usable format.

The users’ data is stored in three different tables in the SQL database: wp_users (with the basic login info), wp_bp_xprofile_fields (with the labels of the extended profile fields), and wp_bp_xprofile_data (with the actual extended profile field data). We need the data in either a single Excel spreadsheet or Access database (ultimately the data goes into our Raiser’s Edge and Convio databases). Currently we can export the three tables as individual spreadsheets, but have not been able to then combine the data into one usable document, with the Profile/Extended Profile Field labels across the top row, and all the data (from the wp and bp data tables) for each user across one line in the spreadsheet.

In the absence of any built-in functionality or plugin, I assume we need to write a SQL query of some kind that does the following:
1 – copies wp_users into a new table.
2 – adds new columns to this table for every field from wp_bp_xprofile_fields where wp_bp_xprofile_fields/type value is other than “option” (“textbox”, “selectbox”, etc.).
3 – takes value from wp_bp_xprofile_data/value and adds it to the correct user in the correct column:
3a – wp_bp_xprofile_data/user_id corresponds to wp_users/ID
3b – wp_bp_xprofile_data/field_id corresponds to wp_bp_xprofile_fields/id
3c – Whenever wp_bp_xprofile_fields/type value is “option”, wp_bp_xprofile_data/field_id will instead correspond to wp_bp_xprofile_fields/parent_id

However, I don’t know much at all about SQL queries. Has anyone had any luck exporting the user data in this (or any other) way? Does anyone have any ideas that could point me in the right direction?

[@crashutah Participant 14 years ago]

I’m no xprofile field expert, but can’t you just create a page template, have the page template go through the user loop and on each loop display each of the xprofile fields that you want for that user in a table. Copy the table to excel and you’ve got it. There are other ways, but that seems like the simplest to me.

It does require you to understand the user loop and displaying the xprofile fields, but that can be modified from the list of members page and the profile page which displays the xprofile data.

Or you could hire someone like Paul or Boone or other BP developer who could probably crank this out pretty quickly.

[@xsn0w Participant 13 years, 7 months ago]

Yes this is exactly what I want to accomplish to a T. I am just as surprised as you, @jvoss, that this functionality is not standard or that someone has still not created a plugin to simply export all user profile data to a .csv or .xls? Have you found a solution yet?

[@magi182 Participant 13 years, 5 months ago]

I was able to hack this plugin to accomplish most of what I needed:

https://wordpress.org/extend/plugins/bp-export-users/

You have to edit the array in the plugin file to use your buddypress profile fields (you can add or delete), but other than that, pretty slick.

[@brentjacobsen Member 13 years, 5 months ago]

@maqi182 – Would you mind posting an example of your code? This is also something I need to accomplish

[@peeld Participant 13 years, 1 month ago]

I’d love the edits you made as well – I like the plugin but need roles/levels to be returned.

[@triznic Participant 13 years ago]

Any update on this. It is exactly what I need!!

[@brimfulof Participant 13 years ago]

It’s actually pretty simple. It’s explained very briefly in the plugin’s installation page: https://wordpress.org/extend/plugins/bp-export-users/installation/

Step-by-step:
1. Download the plugin to your computer.
2. Unzip it and open the file bp-export-users.php in a text editor (notepad, notepad++, gedit, etc.)
3. Find these lines:
$this->wp_fields = array(
'ID',
'user_login',
#'user_pass',
'user_nicename',
'user_email',
'user_url',
'user_registered',
#'user_activation_key',
'user_status',
'display_name',
'spam',
'deleted');
4. Add the names of any custom WordPress user fields you want in ‘inverted commas’ before the closing bracket ) making sure you put a comma in between the fields. E.g. ‘deleted’, ‘custom field’, ‘custom field2’)
5. Find the lines:
$this->bp_fields = array(
'Name',
'Telephone',
'Job Title',
'Organisation',
'Region',
'Primary Discipline',
'Grade',
'A bit about you',
'twitter',
'flickr');
6. Add the names of any custom BuddyPress (xProfile) fields you want to export in the same way.
7. Save the file.
8. Zip the folder.
9. Install the plugin in your WordPress installation using the upload method.
And you should be away.

[@bjkitchin Participant 12 years, 8 months ago]

I found this plugin to also be helpful, particularly at identifying the field names very clearly.

https://wordpress.org/extend/plugins/navayan-csv-export/

Simply install, go to tools, find the wp_bp_xprofile_data and wp_bp_xprofile_fields and export.

Actually with that info you can do quite a bit. But I think the profile fields is a great reference to complete the code array( above.

Warm regards,

BJ

[@jon73 Participant 12 years, 2 months ago]

Anyone know how to add user role to BP Export Users so it shows in the csv. I have tried adding ‘Role’, ‘wp_capabilities’ (user meta in the database) but although the column is added no data is pulled into the csv

Please can someone respond asap.

Thanks

[@jimmmy Participant 11 years, 7 months ago]

@brimfulof thank you! your instructions worked. Piece of cake.

[@qlstudio Participant 11 years, 4 months ago]

You could try this plugin also:

https://wordpress.org/plugins/export-user-data/