CB Juice Problem and Hack

Importing Joomla users with CB Juice and Community Builder and handline an error with CB Juice2 v2-0-4
This article was written November 10, 2011

The Problem:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' FROM jos_users u LEFT JOIN jos_comprofiler c on (u.id=c.user_id)' at line 1 SQL=SELECT u.id,, FROM jos_users u LEFT JOIN jos_comprofiler c on (u.id=c.user_id)

I had already set up all of my extra fields in Joomla 1.7 then found I could not export a demo csv due to the error above. I had never had this problem with J1.5 and thought I would see if CB Juice would work on an demo install of Joomla 1.5 I wasn't using.

The Hack:
There area very confusing version numbers when discussing CB Juice and Joomla and I will attempt to make this as clear as possible.
CB Juice v1.7 installs on Joomla 1.5
CB Juice v2 installs on Joomla 1.7
If you have the wrong versions installed you will get errors and this fix won't work until you set the versions straight.

I exported my comprofiler tables from the 1.7 database in phpmyadmin, changed the prefix to jos_ using Notepad++. I then imported those tables to my J1.5 database. Then I installed CB and CBJuice into J1.5 and was able to export a demo csv file. I add all of my users data into that demo and am now able to import that into my 1.7 site with CB Juice.

The next issue is if the user didn't have an email address, CBJuice would normally make one up for you, but this did not work in the CBJ2 version and I had to create several dummy and unique emails. I have also recently used this for another site and even though I did not check the box that said to notify the users, it still sent out emails with the user name and ****** as the password. I had 200 confused people on my hands. This time, to prevent having 532 confused people, I changed the @ to 111@111. This way if emails were sent they would go to non-existant accounts. I then used dbreplacer from NoNumber.nl to do a find and replace in the user email table and replaced 111@111 with simply @.

You could tweak this hack to import users in general and then uninstall CB.

This is a very ugly work-around and if someone knows a better way to import to Community Builder, please post.