ClearOS Documentation

×

Warning

301 error for file:https://clearos.com/dokuwiki2/lib/exe/css.php?t=dokuwiki&tseed=82873f9c9a1f5784b951644363f20ef8

User Tools

Site Tools


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','testing@example.com','testing','Guy',"BEGIN:VCARD
VERSION:3.0
N:Guy;Example;;;
FN:Example Guy
EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com
EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com
EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com
EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com
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
    • ‘testing@example.com’: 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:guy@example.com
EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com
EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com
EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com
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','testing@example.com','testing','Guy',"BEGIN:VCARD
VERSION:3.0
N:Guy;Example;;;
FN:Example Guy
EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com
EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com
EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com
EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com
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','testing1@example.com','testing1','Guy',"BEGIN:VCARD
VERSION:3.0
N:Guy;Example;;;
FN:Example1 Guy
EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com
EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com
EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com
EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com
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','testing2@example.com','testing2','Guy',"BEGIN:VCARD
VERSION:3.0
N:Guy;Example;;;
FN:Example Guy
EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com
EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com
EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com
EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com
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','testing3@example.com','testing3','Guy',"BEGIN:VCARD
VERSION:3.0
N:Guy;Example;;;
FN:Example Guy
EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com
EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com
EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com
EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com
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','testing4@example.com','testing4','Guy',"BEGIN:VCARD
VERSION:3.0
N:Guy;Example;;;
FN:Example Guy
EMAIL;TYPE=INTERNET;TYPE=HOME:guy@example.com
EMAIL;TYPE=INTERNET;TYPE=HOME:guys2ndhome@example.com
EMAIL;TYPE=INTERNET;TYPE=WORK:workaddress@example.com
EMAIL;TYPE=INTERNET;TYPE=OTHER:otheraddress@example.com
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

You need to use the mysql tools in the sandbox in order to properly access the System MySQL/MariaDB database.

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.

content/en_us/kb_import_address_books_into_roundcube.txt · Last modified: 2015/11/20 14:23 by dloper

https://clearos.com/dokuwiki2/lib/exe/indexer.php?id=content%3Aen_us%3Akb_import_address_books_into_roundcube&1558923226