Below shell script will convert roundcube address book to csv. I am using postgresql for roundcube database. ouput csv file can found in /tmp folder
#!/bin/bash
# Roundcube Address book must be stored in PostgreSQL database.
# This script is created by Praveen C. please feel free to contact me on praveen.velu84@yahoo.com-
# for any query. You can modify this script to suit your requirement
# Enter roundcube user_name. Script will query this user in DB and export Address book
# Enter roundcube user_name. Script will query this user in DB and export Address book
USER="user_name"
# Export your roundcube database passwor. This password will be used for `psql` command
# This will help us to avoid psql prompt for password
export PGPASSWORD=password
# Find the `user_id` of the user
# Find the `user_id` of the user
echo "COPY(SELECT user_id from users where username='$USER') \
TO STDOUT" | psql -h localhost -U roundcube \
TO STDOUT" | psql -h localhost -U roundcube \
-o /tmp/rc_query_id roundcube
# Read user_id stored in file rc_query_id file and store to variable _USER_ID
user_list=`cat /tmp/rc_query_id`
for id in $user_list; do
for id in $user_list; do
_USER_ID=$id
done
done
# Query user contacts in and sent output to csv.
echo "COPY (SELECT name,email,firstname,surname from contacts where user_id=$_USER_ID) \
TO STDOUT with CSV HEADER" | psql -h localhost -U roundcube \
-o /tmp/$USER.csv roundcube
TO STDOUT with CSV HEADER" | psql -h localhost -U roundcube \
-o /tmp/$USER.csv roundcube
Hi,
ReplyDeleteI need this, can you have this for MySQL?
Thanks in advance!
Hi Eybisidee,
ReplyDeleteplease find below script for Mysql databases....
##############
#!/bin/bash
# This script used for Mysql database.
# This script is created by Praveen C. please feel free to contact me on praveen.velu84@yahoo.com-
# for any query. You can modify this script to suit your requirement
# Enter roundcube user_name. Script will query this user in DB and export Address book
USER="user_name"
dbUser="roundcube"
dbPassword="your password"
userIdQuery="SELECT user_id from users where username='$USER'"
idOutputFile=/tmp/rc_query_id
csvOutputFile=/tmp/$USER.csv
# Find the `user_id` of the user
mysql --skip-column-names --user=$dbUser -p$dbPassword roundcube --host=localhost -e "$userIdQuery" > $idOutputFile
# Read user_id stored in file rc_query_id file and store to variable _USER_ID
user_list=`cat /tmp/rc_query_id`
for id in $user_list; do
_USER_ID=$id
done
# Query user contacts in and sent output to csv.
userContactsQuery="SELECT name,email,firstname,surname from contacts where user_id=$_USER_ID"
mysql --user=$dbUser -p$dbPassword roundcube --host=localhost -e "$userContactsQuery" > $csvOutputFile
# By default mysql query output is tab seperated. use sed to replace TAB with semicolon
sed -i 's/\t/;/g' $csvOutputFile