Linking Contacts to Accounts During Import

[andybesy]

Hello Everyone,

What is the best way to go about importing accounts and contacts (from CSV) whilst getting the contacts linked to the accounts?

On my first attempt I initially imported all our accounts, which worked very well. But when I imported our contacts I found duplicate accounts were created instead of the contacts becoming linked to the existing accounts as I expected.

I later tried importing contacts first, allowing the system to create accounts as it goes. But with this method I am only able to map a few fields relating to the account, which leaves my accounts missing a lot of data.

Could anyone recommend a current "best practice" for importing accounts and contacts?

I am faimilar with working with CSV, and can also develop for PHP and MySQL, so I don't mind getting a bit technical if anyone wants to suggest a custom procedure that worked for them.

Many thanks in advance for your advice,

Andrew

Operating system type and version: Debian Linux (Sarge)
Sugar Suite version: SugarCRM Open Source 4.2.0d
Webserver type and version: Apache 2
PHP version: PHP 4.3 (libapache-mod-php4 4:4.3.10-16)
MySQL server version: mysql 4.1 (4.1.11a-4sarge4)

[audiopleb]

Hmm, interesting.

The import has always worked like a treat for me to be honest. Accounts first and then contacts second.

When we imported our data from Act! into sugar we simply looked at the database structure of the few tables we were importing into and matched our data to that creating our own ids to link info across them. We then imported from the Ms acess Db we had straight into the mysql database using the mysql tool "Navicat - £50)

Worked like a dream.

[andybesy]

Hello audiopleb and thanks for your response.

I have been looking at the "accounts", "contacts" and "accounts_contacts" tables, and I understand the relationships defined within those tables.

With regards to IDs, did you have to make any special provision to make sure the IDs you generated were compatible with Sugar? Or was it simply a matter of using any unique ID you like and making them match across the tables?

Thanks for your help, Andrew.

[andybesy]

Hello,

The is the OP posting details of the solution I arrived at.

1) I modified the database query I was using to export Accounts and Contacts so that the ACCOUNT ID from our previous database was included in the CSV files for both Accounts and Contacts.

2) I then imported accounts using the integrated Sugar import tool, this time mapping the ACCOUNT ID from the previous system to the Sugar's own ACCOUNT ID field.

3) I then imported contacts using the integrated Sugar import tool, again mapping the ACCOUNT ID from the previous system to the Sugar ACCOUNT ID field.

In this way I have been able to import all our accounts and contacts, with all details present and with the contacts properly linked to their respective accounts.

This appears to be working fine, but I would be grateful if anyone can confirm or deny that Sugar will be OK using the ACCOUNT IDs from the previous system? They are formatted rather differently than the ACCOUNT IDs Sugar itself generates.

Andrew

[juanwang]

If no-one else has come across this problem, I would be very surprised!

This is my problem scenario. I have a national customer with offices in different states. Each branch is set up as different Account and assigned to a different Account Manager.

At first, I differentiated each Account by concatenating the Account Name with the State (eg Account - State) and I also used the Account Name as the ID during import. This way, I knew what the ID was and could import the Contacts and attached it to the correct Account by specifying the Account ID.

The issue with doing it this way was that it wasn't consistent when an Account was added manually because the system generated the ID and it seem to me like too much manual work concatenating. Besides it looked ugly in the Account list view because a field already exists called City which shows the state the Account belongs in.

So I removed the concatenated states and allowed SugarCRM to determine the ID during import. Now my problem is I have identicle Account Name differentiated by the City (State) field but I can no longer import Contacts because 1. I no longer know the ID and 2. The Account Name are duplicated.

How are other people getting around this predicament?

Cheers,

Juan

[andybesy]

Hi Juan,

The issue with doing it this way was that it wasn't consistent when an Account was added manually because the system generated the ID and it seem to me like too much manual work concatenating.

I can't confirm that deviating from the internally generated Sugar ACCOUNT IDs is OK, but everything I have read about importing accounts and contacts this week suggests that using your own IDs is the right way to go if you want to get contacts linked to accounts automatically. This is what I have done, and I haven't seem any problems occuring as the result so far.

So I removed the concatenated states and allowed SugarCRM to determine the ID during import. Now my problem is I have identicle Account Name differentiated by the City (State) field but I can no longer import Contacts because 1. I no longer know the ID and 2. The Account Name are duplicated.

The ACCOUNT ID is just an internal field that doesn't get displayed. If it's too much work to type the account name concactinated with the state, then how about just using the ID from your existing database, or just an incremental interger like 1,2,3,4 etc?

If you prefer to continue to use Sugar's internal IDs, then you need to import your accounts, then view the accounts table in the database (using PHPMyAdmin or the like) and copy out the IDs it has created in to the file you will use to import your contacts. Then you can map those IDs to ACCOUNT ID when importing the contacts.

I have not tried to create accounts with identical ACCOUNT NAMEs, but if it lets you do this and you need to be able to differentiate the accounts based on another field, then you can use the studio to add the STATE field in to the accounts listing.

Andy

[juanwang]

I decided to concatenate the State to the Account Name and also define my own ID. When I imported the Contacts, I tried associating it with the Account ID and received the following error.

Query Failed:INSERT into accounts set id='Australian32Industrial32Registry32-32VIC', date_entered='2006-07-06 04:51:03', date_modified='2006-07-06 04:51:03', modified_user_id='1', assigned_user_id='326629b2-7902-d520-0524-44978a004afe', created_by='1', deleted='0'::MySQL error 1062: Duplicate entry 'Australian32Industrial32Registry32-3' for key 1

I saw someone else ask this question with no answer. I'm runing version 4.2.0d

Cheers,

Juan

[Markku]

Seems like you're importing into accounts ("INSERT into accounts") and that you already have record with the same ID in the database ("Duplicate entry 'Australian32Industrial32Registry32-3'"). Try to remove duplicates from the import file or from the accounts table.

[andybesy]

Hi Juan,

MySQL is complaining that it cannot insert a record because of a duplicate ID.

The database field ID is set as a primary key and so must be unique. Either you have two records in your import data with the same key, or there is an existing record with a key matching one in your import data,

Note that when you delete account records in sugar they don't get deleted from the database but marked as deleted.

It can be usefult o use a tool like PHPMyAdmin to allow you to look in to the database directly in cases like this.

HTH, Andy

[juanwang]

Hi Guys,

Your both right, but it's a bug in the system. The import I was performing was a Contact whereby I associated to the Account using the Account ID. Sugar is trying to add the Account during the Contact import. I have cleared all the data out of the Accounts, Accounts_Contacts and Contacts tables in MySQL and started from scratch with my import and the same error occured.

I then mapped the Account Name instead of the Account ID in the Contact import and it all worked fine.

Does anyone know where I edit the insert SQL statement so that the Account ID = Account Name instead of an auto generated one?

Cheers,

Juan