Here is a procedure how to export the saved address book contacts from Roundcube webmail directly from the MySQL database to a CSV file (please note that Roundcube natively support exporting contacts from the Webmail itself to CSV – this method is only in case you want to pull out old contacts from a database).
1. Using the shell, access to the Roundcube database with the MySQL client :
|
1 |
mysql -u dbuser -p roundcubedb |
2. Issue the select command below with the exact syntax, only modify the section ‘user@domain.tld’ with the actual user email address from where you want to export the contacts from :
|
1 |
select user_id from users where username='user@domain.tld'; |
Output example :
|
1 2 3 4 5 6 7 |
mysql> select user_id from users where username='user@domain.tld'; +---------+ | user_id | +---------+ | 10 | +---------+ 1 row in set (0.00 sec) |
3. Optional : To view the existing contacts in this user address book, issue the following command :
|
1 |
select name,email from contacts where user_id=10; |
Output example :
|
1 2 3 4 5 6 7 8 9 |
mysql> select name,email from contacts where user_id=10; +-------------------------------------------+--------------------------------------------------------------------+ | name | email | +-------------------------------------------+--------------------------------------------------------------------+ | John Doe | john.doe@domain.tld | | Jane Doe | jane.doe@domain.tld | | Whatever Doe | whatever.doe@domain.tld | +-------------------------------------------+--------------------------------------------------------------------+ 3 rows in set (0.00 sec) |
4. Quit the MySQL client :
|
1 |
exit |
5. Create the CSV file scheme using the following command :
|
1 |
echo "First Name,Last Name,Display Name,Nickname,Primary Email,Secondary Email,Screen Name,Work Phone,Home Phone,Fax Number,Pager Number,Mobile Number,Home Address,Home Address 2,Home City,Home State,Home ZipCode,Home Country,Work Address,Work Address 2,Work City,Work State,Work ZipCode,Work Country,Job Title,Department,Organization,Web Page 1,Web Page 2,Birth Year,Birth Month,Birth Day,Custom 1,Custom 2,Custom 3,Custom 4,Notes," > user.domain.tld.contacts.csv |
6. Issue this command to export the contacts to our previously defined CSV file (replace as needed the name of the CSV file output) :
|
1 |
mysql -u dbuser -p roundcubedb -qbse "select name,email from contacts where user_id=10;" |sed 's/\t/,/g' | sed 's/^/,,/g' >> user.domain.tld.contacts.csv |