Skip to:
Content
Pages
Categories
Search
Top
Bottom

Urgently need a way to export user data into usable format!

  • Avatar of jvoss
    jvoss
    Participant

    @jvoss

    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?

Viewing 11 replies - 1 through 11 (of 11 total)
  • Avatar of techguy
    techguy
    Participant

    @crashutah

    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.

    Avatar of xsn0w
    xsn0w
    Participant

    @xsn0w

    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?

    Avatar of Magi182
    Magi182
    Participant

    @magi182

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

    http://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.

    Avatar of brentjacobsen
    brentjacobsen
    Member

    @brentjacobsen

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

    Avatar of peeld
    peeld
    Participant

    @peeld

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

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

    Avatar of brimfulof
    Brimfulof
    Participant

    @brimfulof

    It’s actually pretty simple. It’s explained very briefly in the plugin’s installation page: http://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.

    Avatar of bjkitchin
    bjkitchin
    Participant

    @bjkitchin

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

    http://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

    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

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

    Avatar of qstudio
    qstudio
    Participant

    @qlstudio

    You could try this plugin also:

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

Viewing 11 replies - 1 through 11 (of 11 total)

The topic ‘Urgently need a way to export user data into usable format!’ is closed to new replies.