Export Roundcube Contacts to CSV from Database

In this installment I’ll show you how to export roundcube contacts to a CSV file straight from the Roundcube database. Perhaps a customer has moved to a different server, and no longer has access to their roundcube mail. You can restore the Roundcube database, and get the contacts manually.

First, you have to gain access to the MySQL database. I’ll leave that for another time. But lets assume you have it, and you want to export roundcube contacts to a CSV file so that you can import it into another Roundcube installation, or even many other compatible programs.

The first thing you have to do is create a header for the CSV file. CSV means Comma Separated Values. It’s just a bunch of stuff with comma’s in the between the values. That’s it. The first line in a CSV file is the header that contains the names of the columns presented in the following lines. We’re going to use Mozilla Thunderbird’s format for compatibility with Roundcube and other programs.

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," > email.address.contacts.csv

Copy and paste that line, and then you have the header. Now we need to get the content. This is the fun part!


Open up MySQL and select the roundcube database:

root@tidbits [~]# mysql roundcube
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1068
Server version: 5.5.39-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [roundcube]>

Now we’ll find the user_id of the email address we want to get the the contacts for.

MariaDB [roundcube]> select user_id from users where username='user@domain.com';
+---------+
| user_id |
+---------+
| 59 |
+---------+
1 row in set (0.00 sec)

There it is, ID 59. So now, we’ll export the contacts. Make sure to copy the line exactly, because the sed statements are very important:

mysql roundcube -qbse "select name,email from contacts where user_id=59;" |sed  's/\t/,/g' | sed 's/^/,,/g' >>  email.address.contacts.csv

Now, it’s complete. You can examine the results:

less  email.address.contacts.csv

Because you’ve added the correct headers to export roundcube contacts from the database, and placed the data correctly, you can import the CSV file into any mail program that accepts Thunderbird exports, including Roundcube itself. Enjoy!