How to export data from Joomla or Community Builder to mail merge and create address labels

Published in Joomla

Jooma-community-builder-to-word-mail-merge-addresss.jpeg

OK. You want to export data from Community Manager for Joomla in order to print address labels right? Because they only have the customers address on them, you don’t need anything other than the name, street, post code (the usual address lable data), heck you probably already tried exporting data out of community builder using CB Juice and found that it can export the data but it’s not in an address label friendly way and it may not collate the date the way you want so…

Here is an alternative way of exporting you data from Joomla Community Builder in .csv to Mail Merge with Microsoft Word and make Address labels (it’s a bit messy but it will get you there).

First a recap to be absolutely clear what this guide is and what it isn’t. This is for people who want an alternative way to export data from Community Builder (a Joomla Component) in a .csv format so that they can mail merge in Microsoft Word (or similar) to easily create address lists/labels. If that sounds like you carry on. 

Note: Step one write a MySQL query to extract the data from Joomla / Community Builder. I’’ll show you mine (“that’s what he said”) so you can work from that. I know little about SQL so you should know it took me a whole morning to write and you may need to invest an equal amount of time learning a bit about SQL scripting to extract the address labels in the way that you want. Do worry, sure this may be technical, but you CAN do it, or at least it shouldn’t be too hard to find someone who can help you.

1) First, before we write the script, we need an easy way to execute and SQL script on the database from the Joomla back end. I installed a component called ArtAdminer. It’s a simple Joomla component which you can install from the administrator backend of Joomla in the same way you install any other component. (Joomla admin>extensions>Install/Uninstall).

2) Now that it is installed you can go to it in the Joomla backend (component>ArtAdminer>admirer). Click one the database in the list (there should be only one), and then click SQL Command on the left. This is the place you will put your SQL script.

3) Now the fun begins. In this box you want to paste an SQL script which will extract the data you need. The order of fields used is not so important because you can re-order the data during the mail merge. Let me start by giving you my example:

SELECT name, cb_direccin, cb_poblacin, cb_cdigopostal, cb_provincia
FROM jos_users a INNER JOIN jos_comprofiler b
        ON a.ID = b.ID

The word select means to “grab that data”. 
The words “name, cb_direccin, cb_poblacin, cb_cdigopostal, cb_provincia” are all fields in Community Manager, you can easily find the ones you want by going into the Joomla administration and clicking Components>Community Builder>Filed management. There, under the “name” column, you can see what the names of the fields are for the data you need to use in the address labels. Note: the field called “name” is a Joomla default field and is stored in a different place, this is why you need the next line…
“FROM jos_users a INNER JOIN jos_comprofiler b” is telling the server where to find the fields in line one. It is saying that these fields come from two different places (the jos_users and jos_comprofiler tables). With the latest Joomla versions these table will be different for every different Joomla website. In order to make Joomla more secure against hackers, the tables now have random letters instead of the “joe” part. You will need to look at your database to find out what they are. To do this click on the ArtAdminer component you installed in the Joomla Admin and click on your database. It will list all the tables so you can look for the ones ‘???__users’ and ‘???_comprofiler” (note: _users, is the Joomla users table where the “name” field is kept and _comprofiller is the Community Builder table where the fields are that you need for your address list).
The line “ON a.ID = b.ID” just means only one entry per unique ID. In essence tells the server to extract only one address per person (unique ID)

Your code will look like something like this to extract address labels from Community manger (<?> shows the things YOU WIL NEED TO CHANGE)

SELECT name, <address>, <city>, <post-code>, <province>
FROM <????>_users a INNER JOIN <????>_comprofiler b
       ON a.ID = b.ID

Just so you understand:
<address> is the name of corresponding address field in Community Builder (without the <>)
<city> is the name corresponding city field in Community Builder (without the <>)
<post-code> is the name corresponding post-code field in Community Builder (without the <>)
<province> is the name corresponding field in Community Builder for the province (without the <>)

Also change:
<????> to the prefix on your Joomla tables (instead of jos_users you might have xyz1_users). Make sure it is the same as in your database. (without the <>)

You can play with the SQL a bit, it’s only extracting data from you database and will spit out errors if it can’t understand what to extract. Test it but clicking the execute button on the SQL Command window of the Joomla Adminer component.

Export the Data as .csv:

Once you sql code is right, the data should appear easy for you to export and later mail merge in word to create address lists.

When you see this you address list correctly extracted, just scroll to the bottom of the data and click export. It will appear in a comma delimited format ready for you to copy and pase into a text editor and save as a .csv

Mail Merge:

Word-Mail-Merge-Manager-screenshot.jpg
Once you have these addresses in you .csv file, you can do a mail merge. I won’t explain the process for mail merging in word because there are plenty of sites that can help if you don’t know how. I hope you got you community builder data exported ok, but if you didn’t leave a comment and I’ll try to help.