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.

CAVEAT: ALWAYS MAKE A BACK UP BEFORE YOU DO THIS KIND OF STUFF.

Problem: I had a site with 1200 articles and every one of them had hard coded inline styles. Fonts, font sizes, colors, underlines, you name it. It was a 6 year old site with multiple contributors all using every button available in the text editor.

Of course I wanted the articles to use the styles in my css file, but inline styles override style sheets. What to do? I tweeted @NoNumber_nl Peter Van Westen from NoNumber and asked him if there was a way to use his excellent tool, dbReplacer to find and replace all of the styles and he kindly responded with the code below.
Hack:
With regular expressions, use: \s*style="[^"]*" or \s*style=".*?" (also grabs the extra whitespace before the style attrib)
If memory serves, I might have been left with style=" ", but I simply used the dbReplacer again to remove all of those. I was then left with all of the <em><strong><p><br> tags in place, but no fonts or sizes.

Since taxonomy is important to Search Engine Optimzation, I also used it to find all of the mis-used <H1> tags and replace them with a more appropriate tag.

p.s. I'm also using his Sourcerer plugin to add source code to my articles