Addressbook mass import

Having trouble installing Group-Office? No worries. Help is on the way.

Moderator: Developers

fischli
Posts: 36
Joined: Tue Apr 22, 2014 10:06 am

Addressbook mass import

Postby fischli » Thu May 08, 2014 7:24 am

I have to import a huge amount of contacts from a foreign system. I don't want to import a lot of companies into several addressbooks through csv by the gui. Are there any CLI tools for managing contacts and for mass-import?
webwolfi
Posts: 616
Joined: Sat May 27, 2006 7:54 am
Location: Vienna/Austria

Re: Addressbook mass import

Postby webwolfi » Thu May 08, 2014 8:04 am

I recently made an php script for importing contacts from another database

i asume you know how to request data from your existing database

to use the GO system you first have to load the GO connection

Code: Select all

require('GO.php'); 
create a new contact:

Code: Select all

$contact = new GO_Addressbook_Model_Contact();
select an addressbook:
(replace 1 with your addressbook-id)

Code: Select all

$contact->addressbook_id=1;
input some standard data, eg.

Code: Select all

$contact->first_name=$your_first_name_variable;
input some custom data

Code: Select all

$contact->customFieldsRecord->col_13=$another_variable;

save the contact

Code: Select all

$contact->save();
hope it helps
Wolfgang
fischli
Posts: 36
Joined: Tue Apr 22, 2014 10:06 am

Re: Addressbook mass import

Postby fischli » Thu May 08, 2014 8:30 am

That's the hard way to code an importer. I would like to have a tool to import CSV-data directly by shell-script. Anything around? Anyway, thank's for your proposal.
webwolfi
Posts: 616
Joined: Sat May 27, 2006 7:54 am
Location: Vienna/Austria

Re: Addressbook mass import

Postby webwolfi » Thu May 08, 2014 9:20 am

there is also an import-option implemented in GO

Address book -> Administration -> Address books -> [select addressbook via double click] -> Import

have a look into the doku:

https://www.group-office.com/wiki/Addre ... g_contacts
fischli
Posts: 36
Joined: Tue Apr 22, 2014 10:06 am

Re: Addressbook mass import

Postby fischli » Thu May 08, 2014 9:44 am

I already know this import feature inside the GUI, but as already mentioned I'd like to do it by commandline. Like a simple shell script piping CSV-data file by file into GO.
webwolfi
Posts: 616
Joined: Sat May 27, 2006 7:54 am
Location: Vienna/Austria

Re: Addressbook mass import

Postby webwolfi » Thu May 08, 2014 10:03 am

you need to get data into the database

my first thought was also a quick-and-dirty solution but Merijn adviced me to use the GO handlers to not corrupt the database.

so a small php script seems to be the smartest solution and can also be run from commandline if you wish
webwolfi
Posts: 616
Joined: Sat May 27, 2006 7:54 am
Location: Vienna/Austria

Re: Addressbook mass import

Postby webwolfi » Thu May 08, 2014 10:18 am

ists really easy.

how is your existing data structured?


edit: if you have a custom problem you need a custom solution, you will not get around of some lines of custom code.
fischli
Posts: 36
Joined: Tue Apr 22, 2014 10:06 am

Re: Addressbook mass import

Postby fischli » Thu May 08, 2014 10:46 am

I have to migrate from PostgreSQL. The source is (luckily) similar structured as in GO. For now I am exporting well formated CSV data by simple sql selects per addressbook. These exported CSV files are ready to import into each addressbook (by the GUI).

My current workflow: select and modify source data ... write to csv ... import by gui ... review in GO ... modify selection and restart another pass

This workflow will end in proper database relations (and endless optimization loops).
webwolfi
Posts: 616
Joined: Sat May 27, 2006 7:54 am
Location: Vienna/Austria

Re: Addressbook mass import

Postby webwolfi » Thu May 08, 2014 11:49 am

First step is to create a mysql matching table for addressbook-IDs for the old and the new database
if you do this by hand or create the addressbooks with a script depends on the number of addressbooks

same for a matching table for compny-IDs, but this should be automatically anyway

second step is to sync the companies

Code: Select all

<?php
//connect to old database
$connection1 = pg_connect("host=sheep port=5432 dbname=mary user=lamb password=foo");
//connect to database with id matching table
$connection2 = mysql_connect('yourhost:yourport', 'mysql_user', 'mysql_password');
mysql_select_db("matching_tables") or die(mysql_error());

 

//query companies from old database
$result1 = pg_query($connection1, "SELECT * FROM old_database_company_table");
while($row1 = pg_fetch_object($result1))
{
	//request addressbook_id
	$old_AB_ID=$row1->addressbook_id;
	$result2 = mysql_query("SELECT new_AB_ID FROM AB_IDs WHERE old_AB_ID=".$old_AB_ID.";") OR die(mysql_error());
	while($row2 = mysql_fetch_object($result2))
	{
	$new_AB_ID=$row2->new_AB_ID;
	}

//create new company
$company = new GO_Addressbook_Model_Company();

//select addressbook
$company->addressbook_id=$new_AB_ID;

//insert data
$company->name=$row1->name;
company->address=$row1->address;
//(repeat for all fields you want)

//store the old company ID in a field you dont need yet, eg vat_no
company->vat_no=$row1->id;

//save the company
$company->save(); 

//read out new company id

//find it again using the miss-used field
$company = GO_Addressbook_Model_Company::model()->findSingleByAttribute('vat_no',$row1->id); 

//get the new company id
$new_company_id=$company->id;

//write the real vat_no
company->vat_no=$row1->vat_no;

//save the company
$company->save(); 

//insert the company into matching tables
$old_company_id=$row1->id;
$query=("INSERT INTO COMPANY_IDs (old_company_id, new_company_id) VALUES($old_company_id, $new_company_id) ;");
$result = mysql_query($query) OR die(mysql_error());

}	
?>
third step would be quite similar for contacts but you also have to request the new company id from the matching table

on the other hand the third step is more easy because there is no need for another entery in a matching table (but if you wish you can)



edit: you can use the same system for creating addressbooks if you have a lot
fischli
Posts: 36
Joined: Tue Apr 22, 2014 10:06 am

Re: Addressbook mass import

Postby fischli » Tue May 13, 2014 1:02 pm

Thanks for your roundup.
You may follow the link and take a look at my current solution https://www.group-office.com/forum/view ... =8&t=10364
It's much easier now to migrate, instead of importing well defined CSV-addressbooks.

Who is online

Users browsing this forum: No registered users and 3 guests