Import: Update by Email Address

When importing in SugarCRM you can update existing records only if you include the unique ID along with each row that is being imported. This can be very impractical as it requires folks to dedicate hours to days of time to scrub lead files if they want their data to be updated for leads that already exist in their system. This is especially true in the case of periodically getting a fresh new version of leads from the same source. What if we alter the import process so that we can grab the ID on the fly by using an email address from the file?

In this example we will be updating the Title field on the Contacts module. Here are the two records that we will be updating. In our import file there is not an ID column. However, you could have ID column and still use this method to fill in the ID for any that are blank based on the email address.

First, the code. The actual import gets kicked off on Step 4. It is at this point where we will need to tell the import to use our custom logic to populate the ID field if blank based on an email address. So we make a custom view.step4.php and extend the original ImportViewStep4 class. In this case, we need to implement everything exactly the same as in the original display() function except for the Importer class instantiation. To clarify, you NEED TO copy the original display() function from the core view.step4.php from the Import module. It may change from version to version so always do this step.

Notice the this block near the bottom:

if($this->bean->module_dir == 'Contacts') { 
    require_once('custom/modules/Import/ContactsImporter.php');
    $importer = new ContactsImporter($importSource, $this->bean);  
} else {
    //Original code from /modules/Import/views/view.step4.php
    $importer = new Importer($importSource, $this->bean);
}

This tells the import process to use our ContactsImporter when we are importing Contacts.

Next up, we implement our logic in ContactsImporter. Much like with view.step4.php we will extend the original Importer class copy the import() function to start. You NEED TO copy this from your modules/Import/Importer.php as it may change from version to version.

Take special note of anything marked with “eggsurplus”. In this file we go through all the columns imported and look for an ID column and an Email1 column. These are related to an integer key so those get stashed for when they are needed on the next step which is to iterate over each row and check to see if the ID is missing and if an email address column exists. If so, our custom existingEmailAddressCheck function is called which looks in the database for a contact with that email address and returns the corresponding ID.

With this code in place we can now do an actual import. It is important that on the first step of the Import that we choose “Create new records and update existing records”.

What if you don’t have an ID column in your file? On Step 3 click on “Add Field” at the bottom and add the ID column.

Next, drag over Email Address to “Fields to Check” on Step 4 “Check for Possible Duplicates”.

Continue on and kick off the import. When finished all of the contacts with a matching email address will be updated.

This same process can be applied to any other module and potentially any other field or combination of fields. You could even look for matches across different modules. For example, avoid importing a lead if an account with the same phone number already exists.

Happy hacking!

EDIT: Adam Jakab has done some nice work to generalize this so that the Importer takes advantage of any indices for a module. Check it out: http://dev.alfazeta.com/blog/item/41-sugarcrm-import-update-records-by-matching-user-supplied-indices-without-supplying-id-in-csv

about

I make high quality solutions at an alarming rate.

10 Responses to “Import: Update by Email Address”

  1. Matthew July 18, 2013 at 8:34 am # Reply

    Nice. Thanks Jason. People do ask about this a lot, nice to know it’s been done.

  2. Matthew Poer September 6, 2013 at 10:27 am # Reply

    Circling back to this today to implement it, just wanted to vouch again because it works and does exactly what is claimed.

  3. Vincent October 2, 2013 at 2:45 pm # Reply

    Do you know what version of sugarcrm this was? CE? 6.5.11? Etc. Thanks

  4. Adam Jakab May 8, 2014 at 5:03 pm # Reply

    Hey Jason! Very nice! I took your work and I gave i a little spin! I have added dynamic CustomImporter loading in step4 so that now you can just drop in a class for any of the importable modules and it will go. Then in the CustomImporter I took a different approach from yours:
    1) i have taken out the necessity to manually add the empty id column (gets added by code automatically if not there)
    2) i use the user selected indexes to find and inject ids in the $row so they become updatable
    3) left an entry point where you can still use custom logic to modify/insert $row values
    4) added logging capabilities so that in view.last you can check on what happened during import.
    Thanks for your work – it got me started good. If you care drop me a line and i’ll send you the code – i will probably post it on my blog some time – but it can take years before i get around to do that.

    • jason May 8, 2014 at 7:51 pm # Reply

      Ha! I know how that goes. When you do post it definitely give a shout out back here and I’ll make sure to link to it in this post. Glad to see this got you kick started and you were able to greatly improve on it.

      • Adam Jakab May 10, 2014 at 7:31 am # Reply

        I have’ve done it! Not for my glory but to help comunity. http://dev.alfazeta.com/blog/item/41-sugarcrm-import-update-records-by-matching-user-supplied-indices-without-supplying-id-in-csv
        Thanks J\
        A\

        • jason May 12, 2014 at 10:37 am # Reply

          That’s great! I’ve updated the article to link to your post at the end. Thanks for sharing that. I’m sure it’ll help folks out there.

          -Jason

        • Martin August 21, 2014 at 10:30 pm # Reply

          Hey Jason/Adam, great idea/code, I just can’t get it to work :=]. Updated the display methods as outlined above (SugarPRO 6.5.16). It does the import but not the update, no errors, displays the CustomImporter Logs (below), but just finds dupes and that’s all. Any thoughts on where the prob could be?

          Am implementing Jasons code…

          Initialising CustomImporter: CustomImporter

          Record updates enabled: Y
          Available Columns: ["first_name","last_name","salutation","title","email1","preferred_name_c","id"]
          DupeCheck indices: ["special_idx_email1::email1"]
          DupeCheck fields: ["email1"]
          Initialising Importer: ContactsCustomImporter

          ———- Starting Import ———-

          Thanks for your time and patience

          • jason August 21, 2014 at 10:55 pm #

            Hello Martin,

            I recommend adding logging throughout to see if it’s getting to the existingEmailAddressCheck function. Once you pinpoint where logging is not happening that tackle that issue.

            For example:
            $GLOBALS['log']->fatal(“In existingEmailAddressCheck “);

            Then check sugarcrm.log afterwards.

Trackbacks/Pingbacks

  1. Le blog Webnet – blog.webnet.fr | Comment importer et mettre à jour des données dans SugarCRM lorsque l’identifiant n’est pas disponible ? - August 20, 2013

    [...] existe un article sur le sujet (http://www.eggsurplus.com/home/content/import-update-by-email-address/ ), mais le processus est très compliqué et surtout il ne fonctionne qu’avec le module [...]

Leave a Reply