Export Buddypress xProfile Data

We recently added additional fields to BuddyPress profile. The administrator of the website wanted to see all members profile data in an Excel spreadsheet.

Although there is currently no way to view the data in this format it is simple to extract it from the database.

Here’s the SQL statement we used in phpMyAdmin:

Remember to backup first and any code used is done so at your own risk.

SELECT

user_id,

GROUP_CONCAT(if(field_id = 1, value, NULL)) AS ‘Field1’,

GROUP_CONCAT(if(field_id = 2, value, NULL)) AS ‘Field2’,

GROUP_CONCAT(if(field_id = 3, value, NULL)) AS ‘Field3’,

GROUP_CONCAT(if(field_id = 4, value, NULL)) AS ‘Field4’,

GROUP_CONCAT(if(field_id = 5, value, NULL)) AS ‘Field5’,

GROUP_CONCAT(if(field_id = 6, value, NULL)) AS ‘Field6’,

GROUP_CONCAT(if(field_id = 7, value, NULL)) AS ‘Field7’,

GROUP_CONCAT(if(field_id = 8, value, NULL)) AS ‘Field8’,

GROUP_CONCAT(if(field_id = 9, value, NULL)) AS ‘Field9’

FROM wp_bp_xprofile_data

GROUP BY user_id;