Import accounts from CSV file.

[avagarwal]

Hi,

I am using SugarCRM 5.0. I need to import Accounts data from a CSV file, checking on the Account Name for existing / duplicate entries. If an Account Name is duplicate then the record should be updated instead of a new record being created. Is this possible using the existing Import Page, if so how?

Thanks in Advance

Anurag

[clint]

Yes, this is possible if you add a custom index on the name field. Note at the bottom of the field mapping page in the Import process there is a "Verify duplicate entries against selected indexes" select field. This is a list of indexes defined for the Accounts module. If the index you wanted existed, it would be in this list. You would select it and during the Import the system would check against the name field for duplicates.

To create the index on the Name field, do the following:

1) create this new file: custom\Extension\modules\Accounts\Ext\Vardefs\cust om.php
2) Add in this code to this file:

 <?php
 $dictionary['Account']['indices'][] = 
        array('name' =>'idx_name_del', 'type'=>'index', 'fields'=>array('name','deleted'));
 ?>  

3) Log in to Sugar as an adminsitrator, go to Admin->Repair->Quick Repair and Rebuild and execute the repair here twice. Note, you do need to run the repair twice so that the new index definition is registered and then actually execute.

You will now see an index called "Name, Deleted" that you can use to check duplicates against while importing.

[avagarwal]

Hi Clint,

Thank you for your reply. Creating and selecting the index, causes the duplicate records to be skipped. I however would like to update the records instead of skipping them.

On another note, can we modify the way in which the date format is displayed in php?

Thanks

Anurag

[clint]

Hi Anurag -

The Sugar 5.0 import tool doesn't do updates of existing records. However the update feature is in the 5.1 release that is in beta now and scheduled for general availability late next month.

In that case, updates won't key off of duplicates found by the duplicate search mechansism but rather by directly mapping the record ID field.

Also, you can change your date display in the My Account settings. However in the 5.0 import tool, your dates must be in the yyyy-mm-dd format for import. In the 5.1 import tool, we've changed that so that the dates of your import will need to match whatever date display format you set up in My Account.

[tatodjgdn]

hi,
how can I add a custom field as field index in import?

tanks

[eggsurplus]

Is there a way to add emails to the duplicate check?

I know this used to be here. Hopefully it wasn't dropped in 5.0 due to the email address move. It would be another change that has cost me days of valuable time from other non-Sugar systems that I need to work on. I may be a bit frustrated right now.

[eggsurplus]

Here's how to add your own code to force a duplicate check based on email during import. Use at your own discretion.
module\Import\ImportStep4.php:

Find this line of code:
$isUnique = checkForDupes($focus, $var_def_indexes, $row);

and add this code immediately after:

     /** [IC] check for email dup */
    if($isUnique && isset($focus->email1)) {
        $dupe_sql = "select email_addresses.id from email_addresses "
                   ."inner join email_addr_bean_rel on email_addresses.id = email_addr_bean_rel.email_address_id and email_addr_bean_rel.deleted = 0 "
                   ."inner join $focus->table_name on email_addr_bean_rel.bean_id = $focus->table_name.id "
                   ."where email_addresses.deleted = 0 and email_addr_bean_rel.bean_module = '$focus->module_dir' "
                   ." and email_address = '".trim($focus->email1)."'";
        $result_count = $focus->db->query($dupe_sql); 
        if($focus->db->fetchByAssoc($result_count)){
            $isUnique = false;
        }
    }
    /** end [IC] */ 
  

[saradhinc]

Can someone please suggest a way to use custom field to prevent duplicate entries??

Like in the Leads module I have a custom field for Project_id_c (in the table leads_cstm). How can include this field in the "Verify duplicate entries against selected indexes" select field?

Please help.

[clint]

There isn't a way to use custom fields yet.

[jsnkadlec]

Hurrah! I deleted my former saved import, tried again and also selected double quotes " as a qualifier for the CSV. Not sure if the qualifier made a difference, but the issue reported below is not an issue! Hurrah!

Moreover, I WAS able to use the SugarID as a match field and even updated an account name. ONWARD with the Migration.

I'm leaving the post below in case anyone else is looking on any of the terms I used below.

In essence- if you are having trouble using the Import function to Update records:
a. make sure you are on at least 5.1 a
b. If you created/saved a custom mapping, you may want to delete it and try again. If you match your column names on the CSV to the fields in Sugar, you won't need to make it custom, BTW
c. You will need to use the Sugar ID as a match field...so Import all your records, Export them all and then match to your existing DB. You can work around there not being the feature to use a custom field as a key ID in this way. Just make sure that you import your custom Key ID into Sugar and that you export it. When you merge to your non Sugar DB, match using your key_id and be sure to also import the Sugar ID. When you need to then integrate the other way, use the Sugar ID. I'm sure that's not going to be perfect, but if you're careful it should work fine - AFAIK.

Here was the original issue:

I am using Version 5.1.0a (Build 4837) currently hosted via a test account (eval.sugarondemand).

I would like to use the Import records to update matching records, and create new records where there is no match.

I am updating from a Filemaker Database that is exporting to CSV.

I was hoping to use the Sugar ID as a match field where when the ID matches, the record is updated an not skipped.

I understand the "Name" field is what is currently matched, but when I import even one test record, instead of matching the record (I have triple checked the spelling) - it actually adds a new record AND inserts a space within the text!

The record name on the CSV is "A.A. Bondy" The newly created record is "A.A. Bondy." Even more perplexing is that the extra space does not display in the view, but can only be found during Edit.

I must do weekly updates of records! Not being able to match on a field like an ID number is frustrating. Account names can be unstable- the account desides to be AA Bondy for example. I had always thought that using a key ID was the best practice method of integrating data.

When using the ID as an Indexed field on import, instead of matching, it skips the records? Is this because I am on a Eval license? Does this work properly in version 5.1 b?

Thank you for your help, I was hoping to have the migration complete over the holiday weekend so that the staff can actually test this out. Not being able to easily import and match data is a huge requirement for me and others, I am sure. I've searched for an hour and have not found a response that has enabled me to import.

Thank you for your time...who ever is working over the holiday...