Exercise: Upsert Using External ID

[ExerciseIntro]

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).

[sc:ExerciseSteps ]

[RCNotLoggedInNotAdmin]

[/RCNotLoggedInNotAdmin]

[RCAdmin]

[box type=”download” style=”rounded” border=”full”]Download the Source Files:
Accounts.csv
Contacts.csv[/box]

[box type=”note” style=”rounded” border=”full”]Make sure to disable any Data.com duplicate rules before attempting this exercise.  This is step is required for any recently created developer edition account.  Navigate to Setup –> Data.com Administration –> Duplicate Management –> Duplicate Rules, then select and deactivate any active rules for the Contact and Account object.[/box]

Step 1. Download and Install the Data Loader

  1. Navigate to Setup –> Data Management –> Data Loader
  2. Download and install.

Step 2. Create the Account ID Field

  1. Navigate to Setup –> Customize –> Accounts –> Fields.
  2. Click New (to create a new field).
  3. Select Number.  Click Next.
  4. Enter Label “Sage ID”.
    Enter Length 8.
    Check Unique and External ID.
    Click Next.
    1-30-2013 10-30-35 AM
  5. Click Next.
  6. Click Save.

Step 4. Reset Your Security Token [May be Optional with Latest Data Loader]

If you do not already have your security token, then:

  1. Navigate to Setup –> My Personal Information –> Reset My Security Token.
  2. Click “Reset Security Token”.
  3. Check your email to find the security token.

Step 5. Insert Account Data

  1. Open the Data Loader.
    Note:  If this is your first time running the data loader, you will be prompted at some point during the below steps to choose a directory to store success and error files.  Choose (and create if needed) a directory that makes sense for you.  These log files can prove incredibly helpful if something goes wrong with your data load.
  2. Choose “Insert”.
  3. Enter your username and password.  Append your security token to the end of your password.
    Click Log in.
    Click Next.
    1-30-2013 10-45-53 AM
  4. Select “Account”.
    Choose the account.csv file.
    Click Next.
    1-30-2013 10-49-19 AM
  5. You should see the message “Initialization Succeeded.  Your operation will contain 13 records.”
    Click OK.
  6. Click “Create or Edit a Map”.
    Click Auto-Match Fields to Columns.
    This should align align all fields.  Ensure Sage ID is mapped properly as shown below.
    Click OK.
    Click Finish.
    1-30-2013 11-30-28 AM
  7. Click Yes when prompted to insert records.

Step 6. Upsert Contact Records

The contacts import file does not have the Salesforce account ID, but instead references the account’s Sage ID.  Therefore we will need to upsert contacts, referencing the external ID on the account.

  1. Switch to the Data Loader.
  2. Choose Upsert.
  3. Login if required.
  4. Select Contact.
    Choose the contact import file.
    Click Next.
    1-30-2013 11-50-10 AM
  5. Click OK when prompted that your operation will contain 19 records.
  6. Ensure Id is selected as shown below.
    Click Next.
    Note: you cannot use the “Insert” operation in conjunction with external IDs; however matching on contact ID (and at the same time not providing a contact ID), will have the same affect as inserting the data.
    1-30-2013 11-53-55 AM
  7. Select Sage_ID__c.
    Click Next.
    Note: if Sage_ID__c is not listed, ensure the field was created properly in the steps above, and then restart the data loader.
    1-30-2013 11-56-18 AM
  8. Click Create or Edit a Map.
    Click Auto-Match Fields to Columns.
    Drag “Account:Sage_ID__c” to SAGE_ACCOUNT_ID as shown below.
    Click OK.
    Click Next.
    1-30-2013 11-59-08 AM
  9. Click Finish.
  10. Click Yes.

I recommend viewing the resulting log files once you are completed.

[sc:ExerciseSuccessCriteria]

Each account within the sample data has been loaded into Salesforce (this is the same data present in new developer edition orgs, with “Sage Import – ” appended to the account name).

Each contact within the sample data has been loaded into Salesforce, and is associated with one of the imported account records.

[/RCAdmin]

51 thoughts on “Exercise: Upsert Using External ID”

  1. John,

    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?

  2. 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?

  3. 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’

  4. 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.

  5. 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…?

    1. 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.

    1. 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).

  6. 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.

  7. 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.

    1. 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.).

  8. 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”? 🙂

  9. Hi,

    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?

  10. 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?

  11. 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

      1. 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.

  12. 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?

  13. 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?

    1. 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.

      1. 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.

        1. 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..

          1. 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).

    1. 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.

      1. 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.

Leave a Reply