[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
- Navigate to Setup –> Data Management –> Data Loader
- Download and install.
Step 2. Create the Account ID Field
- Navigate to Setup –> Customize –> Accounts –> Fields.
- Click New (to create a new field).
- Select Number. Click Next.
- Enter Label “Sage ID”.
Enter Length 8.
Check Unique and External ID.
Click Next.
- Click Next.
- 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:
- Navigate to Setup –> My Personal Information –> Reset My Security Token.
- Click “Reset Security Token”.
- Check your email to find the security token.
Step 5. Insert Account Data
- 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. - Choose “Insert”.
- Enter your username and password. Append your security token to the end of your password.
Click Log in.
Click Next.
- Select “Account”.
Choose the account.csv file.
Click Next.
- You should see the message “Initialization Succeeded. Your operation will contain 13 records.”
Click OK. - 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.
- 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.
- Switch to the Data Loader.
- Choose Upsert.
- Login if required.
- Select Contact.
Choose the contact import file.
Click Next.
- Click OK when prompted that your operation will contain 19 records.
- 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.
- 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.
- 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.
- Click Finish.
- 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]
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?
Do you have the new setup interface enabled? And are you logged in as a system administrator?
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?
Any chance you have a validation rule set up on the contact that requires a matching zip code?
It worked! I figured it out!
Awesome – probably the duplicate prevent rule getting the way?
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’
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.
At this moment you do not need your security token any more (at least on Mac). You can now login with help of OAuth.
Thanks for the heads up – I’m seeing the same on PC. Haven’t updated DL in a while… will update materials.
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…?
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.
You will need to disable contact duplicate rules as well.
Account CSV is not getting imported only 3 records worked. What could be the reason.
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).
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.
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.
Step 2. Should be select Number field type. Then proceed.
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.
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.).
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”? 🙂
Yes 🙂 That is the short answer. I will fix the sentence, thanks for the heads up.
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?
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.
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?
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
Note that the data loader doesn’t work with windows 8. https://success.salesforce.com/ideaView?id=08730000000kNzpAAE
Thanks Alex, good to know
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
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.
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.
Yes, that makes total sense. Moved the instruction up. Good luck tomorrow Aaron!
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?
Sounds like you have the new setup. Click on your name, then on my settings, then on Personal and you’ll see it!
Quite likely the case – there is a reference in the getting started section to turn this off… some of the instructions will vary otherwise.
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?
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.
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.
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..
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).
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.
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.
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.
Awesome, thanks for the tip!
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.
For Mac users they need to use the Lexiloader
Simple question. How do I view these imported accounts and contacts in salesforce?
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.
My Accounts page offered “New This Week” as a search criteria, which of course worked perfectly.
my mapping does not offer SageID. I’m in my company’s sandbox / developer.
is that an issue/limitation. it’s are only sandbox.
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).