Exercise: Upsert Using External ID

Exercises are a set of instructions to follow in your practice org (see Getting Started – Free if you do not have a practice org).  The goal is to quickly introduce how to complete tasks or perform configuration.

In this exercise, we are going to load data into Salesforce using the Data Loader.  We’ll be migrating account and contact data from Sage CRM into Salesforce, and we’ll want to ensure that the relationships are maintained.

To do this, we will insert the account records, then upsert the contact records to the associated account using the account’s Sage ID (external ID).

Exercise Steps

To view the remainder of this content, you must purchase the Salesforce.com Certified Administrator Study Guide.  Please Login or purchase the study guide.

64 Responses to “Exercise: Upsert Using External ID”

  1. Bettina August 2, 2017 at 9:09 pm #

    Hi John,
    When I auto-map the contact fields, I get a column header “NAME” that has no matching field name. What should this be mapped to? I already have FIRSTNAME and LASTNAME mapped. When trying to load the data this way, I get 19 errors… Are they related to the unmapped NAME column header?
    Thank you!

    • Bettina August 3, 2017 at 8:35 am #

      …I found the error, I had defined required custom fields that were not in the CSV.

  2. philk July 27, 2017 at 4:23 pm #

    Hi John,

    I have dataloader already installed while I was using a previous training environment. I’m having trouble logging in with dataloader. I changed the url to https://login.salesforce.com. However I can’t generate the security token in my dev org because I don’t have those menus. I searched for keywords like token, or personal, etc, but nothing comes up. Has the names of these menu’s changed?


    • JohnCoppedge July 27, 2017 at 4:48 pm #

      If you’re using trusted networks or login ip ranges then you won’t have the security token option…

      If you are using a later version of the data loader you can use oath which doesnt require a token as well

  3. mengqinas June 14, 2017 at 3:20 pm #

    Do a lab and find that chatter feed tracking does not fire when I import data with Data Loader. Then I manually update the contact by Data Loader, feed tracking shows up under my chatter. Forget the original place for this post, just to share with you.

    Thanks for your great website!

  4. rtrailhead11 June 1, 2017 at 11:34 pm #

    Hi John,

    Was able to complete this exercise without having any trouble. However, I was hoping to get a better understanding of the what we were doing here. A use case or more detials on how the relationships are maintained?

    • JohnCoppedge June 14, 2017 at 12:25 pm #

      You’re uploading data using a relationship based on IDs from another system.

      Without using an external ID, you wouldn’t be able to connect the contacts to accounts.

  5. sahana March 27, 2017 at 1:23 am #

    how to choose csv files. when i browse i dont find any

    • ag3991 June 13, 2017 at 2:31 pm #


      The .csv files are probably saved in the “Downloads” folder.



      p.s. “Do not double-post” – John C

  6. sahana March 27, 2017 at 1:17 am #

    how to choose csv files. when i browse i dont see any csv file..

  7. piyushsharma09 January 12, 2017 at 6:34 pm #

    I cannot login to dataloader. I tried with or without appending security token. I have reset my security token recently. why i cant login with my SF credentials…where am i doing wrong..?

    • JohnCoppedge January 19, 2017 at 2:37 am #

      If you are using an OAUTH connection which is generally the case for the new dataloader, you should not need the security token.

      If you are logging in to a developer org, make sure you are using https://login.salesforce.com

  8. sachin.qatester September 15, 2016 at 6:51 pm #


    I am logged in as Salesforce admin. But when I Navigate to Setup –> Data Management, I can’t see any option for Data Loader. Its not there for me. I created my dev account exactly 15 days ago.

    I have another dev account I created 3 months ago, I could see Data Loader option in that account.

    What is that I am missing in the account I created 15 days ago?

    • JohnCoppedge September 28, 2016 at 9:30 pm #

      Do you have the new setup interface enabled? And are you logged in as a system administrator?

  9. LarryVan August 30, 2016 at 10:29 pm #

    Hi, I successfully uploaded 16 of the 19 contacts. The other 3 gave an error message of “Mailing zipcode does not match shipping zipcode in account”. When I checked one of the accounts it had no shipping address. I want to know if that is the problem during the upsert?

    • JohnCoppedge September 5, 2016 at 7:44 pm #

      Any chance you have a validation rule set up on the contact that requires a matching zip code?

  10. lairdo April 6, 2016 at 3:51 pm #

    It worked! I figured it out!

  11. lairdo April 6, 2016 at 3:37 pm #

    Hi John, I am new here. Love your site, very helpful. So when I get to the ‘finish’ stage it promps to save errors and successes in a directory, otherwise I can’t continue to the ‘finish’ button. When i save to a desktop folder it says there are ’13 errors’

  12. lairdo April 3, 2016 at 5:05 pm #

    Hi all,
    On a macbook and set system preferences to ‘download from anywhere’ and I am still getting 0 successes and 13 errors on the account import. Thoughts?
    Many thanks for your help. Seems pretty straight forward but it is not working.

  13. jhoosemans March 17, 2016 at 3:23 pm #

    At this moment you do not need your security token any more (at least on Mac). You can now login with help of OAuth.

    • JohnCoppedge April 6, 2016 at 8:51 pm #

      Thanks for the heads up – I’m seeing the same on PC. Haven’t updated DL in a while… will update materials.

  14. lrmanfre March 15, 2016 at 9:03 pm #

    I am getting the same error as nerd.sfdc. Also, the same error for the Contacts csv file. The error message for both files, for 10/13 records in each file, is “Duplicate Alert.” The Sage Account ID column in the contacts file has duplicate ids, so that’s the problem there.
    But I am not sure why the error comes up for the accounts file. I have no validation rules, no triggers, and account name is populated/discrete for each row…?

    • JohnCoppedge March 16, 2016 at 12:04 am #

      Got it. It looks like new developer edition orgs come with the duplicate rules enabled for the account object. It detects a duplicate for most of these imported record.

      You can go to Setup –> Data.com –> Duplicate Management –> Duplicate Rules, and then deactivate any rules on the account.

      Will update instructions, thanks.

  15. nerd.sfdc March 8, 2016 at 2:32 am #

    Account CSV is not getting imported only 3 records worked. What could be the reason.

    • JohnCoppedge March 10, 2016 at 7:28 pm #

      Do you get an error? Generally imports fail because a) the data is not populated properly (e.g. text in a numeric field), b) validation rule is turned on, or c) required data is not entered (like account name).

  16. Louise Witkowska October 27, 2015 at 10:11 pm #

    Users of Data Loader for Mac will need to change security settings for the app to work. You can do it in System Preferences > Security & Privacy > Padlock > Allow apps downloaded from anywhere.

    • JohnCoppedge November 9, 2015 at 4:10 pm #

      Windows users will also need to allow the app to be installed when prompted – good note. Sometimes this will require IT involvement if you are using a company provided computer.

  17. racerxx1969@hotmail.com October 20, 2015 at 9:06 pm #

    Step 2. Should be select Number field type. Then proceed.

  18. dsmith1308 June 8, 2015 at 4:18 pm #

    Do you know of any alternative ways to reset the security token with the new training orgs? I followed both suggested paths and the option is no longer available in the training org.

    • JohnCoppedge June 10, 2015 at 11:57 pm #

      If you do not have the new setup UI disabled (which is suggested in the getting started section), you will need to navigate the personal setup (click your name, then My Settings). This may vary org to org depending on a few factors (UI features enabled, communities enabled, etc.).

  19. Rena Bennett-Dellwo April 8, 2015 at 6:48 pm #

    In Step 6, the following doesn’t seem to be a complete sentence: “Note: you cannot use the “Insert” operation in conjunction with external IDs; however by matching on contact ID (and at the same time not providing a contact ID), will have the same affect as inserting the data.”
    I noticed this because I was trying to understand why Insert doesn’t accept external IDs, but upsert does. Is the answer to this “Because that’s the way it works”? 🙂

    • JohnCoppedge April 14, 2015 at 2:04 am #

      Yes 🙂 That is the short answer. I will fix the sentence, thanks for the heads up.

  20. Flavia Mich February 7, 2015 at 5:56 am #


    My Developer Edition came with Sage ID on the Account object, but when trying to map the fields in Data Loader the Sage ID is not available for mapping. Any ideas on what I might be doing wrong?

    • JohnCoppedge February 24, 2015 at 8:11 pm #

      Did you create the field after you logged in to the Data Loader? If so you will need to close the data loader (or logout) and log back in.

  21. Alex Messinger December 18, 2014 at 4:07 pm #

    Is the reason that you can’t insert when matching on an external ID because not all records necessarily have one, therefore there’s not sure way to determine match?

    • JohnCoppedge December 18, 2014 at 5:10 pm #

      I’m not sure why you can’t reference an external ID with any other operation other than upsert – there is probably a technical reason

  22. Alex Messinger December 5, 2014 at 1:29 am #

    Note that the data loader doesn’t work with windows 8. https://success.salesforce.com/ideaView?id=08730000000kNzpAAE

  23. Lisa Seyler October 12, 2014 at 2:37 am #

    After mapping the fields, I click next and I am prompted to “Select the directory where your success and error files will be saved.”. You can only click “Finish after this step

    • JohnCoppedge October 19, 2014 at 2:25 am #

      I added a note to address this – yes if this is your first time running the data loader you will need to set a directory for the success/failure files.

      • Aaron Howerton November 14, 2014 at 10:30 pm #

        Hey John – do you think it would be beneficial to move this note up in the instructions? I’m not an idiot and figured out what it was asking when it popped up, but my first thought was that I did something wrong following the steps. Moving this note up would alleviate that concern and bring this short little section in line with the generous detail and accuracy you’ve provided throughout the entire site. Taking my admin test next Monday – your site has been a fantastic resource to prepare. Thanks for the hard work.

        • JohnCoppedge November 16, 2014 at 9:12 pm #

          Yes, that makes total sense. Moved the instruction up. Good luck tomorrow Aaron!

  24. Berglind Heath-Smith September 10, 2014 at 11:23 pm #

    Try as I might, I’m not seeing the path: Setup –> My Personal Information –> Reset My Security Token at all, or anything in setup that references the security token. I don’t think this is just me as I seem to remember seeing this in another org I used recently… is it gremlins?

    • Charles Cummings September 13, 2014 at 12:49 am #

      Sounds like you have the new setup. Click on your name, then on my settings, then on Personal and you’ll see it!

      • JohnCoppedge September 21, 2014 at 6:08 pm #

        Quite likely the case – there is a reference in the getting started section to turn this off… some of the instructions will vary otherwise.

  25. Tasnim Tailor May 20, 2014 at 7:17 pm #

    When I tried to upsert the contacts, it inserted 3 and gave errors for the rest (Error – Email already exists). This is because I had a validation for duplicate email.

    What I did is updated the email of the error records and tried to upsert the same csv file. I was expecting that all the error records would be inserted (which happened) and the 3 that were inserted with previous upsert should be updated. But it gave me the error – Email already exists for those 3 records. Why so?

    • Tasnim Tailor May 20, 2014 at 7:30 pm #

      I checked, here was a trigger on Contact object that was showing off the email validation error. I just deactivated that and it worked fine. The only thing is I used upsert 3 times and it added the contacts 3 times to the related account. If I would like to prevent it, I need to add an external field to contact object and then DL would insert it the first time and then update.

      • JohnCoppedge June 10, 2014 at 9:15 pm #

        You would need to use the email address on the contact for the upsert as well; this would prevent duplicate contact creation – and allow you to specify the external ID of account as well in the operation.

        • Tasnim Tailor June 16, 2014 at 1:35 pm #

          I think I would need to specify Email as ExternalID for contacts, only then I would be able to avoid duplicate contacts being imported into each of the accounts. And because it is a standard field I cannot change it to external ID. Do I need to create another ExternalID on Contacts to avoid duplicate contact records being inserted?

          Please point me out, if I am misunderstanding it..

          • JohnCoppedge June 16, 2014 at 3:04 pm #

            Correct – you would need to create a second field. You could use a workflow rule to copy the value of the email address from one field to another. Or you could use the import wizards which I believe can perform this type of validation without using an external ID.

            Note that this will not find existing leads with the same email address (this is a very common reason folks look to 3rd party solutions).

  26. Rujuta Patil February 18, 2014 at 4:00 am #

    Once the data is loaded successfully I am unable to view the records on my SALESFORCE account object. Any other setting is required? Need help.

    • JohnCoppedge February 19, 2014 at 5:18 am #

      Hi Rujuta,

      Try running a search – but it may take a few minutes for the uploaded data to be indexed. If there are no results, wait a few minutes and then run the search again.

    • Kaira Bergstra February 24, 2014 at 1:13 am #

      create a new view, adding a criteria Account Name Starts with ‘Sage Import’

      To do this, try the following:
      (While logged into your Salesforce org) Click on Account tab –> right beside the Go! button, click “Create New View”. Fill in the fields as follows;

      View Name: Sage Import. Hit tab on your keyboard and it will auto populate the view unique name field.

      In the filter criteria, set the criteria as follows:
      Field=’Account Name’
      Operator=’Starts With’
      Value=’Sage Import’

      Hit ‘Save’ button and choose the newly created view then hit Go! button.

      On succeed, you should see all 13 account records you just loaded.

      • JohnCoppedge March 8, 2014 at 7:29 pm #

        Awesome, thanks for the tip!

      • Roger Grilo March 15, 2014 at 8:38 am #

        no need — just click on the Account tab, then choose the standard view “New This Week” and hit ‘Go’. All Sage Import accounts will appear.

  27. Yanique Bourjolly February 13, 2014 at 7:14 pm #

    For Mac users they need to use the Lexiloader

  28. Amey Parmarthi December 30, 2013 at 10:01 pm #

    Simple question. How do I view these imported accounts and contacts in salesforce?

    • JohnCoppedge February 19, 2014 at 5:17 am #

      Hi Amey,

      You can run a search, click the tab and then find an appropriate list view, run a report, etc. They won’t show up in recent items until you have viewed them, however.

      • Jeanne Busch October 23, 2014 at 5:33 pm #

        My Accounts page offered “New This Week” as a search criteria, which of course worked perfectly.

  29. Erin July 13, 2013 at 5:32 pm #

    my mapping does not offer SageID. I’m in my company’s sandbox / developer.
    is that an issue/limitation. it’s are only sandbox.

    • JohnCoppedge July 15, 2013 at 4:57 pm #

      External IDs will work in any environment. After you’ve added the external ID field, you will need to close the data loader and log back in (this allows the app to refresh the metadata).

Leave a Reply