Importing data from Excel, CSV sheet
I suggest you install phpMyAdmin on your server (or use equivalent) – http://www.phpmyadmin.net/home_page/index.php. Then, assuming there’s at least one profile with all profile data completed, export that data/table as a CSV-formatted file.
You can then load this into Excel and can use it as a base how to build the file.
Be very careful about certain characters in your text – ‘ ” and TRUE/FALSE boolean values spring to mind. If this is beyond your current understanding, a quick Google for “mysql special characters tutorial” or similar is a good idea.
You can import anything you want into a mysql table. The catch is that tables relate to each other. The profile fields you see are composed of three tables that interrelate. We’ve got profile groups, fields and field data. Groups are composed of Fields and Fields have Data. They are all linked to each other.
What is the source data you are working with?
@DJPaul: I tried that. I created one profile and exported the database using phpMyadmin. But the excel sheet looks too confusing. I’ll try this once more time though.
@Burtadsit: Yes, that’s most confusing.
Let’s say, I’ve created following fields in buddypress:
My old database too; has all this data. How do I go about importing? Is there any better way than excel import?
Yes, I am looking exactly for this functionality for my client.
My client needs to be able to 2 things:
a) they are a small nonprofit and have 600 members the take care of. They need to hopefully import usernames and email addresses to automatically generate new users for all those 600 members.
b) Then they need to import an excel sheet full of all the xprofile (extended profile) data for each of those users.
Is there a plugin (I searched but could not find one…) that can help me automatically generate lots of usernames/accounts?
Is there also a better way to import and export all the extended profile data?
If a WPMU developer is available to help, I can provide more details to have this project done!
A quick google reveals several bulk-import scripts and plugins for WordPress/MU. Have a look at http://www.dagondesign.com/articles/import-users-plugin-for-wordpress/.
Regarding bulk import of xprofile data: Manjor Kumar had done some BuddyPress-specific import plugins for a very early version, but I can’t vouch for if they still work:
@DJPaul: tks! weird… my search terms seemed to have been slightly off.
I installed the “Bulk Import Members (Users)” ( https://wordpress.org/extend/plugins/bulk-import-members-users/ ) but it triggered an install error:
Warning: require_once(bp-core.php) [function.require-once]: failed to open stream: No such file or directory in /var/www/my_site_name/wp-content/plugins/user-import-for-buddypress-all-fields/bp-user-import-adv.php on line 29
I searched more and found an alternative version of this (version 1.0.1) the the developer’s own site and it installs fine:
The other 2 seem fine too but not as comprehensive and new as the one above. I also have used dagondesign in the past and I like their plugins, but because it it not in the repository I fear about using these types of plugins sometimes.
I just re-installed my Buddypress site and switched from WPMU+Buddypress to just normal WP+Buddypress. The plugin here now appears to not work:
I manually uploaded it to my server and activated it and all seemed fine. But I can find no link to the plugin within my adm area. Maybe it does work fine but I can not find the link to where to upload users…. anybody have an idea?
Most of the early BuddyPress plugins were WPMU specific as that’s what BuddyPress ran on, and whilst a lot will work with regular WordPress, it looks like you have one that doesn’t.
@DJPaul: tks for the info, though it doesn’t make me smile
I searched for other plugins that allow for bulk import that include xprofile fields, but I find none. Any thoughts on ways to handle this? direct import via mysql is not a good solution as my client needs as wysiwyg as possible.
Are we sort of in a “dead zone” now as people wait for WP 3.0 before coming out with plugin updates?
You will see in the comments that I asked @boonebgorges if he thought it might be possible to use a regular CSV instead of one generated by Ning. He responded that in theory it should work because the only two required column headers are “name” and “Email” and any other columns would “sniffed out”.
WARNING: I have not tried this yet and therefore can not tell you if this actually will work.
If you do try this and have success please let me know.
@ffemtkl – good creative solution I am reading the docs now and will attempt imports and tweaking soon. I will let you know the result.
Ok, I have feedback for you on the “Import from Ning” plugin if I use it to upload a normal CSV file that is not originally from Ning. I hope the developer @boonebgorges of the plugin reads this to give him some ideas about expanding the ability to use this
The plugin does work, but not entirely. In your CSV columns, you must have Name and Email as the first and second columns, respectively. If you do not have them in that order, it will not import. That is an easy thing to do though, so not a headache
Here are some problems I found:
1) I want to create usernames for my imported users. How are usernames generated for users when you import them? The “Import from Ning” plugin takes the ‘Name” field and makes that the username automatically. So if your name is “George Bush”, it will automatically create a username called “georgebush”.
However, I do not want this. I have separated by “Name” field from the “Surname” field, so my user would have Name=George and Username=George. I do not want that. Instead I want to create my own usernames. However if I upload a column called “Username”, the importer does not recognize that.
2) The “Import from Ning” plugin does a decent job of automatically aligning your xprofile fields with the columns in the CSV file. For selectbox fields, there is no problem. For textbox fields, there is no problem. For radio fields, there is no problem. However there are problems with these two fields: datebox and checkbox.
What happens if I try to upload data into datebox? I tried uploading in the formats “1 January 1950” and “January 1, 1950” but the plugin ignored those, so I am unsure how to upload dates correctly. When I tried “1 January 1950”, it changed that and instead displayed “January 1, 1970”, so I feel that maybe I am near a solution…. however when I tried “January 1, 1950” in my CSV field, it just left that entire area blank… I have no idea about the correct protocol for uploading dates tat include months, days, and years together. But I feel I am near a solution….
What happens when I include data from a checkbox? Nothing is included. For example, I have a field that is “Favorite Foods” and users can select more than one choice. In my field, I am using “~” as the separator. So I have “Pizza~Noodles~Fruit” in my CSV field, but the plugin ignores this. Therefore, for the “checkbox” field data, I am unsure of the correct protocol for how to prepare my data to upload using this plugin.
I feel that this is a great plugin and we only maybe need some help figuring out the details from above.
@wordpresschina – I also tested over the weekend and it worked great for me. But I have one issue I would love to be able to solve…
The e-mail sent out to the new users is the standard WP e-mail (with username, password (sutogenerated) and link to standard WP login screen) and not the one from BuddyPress. I would like to do the following.
1) Change the text of the e-mail.
2) lead people to a custom login page
I know this is not part of the plugin for Ning, but any help would be greatly appreciated.
By the way, unfortunately I cannot help you with your two issues, I don’t mind that the username is the name (it actually helps me) and I am not importing any date information, so I don’t have the issue. The best I can do is wish you the best of luck.
Any help on my two above issues would be greatly appreciated.
@ffemtkl – to customize those emails, what about trying this plugin:
When you were importing info, were you importing checkbox data? That was alsop problematic for me and I have no idea what format I should prepare that data so it goes in nicely….
@wordpresschina – Thanks for the suggestion, this is exactly what I was looking for. I really do not understand one thing. Why when someone wants to register they get redirected to the Buddypress version of the registration, but when someone wants to login they go to the standard WP login page? Maybe there is something wrong with my install
Unfortunately, I am importing very basic information so I am not having the same issues as you. The only “special” info I am importing is for s drop down selecting menu. It should be noted that it does not force matching, meaning if the field requires 1 – 10 and the CSV file has 12, it will import the 12. The import only really matches up the columns. FYI… if you type something wrong in the top row like Website instead of “company website” the import will actually create a new xprofile field.
Hope this helps (or at least answers some questions.
@wordpresschina – Thanks so much for your work on this, and especially for sharing your work on the forum. Very glad you were able to make it (mostly) work.
When I built the plugin, I didn’t make any effort to differentiate between different kinds of profile fields (checkboxes vs radio buttons vs text boxes etc). It’s impossible to get this info merely from the Ning export (at least, the old style CSV export), so it would require prompting the importing admin for each new field. Not impossible, but it required a lot of coding that I didn’t want to do because I wanted to get the dang thing out there so people could use it
I’m hopeful that the upcoming Ning network exporter will produce more robust data that will allow easier, and more fine grained, imports into BP.
Whether that can be expanded to more general CSV imports will always be a tricky issue. Ideally, an importer would be designed to handle a generic import format, so that importers for new formats would only have to convert to this standard format – all the BP-specific stuff would be done with the all-purpose importer. I’m afraid I don’t have the funding to build such a plugin right now, but it will definitely be crucial to the growth of BP as a platform in the months and years to come.
What you meant by ning network? Could you explain how it will work ?
Unfortunately, I cannot get it to work so far. I have 870 users with about 30 different profile fields from another CMS which I need to import, so it would be really important not to have to do this by hand… Could you provide an example csv file which shows how it should be structured? Are the names for the columns just given in the first row?
I’ve been trying to use the Import from Ning plugin to import users from a CSV file. I created the CSV file manually – the users were originally listed in a plain html file.
My problem is that when I try to import, the plugin says:
“In order to run the importer, you must first use your FTP program to upload the contents of your Ning export into a directory called ning-files in your wp-content directory. The plugin couldn’t find a valid members file at /home/mp83lbho/public_html/w/wp-content/ning-files/ning-members-local.json, which probably means that you haven’t uploaded the files to the right place. Upload your unzipped export to the json directory, and try visiting this page again.
If you’re sure that your files are in the right place, then the most likely explanation is that your ning-members-local.json file is invalidly formatted. Contact Ning to ask about getting a new one.”
I’ve tried creating a blank json file with that name, in the right location, with no luck.
Creating a json file itself seems pretty simple too (I’ve been looking at http://json.org/example.html), but I’m confused as to what it needs to contain – do I add all the user info into the json, or is the json file just to tell the plugin what information from the CSV goes where?
If anyone could throw some light on this that would be great, or upload a sample json file with accompanying CSV.
- The topic ‘Importing data from Excel, CSV sheet’ is closed to new replies.