Importing User Data Into Joomla

I had a need to import user data into Joomla with the Community Builder RC1 component installed. After a bit of discovery I found a method.

First, I had to export my users from a proprietory Access database to something I could import using phpMyAdmin. I ultimately found that MySQL seems to like tab delimited files better, since phpMyAdmin likes to have a character set surrounding each cell for import purposes, and Excel doesn't export csv that way (at least not to my knowledge).

Before any of these changes were attempted, I exported the tables marked for updating. Backups are good juju....

Once that was done, I had to form the data to prepare for import. The first table I imported was jos_users. I used a nice php script found somewhere on the net.

Replace $TABLENAME, $FILENAME, $DATABASENAME, $PASSWORD, $USERNAME, and $HOSTNAME with appropriate values.

# first get a mysql connection
$dbh=mysql_connect ("$HOSTNAME", "$USERNAME", "$PASSWORD") or die ('I cannot connect to the database because: ' . mysql_error());
mysql_select_db ("$DATABASENAME");

$fcontents = file ('./$FILENAME');
# expects the csv file to be in the same dir as this script

for($i=0; $i $line = trim($fcontents[$i]);
$arr = explode("\t", $line);
#if your data is comma separated
# instead of tab separated,
# change the '\t' above to ','

$sql = "insert into $TABLENAME values ('".
implode("','", $arr) ."')";
mysql_query($sql);
echo $sql ."\n";
if(mysql_error()) {
echo mysql_error() ."\n";
}
}
?>

Use the UPDATE command to convert passwords to MD5 in a SQL query like this:

UPDATE `jos_users` SET `password`=md5(`tmp_pass`)

That would set the password field in jos_users to an md5 hashed value found in a temporary column called tmp_pass. You could then delete the unused column.

I then found that there is yet another couple of tables to update called jos_comprofiler, jos_core_acl_aro, and jos_core_acl_groups_aro_map. Do the updates to these tables in this order, since it's easy to spot the patterns in data, so you can modify your original Excel data to fit.


Fun!