Roundcube Community Forum

News and Announcements => General Discussion => Topic started by: Predatorftp on March 03, 2010, 08:47:40 AM

Title: Tutorial on how to import address books to RC MySqL database
Post by: Predatorftp on March 03, 2010, 08:47:40 AM
I was looking for a solution for this issue a long time and finally i have managed to find my self a solution and decided to share it with all (please put it as sticky) those newbie's (like me)

Subject: Solution to import Contacts from your outlook \ Gmail or any CSV capable client.

The bad news is: there isn't an easy way to import address books, no one didn't write a plug-in for it yet.

Here is a step by step guide, I don't take any responsibility for the following procedures, proceed with care. Any comments are most welcomed.

0. Creating the .CSV (Comma separated values) file:
You can export your address book by from your favorite email client, using the software interface (Eg. Googles Gmail or MS Outlook). Make sure you choose to export the address book to a "Comma Separated Values" file. A "Comma Separated Values" file is a text file containing fields (aka values) separated by commas, Each field in is delimitated by a comma from both sides (except for the first and the last in the row). The fields are positioned in a specific way which is determined by the first row. For example if the first row looks like this: "first name, last name, email, phone" the data on the second line will look like this: "Mike, Silver,, 32392013993". In the same order as it shows on the first line.
B.CSV format
Programs like MS Outlook lets you choose the fields to export into the CSV file, don't export all the default fields of MS Outlook address book because Roundcube only uses the following fields (if you didn't manually added any other fields): "name email firstname and surname"
C. preparing the CSV file for Import
After the creation of the CSV file, you will have to edit the file using Excel or notepad and remove the first row (the title row) this row contain the titles of each field, The import engine doesn’t recognizes this line as a "title only" row and will import it to the Address book database with all your other contacts.
Make sure you clean\remove all the inverted commas and commas from the values inside the fields, you don't want to confuse the import engine. For example a comma inside a field will confuse the import engine to think that the field is ended and another field started, this can cause the import engine to think that there are more fields than intended Example of incorrect field: "mike , siverman" will be interpreted as 2 fields not one.
D. Find your User ID
Find out your user_ID number by entering the users table on your Roundcube database and locate yourself in it - the number in the User_ID field is yours.
E. Allocate the imported information to a specific user in the round cube database.
While you edit your CSV file, you need to add a column to the end of the table with your user id. Do this for all the rows in the CSV file (just drag it in excel or copy paste). You can attribute the contacts you are importing to any existing user in the database or even to the global address book by adding user_id number to that field.
F. Save the CSV file.
Save the file, and don't forget to save it as "Comma separated values file" format.
Place the file where you can find it later on.
1 Phpadmin Access
Start by gaining access to your PHPadmin (this is where you manage the MySql databases and tables).
2 Contacts table
Now you need to get in the contacts table. Do that by  clicking on the roundcube database a list showing all the existing tables in the database will be opened on the left side of the screen - click on the contacts table.
3 Import setting and action
After you are in the contacts table press the import tab (on the top selection row) Press the browse button and select your .CSV file previously created. Set  "Character set of the file" to UTF8, in "Set the Format of imported file" form, you will need to set the following fields like this:
Fields terminated by "," (comma)
Fields enclosed by "" (Empty)
Fields escaped by "" (Empty)
Lines terminated by AUTO
Column names -  This information field tells the import engine how to treat each field in the CSV file. So if for example you put – "name, email, firstname, lastname" the import engine will import the first field in the CSV file to the "name" field of the contacts table and the second field will be imported to the "email" field of the contacts database and so on. With my database and CSV file this is what I have: "name, email, firstname, lastname, user_id". Don't forget to import the user_id from the CSV file.
Press GO and you are done. Check your address book in roundcube all your imported contacts will be there.
Hope this helps.