Import Plain Text Passwords - Help & Help Needed

Down to the last lap of my Joomla/CB site launch, and black flagged... :ohmy:

There are a lot of messages about this topic on this forum and others, and I think the short answer to my question is "use phpMyAdmin", but I wanted to make sure that I'm on the right track and there isn't some other problem before heading in that direction.

I started by installing and using JUICE thinking that would take care of it, but it would only import the first user of my CSV file (I tried tab separated as well for good measure). The text file is formatted properly, so I'm not sure why it wouldn't import the users. The first user imported couldn't login, which was expected, as the password was in plain text rather than MD5. I assumed I could import the users with JUICE, and then convert the passwords to MD5 doing something like this or this . After import, I expected to deal with problems like this one . I didn't expect an import problem, so I am concerned that there may be a bigger issue.

A user in this post had exactly the same scenario as I do, passwords in plain text, but unfortunately, the conversation drifted and no one followed up about whether or not the instructions provided were correct.

With JUICE out of commission, another user confirmed that there were other scripts that worked, so I followed the link to this thread . I downloaded the "mambo_import" script, which by default imports the username as the password, but following a tip, changed the relevant bit of code to:

[code:1]
$username = mysql_escape_string($data[0]);
$password = md5(mysql_escape_string($data[1]));
$name = mysql_escape_string($data[2]);
$email = $data[3];
[/code:1]

I thought this would convert the plain text passwords to MD5 on the fly, and it worked fine in testing, but like JUICE would only import the first user into the database. This time, the user information did not show up in the main Joomla admin control panel. Even after deleting "all registered users" with JUICE and trying again the first user wouldn't show up imported with either script (new signups are fine, thankfully).

Before I start fiddling around with phpMyAdmin, is this something I can solve that way or is there another problem? Is there an alternate solution, like a script that just converts a file full of plain text to MD5, which I could then import back into my offline database for export, and then import into Joomla/CB through JUICE? I found this , but nothing suitable for a large list of users.

Obviously, I'm anxious to get my new site online, but the suspense is killing others as well.

Hope the details I've provided can help others in the same boat. Thank you very much for any tips or assistance!

Post edited by: bdkyle, at: 2006/05/22 19:24

Post edited by: bdkyle, at: 2006/05/22 19:26

Well, I'm well into full-scale "fiddling around" with phpMyAdmin...

Both JUICE and the mambo_import script write to the jos_users, jos_core_acl_aro, and jos_core_acl_groups_aro_map databases. Since I started with Mambo, mine are the same but prefixed with "mos" as expected.

I'm not sure if the problems I've had are because JUICE isn't compatible with the CB 1.0 release, because I originally started with a Mambo install, or because there were lots of now deleted test users hanging around in the three databases listed above, and jos_comprofiler (CB's database). JUICE doesn't seem to work with anything I throw at it, but after cleaning out the databases of dead users, and then creating one user through the standard registration form, I was able to use the stock mambo_import script to import a handful of test users. They showed up in the SQL backend, the admin control panel, and after syncing the Joomla database with CB's, were able to login using the default script's username/username as password. They are not properly greeted by name, however, when they login.

So, I'm not sure if my MD5 encoding attempt of existing passwords was a problem or not, but I think I'll give that another shot and attempt to modify the script to import first and last names separately with clean databases. Is this the right direction?

I would greatly appreciate any tips you can provide. Thank you.

On second thought, I'm not quite sure how CB handles the whole single name field stored in Joomla - first, middle, last stored in CB split.

I may need a life preserver here...

Surely someone out there has imported users from a plain text file into a new Joomla/CB site.

As I mentioned previously, I've been able to import test users successfully using the stock mambo_import script, which just imports their username as their password in MD5, and these users can login, but they're not greeted by first name and the first and last name fields in CB aren't populated in the user profile edit screen.

The names are visible in the admin control panel both in Joomla and CB. Within phpMyAdmin, their names properly appear in mos_users and mos_core_acl_aro, but appear as NULL in mos_comprofiler. This has to be the problem.

Even the smallest tip on how to fix this would be greatly appreciated. Thanks!

It's never good when you're the only one posting in a thread. I figured it out and thought I would post the solution here to help others in the same boat later.

Fortunately, Beat was kind and provided the hint that CB wasn't parsing the name information from the Joomla database, and it would have to be imported separately. I couldn't figure out how it was parsing the names and didn't know how it would handle names like Erik van der Sloot, so it was good to know that it wasn't.

So, the solution I found was to export username, *fullname*, e-mail, and password fields from my offline database in a CSV file, modestly customize the stock mambo_import script to encode the passwords in MD5 (as noted previously), and use the script to import this information into the mos_users, mos_core_acl_aro, and mos_core_acl_groups_aro_map SQL databases.

*Pay careful attention to the ID numbers assigned by Joomla*. Then assign these same numbers in your offline database to the appropriate records using a serial numbers function.

Export a second CSV file with the fields ID, User ID (which should both contain the ID number that corresponds to the one created by Joomla), firstname, and lastname (instead of fullname). You'll probably find it easiest to use a text editor to find all of the "," delimiters and replace them with ";" as it is the default used upon import (note that I mean literally "," including the quotes). Then import this file directly into the mos_comprofiler SQL database using phpMyAdmin. Within phpMyAdmin, you also will want to change the default line wrap setting to "\r" (for "return", it should have a backslash in front, but this forum kills it upon post) so it will import properly.

If you have any problems, and have to redo the import, it seems to be helpful to create a new user through the standard registration form first to start with a "clean slate" for import.

Your mileage may vary, but this worked for me. I hope this is helpful. Karma me up.

Post edited by: bdkyle, at: 2006/05/27 18:25

Post edited by: bdkyle, at: 2006/05/27 18:26

Thanks for sharing this method with the community, specialy as monologue poster.