Import Address Books into Roundcube
Using this method, you can import address books for your users or even populate the Global Address Book if you happen to have installed the plugin.
If you are using the Global Address Book plugin for Roundcube, you can populate the global address book with an import of a spreadsheet or database directly into the database. To do this, you will need to format you import file in a manner consistent with a .sql import script. This will allow you to forego creating all the entries by hand in the web interface.
Preparation
Finding the global addressbook user
If you are importing to the Global Address Book, the username is “[global_addressbook_user]”, you can skip the rest of this paragraph. If you are importing for a specific user, you will need to find the username in the database server but likely the user is the first part of the user’s email address. If you want to validate the user, log into the system-mysql database using the method in the next section and run the following queries:
use roundcubemail; select * from users;
Formating the Import File
You will need to export your data and create a text file (for example ‘import.sql’) that will have all the data that you intent to import. The format of an import will look like this if you are importing to the Global Address Book:
insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testing','[email protected]','testing','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=WORK:[email protected] EMAIL;TYPE=INTERNET;TYPE=OTHER:[email protected] TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]";
These are the valude that you will need in the query:
- changed
- NOW(): This value is simple the moment that you create the record, the static value of ’NOW()’ will take care of it for you so don’t worry about date/time format
- name
- ‘testing testing’: This is the contact’s full name
- email
- ‘[email protected]’: This is the contact’s email address
- firstname
- ‘testing’: This is the first name of the contact
- surname
- “Guy”: This is the last name of the contact
- vcard
- This is the big blob. See example below.
- user_id
- We will pull this value (which is a number), dynamically from the database using the username instead of looking up the number.
Example raw VCard
BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=WORK:[email protected] EMAIL;TYPE=INTERNET;TYPE=OTHER:[email protected] TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD
Here is a long example of a 5 user import (note, if you have trouble importing, you can split up your file and address syntax issues):
insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testingi','[email protected]','testing','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=WORK:[email protected] EMAIL;TYPE=INTERNET;TYPE=OTHER:[email protected] TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testing1','[email protected]','testing1','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example1 Guy EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=WORK:[email protected] EMAIL;TYPE=INTERNET;TYPE=OTHER:[email protected] TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing testing2','[email protected]','testing2','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=WORK:[email protected] EMAIL;TYPE=INTERNET;TYPE=OTHER:[email protected] TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing3 testing','[email protected]','testing3','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=WORK:[email protected] EMAIL;TYPE=INTERNET;TYPE=OTHER:[email protected] TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]"; insert into contacts (changed,name,email,firstname,surname,vcard,user_id) select NOW(),'testing4 testing','[email protected]','testing4','Guy',"BEGIN:VCARD VERSION:3.0 N:Guy;Example;;; FN:Example Guy EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=HOME:[email protected] EMAIL;TYPE=INTERNET;TYPE=WORK:[email protected] EMAIL;TYPE=INTERNET;TYPE=OTHER:[email protected] TEL;TYPE=CELL:123-555-1234 TEL;TYPE=home:123-555-1111 TEL;TYPE=work:123-555-2222 ADR;TYPE=home:;;5 Anyplace Drive;Someplace;NY;55555;USA URL;TYPE=homepage:www.example.com X-SKYPE-USERNAME:guys.skype.address END:VCARD",user_id from `users` where users.username = "[global_addressbook_user]";
If you are importing to a specific user (for example ‘bsmith’), you will replace “[global_addressbook_user]” with “bsmith”.
Password
You will need the root password for the system mysql/mariadb database server. Run the following:
cat /var/clearos/system_database/root
Take note of this password. The output will look like this:
password = 1vzF9cGDdCcRo8ifjuhg1fjlgIJ4r2MgXL2GAMOz
Importing
Once your data is assembled, you will be able to import the file by using something similar to this:
/usr/clearos/sandbox/usr/bin/mysql -p roundcubemail < /root/import.sql
Testing
Inside the Roundcube interface, check your data to see if it is present.
Notes
Contacts deleted in the Roundcube interface are not truly deleted you can restore contacts in the table by updating the ‘del’ attribute.