How to i import Squirrelmail address book to Plesk Horde?

[yongsan]

Hi,

Is it possible to import Squirrelmail address book into Plesk Horde address book?

I do not see any option of importing in Horde for Plesk Reloaded 7.5.3

Thanks

 

[jamesyeeoc]

First you will need to download and install an addon module to Squirrelmail to allow you to export the addresses from Squirrelmail to CSV:

http://www.squirrelmail.org/plugins_category.php?category_id=2

If you are not the server admin (root) on the Squirrelmail server, then you will have to ask the admin to do this.

Once you have exported them from Squirrelmail, then you can take that CSV file and import it into Horde/IMP

Horde/IMP supports importing the following types of data files:

Comma Separated Value (CSV)
Tab delimited
vCard
Mulberry Address Book (similar to LDIF?)
Pine Address Book

(And people like Squirrelmail better? At least IMP has the import/export built in....)

 

[yongsan]

Hi,

thanks. I got that sorted out.

 

[zpower]

Recently, I needed to import a lot of squirrelmail addressbooks to Horde after a mass migration from ensim to plesk.

I was looking for an easier way to import all those addresses that my users pains-takingly put into their squirrelmail addressbooks. As I had thousands of addresses for 100s of users, the above method was not of any use for me 'cause it would require me to go into each and every users mailbox in squirrelmail and then export to CSV and then... blah blah blah...

So, I had to come up with a faster and easier method. I wrote up a small perl script to generate mysql statements that can add up all the address book entries for all users.

I thought I would share the script hoping it might help someone. So, here it is:

<code>
#!/usr/bin/perl
#

$file=$ARGV[0];
$ownerid=$file;
$ownerid=~ s/\.abook//;
open (FILE,"<$file");
while(<FILE>) {
chomp($line = $_);
($oalias,$ofirstname,$olastname,$oemail,$onotes) = split('\x7c',$line);
$oname = $ofirstname . " " . $olastname;
$objectid=$ownerid . int(rand(5000));
print "insert into turba_objects (object_id,owner_id,object_alias,object_email,object_name,object_notes) values (MD5('$objectid'), '$ownerid', '$oalias', '$oemail', '$oname', '$onotes')\;\n";
}
close FILE;
</code>

You can also ge the script from http://zort.org/codes/perl/abook2turba.pl

You can just go into the squirrelmail-data directory of the ensim server (usually /home/virtual/<domain name>/var/www/squirrelmail-data) and run this script this way:

<shell>
# for abook in `ls *.abook`;do ./abook2turba.pl $abook;done;
</shell>

This will throw all the INSERT mysql statements on the screen. You can pipe the output to a file and just import it into horde database using mysqladmin or mysql command.

I wonder why Plesk didn't include this in their PMM. Or, maybe they have it planned.

 

[sgala]

here a modified version of this script. the original version fail when a contact have an accent in the name and if you import a large address book(the entropy of rand(5000) it's not enough).

Now it's ok!

Here:

#!/usr/bin/perl
use Time::HiRes qw(gettimeofday);

sub addslashes ($) {
  $string = shift;
  $string =~ s/([\\'])/\\\1/g;
  return $string;
}

$file=$ARGV[0];
$ownerid=$file;
$ownerid=~ s/\.abook//;
open (FILE,"<$file");
while(<FILE> ) {
  chomp($line = $_);
  ($oalias,$ofirstname,$olastname,$oemail,$onotes) = split('\x7c',$line);
  $oname = $ofirstname . " " . $olastname;
  ($epochseconds, $microseconds) = gettimeofday;
  $objectid = $ownerid . $microseconds;
  printf ("insert into turba_objects (object_id,owner_id,object_alias,object_email,object_name,object_notes) values (MD5('%s'), '%s', '%s', '%s', '%s', '%s')\;\n",
    addslashes($objectid),
    addslashes($ownerid),
    addslashes($oalias),
    addslashes($oemail),
    addslashes($oname),
    addslashes($onotes));
}
close FILE;