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<sizeof($fcontents); $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!