Importing & Manipulating Data

Data Import Wizards

There are now two import wizards in Salesforce:

  1. Import Accounts & Contacts
    • This wizard is specifically designed to accept pre-formatted data from common contact data sources (Outlook, Gmail, LinkedIn, etc.).
  2. Data Import Wizard
    • This is a general-use wizard and can import records into the following objects:
      • Accounts
      • Contacts
      • Leads
      • Solutions
      • Campaign  Members
      • Custom Objects
    • Data Import How To Series
      [Should / ~15m / Salesforce.com]

In 2015, Salesforce revamped the existing data import wizards, launching the wizards above.  The existing “legacy” import wizards were retired completely in the Winter ’17 release.  The data import wizards now support external ids (Summer ’17 – see Match Look-Up Fields with an External ID).

Data Manipulation Wizards

The following wizards are available through the user interface:

ToolAffectAvailable Objects
Mass Transfer RecordsChanges record ownership (e.g. transfer 100 leads from one user to another user).Leads, Accounts, Service Contracts, Custom Objects
Mass Delete RecordsDeletes records.Leads, Accounts, Activities, Contacts, Cases, Solutions, Products
Mass Transfer Approval RequestsTransfers pending approvals from one user to another (e.g. someone leaves the company, transfer all pending approvals to their supervisor).
Mass Reassign Account TeamsAdd, remove, or replace members within an account team.Account Teams
Mass Reassign Opportunity TeamsAdd, remove, or replace members within an opportunity team.Opportunity Teams
Mass Update AddressesUsed to standardize variances in the country and state fields (e.g. transform all instances of "Ohio" to "OH").Address fields on accounts, contacts, contracts, leads.

Note that many standard objects are not supported by several of the wizards.  For instance, you cannot mass transfer opportunities (although the account transfer wizard will allow you transfer opportunities related to an account).

Data Loader

The data loader is purely a data manipulation tool.  It gives the administrator a great deal of flexibility (almost all objects and data can be accessed) but provides little to no guidance in how to achieve a specific task (e.g. using the Import Accounts & Contacts wizard will automatically establish the relationship between an imported contact and an account; this relationship needs to be established within the data via a Salesforce ID or an External ID when using the data loader).

As of the Summer ’15 Release, the Data Loader is now available for Windows and Mac (previously not available for Mac).

Where to get the Data Loader

  1. Navigate to Setup –> Data Management –> Data Loader
  2. Download & Install

1-24-2013 12-36-37 PM

Data Loader Operations

ActionDescription
InsertCreates new records.
UpdateUpdates existing records. The Salesforce ID of each record must be defined within the source file.
UpsertUpdates an existing record if present; if record not present, creates a new record. Matching can be based on Salesforce ID or external IDs.
DeleteDeletes records. The Salesforce ID must be defined within the source file.
Hard DeletePermanently deletes; records are not moved to the recycle bin. The Salesforce ID must be defined within the source file.
ExportExtracts records from Salesforce; excludes records in your recycle bin.
Export AllExtracts records from Salesforce; includes records in your recycle bin.

Wizards vs. Data Loader

The Data Loader is a desktop tool that uses the API to interact with Salesforce data.  Your computer is requesting and making the changes to Salesforce data, whereas when using the import wizards you upload a file and it is then processed server-side by Salesforce.  This is important for a few reasons:

  • You need the API in order to use the Data Loader (typically Enterprise Edition or higher, or a cost for lower editions).
  • The wizards offer options that are not available via the Data Loader (e.g. disable workflow rules when loading data — workflow rules will always be triggered by the Data Loader).
  • The permissions are different.  Access to the wizards are driven by specific permissions (e.g. ‘Import Personal Contacts’ and ‘Import Leads’ permissions on profile/permission set), while Data Loader permissions are driven by the global ‘API Enabled’ permission on profile/permission set.
  • The import wizards can load files with up to 50,000 records per job.  Transfer wizards can transfer up to 250 records at a time.  The Data Loader, however, can process up to 5 million records per job.  Therefore, if you need to process large volumes of data (e.g. create 250k accounts or transfer 10k accounts), use the Data Loader.
  • An administrator can save mapping using the Data Loader and even schedule jobs on a local server

Other Data Manipulation Tools

NameTypePriceDescription
dataloader.ioWebFreeWeb-based tool with similar functionality to the Data Loader.
Force.com WorkbenchWebFreeAllows you to explore and manipulate data, metadata, and perform a variety of other functions. Built by Salesforce.com (may not be officially supported).

Note:  The above tools are used for data manipulation.  For data cleansing tools, see Introduction to Data Quality.

80 Responses to “Importing & Manipulating Data”

  1. bregirl.2@gmail.com November 5, 2017 at 9:24 pm #

    John,

    Enjoying your site & the quizes! Please update to include Data Import Wizard being able to import campaign members, person accounts also, per attached reference link.

    https://help.salesforce.com/articleView?id=faq_data_import_wizard_what_kind_objects.htm&type=0

    Thank you!

  2. valerierbp June 19, 2017 at 6:55 pm #

    I am trying to create child custom objects with a one to many relationship to the parent Account using the data import wizard but it is causing an error because of the “many” piece. I am not sure what I’m doing wrong here. I am wondering if I’m not setting up the master-detail field properly or if I have to use Data Loader (I’ve reached my limit this month with them and will pay the 100 bucks but want to be sure I need to first). Thanks!

    • JohnCoppedge June 22, 2017 at 12:57 pm #

      What’s the error?

      The detail record will need to have the id of the account populated in order to insert the record (or upsert) – you can’t load a detail record without the parent established.

      The data loader application is free – not sure what’s asking for $100 but you shouldn’t need to pay to load data.

  3. anumedha June 10, 2017 at 12:48 am #

    When using the import wizards, will an import fail if the import violates a data validation rule?
    Can u please give the right answer ?

    Thanks
    Anu

  4. san2016 May 4, 2017 at 7:10 pm #

    Hi John,
    Is there any Mass reassign account teams in setup area in sf?

  5. rashminder@techneosys.com March 17, 2017 at 3:25 am #

    Hi John,

    Why would I need Slaesforce ID to simply delete or hard delete records through the data loader?

    thanks
    rashminder

  6. jobzmpons@hotmail.com November 22, 2016 at 4:35 am #

    Hi John,
    how come i do not see Import My Accounts & Contacts under My Settings > Import? I use a developer org and our training org. Is this something we need to activate? I only see Data Import Wizard on both envts.

    Jobelyn

  7. satyaalluri October 13, 2016 at 12:16 am #

    Import Your Accounts and Contacts link is giving error

  8. jtsai9578 September 2, 2016 at 3:14 am #

    Hi John,
    Can Import Wizard avoid duplicating records when you import?

    Thank you,

    Johnny

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

      Yes but very limited in options- e.g. name or email for contacts.

      • sneha06 September 30, 2016 at 8:13 am #

        Hi John,

        Can data loader avoid duplication?

        Which one is used to de-duplicate a record from below tool?
        1. Data loader
        2. Import Wizard

        • JohnCoppedge September 30, 2016 at 2:36 pm #

          the data loader can only avoid duplicates using an external id. the import wizard has more options for some objects but is still fairly limited

  9. lhooper August 21, 2016 at 6:59 pm #

    There are some WordPress errors at the end of this page. I don’t know if you had intended more content on this page or these are just errors that need to be removed.

    • JohnCoppedge August 25, 2016 at 11:36 am #

      I’ve seen that a few times- usually a page refresh fixes the issue. Keeping an eye on it.

  10. sergeik June 24, 2016 at 1:34 pm #

    Another free alternative to Salesforce data loader with powerful features – https://skyvia.com/data-integration/salesforce-data-loader

  11. vijay24805 April 23, 2016 at 12:13 am #

    Sorry Forgot to check Notify me when new comment are added, so adding an extra comment to get notified.

  12. vijay24805 April 23, 2016 at 12:02 am #

    In your statement- The wizards offer options that are not available via the Data Loader (e.g. the ability to specify which lead assignment rule to process)

    I guess we can specify the assignment rule ID in the Data Loader. Can you please confirm if your above statement is correct?

    • JohnCoppedge August 19, 2016 at 8:23 pm #

      I believe you can trigger the lead assignment rule with data loader- removed that segment. There is some automation (e.g. matching on name) that the import wizard can do though, and data loader cannot.

  13. hassanuniquest February 18, 2016 at 11:31 pm #

    HI Jhon

    Thanks for all the content much appreciated.
    I was wondering if you could help.
    Lets say you wanted to integrate 2 databases, so contact object in SF whilst the other is FTP source with a flat file which which has contacts.
    I wanted to import the external ids by matching the names from the FTP source into the the Contacts in salesforce. If the names match then upsert and i would map over the the external ID.

    From then on, i would use the external ID to match and upsert since the ID is already.

    Jhon in your experrience how is this process typically done via an ETL tool? Whats the process of integrating an sql database lets say or FTP source with a flat file with an object in salesforce. Most importantly how would I map the records in the other database to salesforce if salesforce does not have the ID’s from the other database. The simplest way I could think of was the above scenario.
    Would be grateful for your advice or from other members thanks.

    • JohnCoppedge February 22, 2016 at 9:55 pm #

      Typically an ETL tool- yes. You’d need an intermediate tool that could interface with FTP (direct to another database such as SQL is often easier). The mapping between the fields and the actions that are processed would all be determined by the ETL tool.

      In the config for the ETL job, you would want to specify the action (UPSERT) with the mapping to the external ID. Specifying the UPSERT action would then allow you to match the ID of the source (FTP or SQL for example) to the custom external ID field. Hope that makes sense!

  14. Basil Dobek December 31, 2015 at 7:38 pm #

    I think this page is out of date, SF link below shows it’s now 50k for end users (https://youtu.be/VMW94CuoEYo?t=82)

  15. soraya.attia@papilioconsulting.be November 11, 2015 at 7:53 am #

    In the note : “Note that many standard objects are not supported by several of the wizards. For instance, you cannot mass transfer opportunities (although the account transfer wizard will allow you transfer opportunities related to an account).”

    What is the account transfer wizard? It does not appear in your ‘transfer wizard list’. Is this perhaps the same as the Import Account/Contacts wizard?

    • JohnCoppedge January 9, 2016 at 2:50 am #

      From the setup menu, use the “Mass Transfer Records” menu option- that will give you the list of supported objects.

  16. soraya.attia@papilioconsulting.be November 11, 2015 at 7:51 am #

    In the section “Each of the import wizards has slightly different options for preventing duplicate records”
    when I test this via my dev org and go to Setup > Personal Setup > Import > Import My Accounts & Contacts I do not see the screen that you provided in order to manage duplication.
    How can I do this?

    • JohnCoppedge January 9, 2016 at 2:46 am #

      Thanks for pointing this out- this was from the legacy importer, it is no longer valid. Updated content accordingly.

    • soraya.attia@papilioconsulting.be November 10, 2015 at 5:31 pm #

      Indeed spotted it too :
      ◦How many contact and business account records can I import?
      [Could / Short / Salesforce.com]

  18. Ezekiel Apte August 29, 2015 at 1:38 pm #

    John – In the Wizards vs. Data Loader section you mention that the Data Loader will Always trigger workflow rules. However in the Salesforce Video earlier: http://salesforce.vidyard.com/watch/ARIjWm2qrDkJVJxEhReFug it mentions that though there is no option to turn workflow rules off during an import it can be turned off in the Setup pages.

    • Ezekiel Apte August 29, 2015 at 2:19 pm #

      Ignore the above.
      On further video watching I realized they meant the Setup page of the workflow rule itself (in order to deactivate it so it does not trigger) and not, as I thought any setup associated with the Data loader itself.

  19. Sbock August 19, 2015 at 6:47 am #

    Ugh, i cant type, Sorry

    How many contact and business account records can I import?

  20. Sbock August 19, 2015 at 6:45 am #

    Hi, Minor spelling mistake

    Under section 1
    Import Accounts and Contacts

    How many contat and business account records can I import?

    Should read

    How many <> and business account records can I import?

  21. Ezekiel Apte August 7, 2015 at 10:32 am #

    In my Dev Org when I browsed into Mass Transfer / Delete /Update Addresses the displayed options/links are different than what has been recorded here. My Dev Org shows:
    Mass Transfer: Accounts, Leads, Macros, Streaming Channels.
    Mass Delete: Accounts, Leads, Activities, Contacts, Cases, Solutions, Products, Reports.
    Mass Update Addresses: accounts, contacts, contracts (active & inactive), leads, orders (active & draft).
    Is this a recent change in Salesforce or different editions showing choices ?

  22. Bruce Frager April 6, 2015 at 11:27 pm #

    I think it is worth noting that end users have the ability to ‘Import My Accounts & Contacts’ (up to 500) without access to the setup menu. A couple other differences between the Data Loader & Import Wizards are worth pointing out such as handling of duplicates (Import Wizard can dedup), Import Wizard asks whether to disable workflow & validation rules. Data Loader can save field mapping and set up a schedule.

    • Rena Bennett-Dellwo May 4, 2015 at 7:00 pm #

      Just to confirm:
      validation rules run when Data Loader and Import Wizards are used, but only the Import Wizard gives you the option of turning VR off?

      • JohnCoppedge August 15, 2015 at 7:22 pm #

        I didn’t see an option to disable validation rules – but yes validation and workflow will always take place for data loader. For import wizards, you can opt to disable workflow.

    • JohnCoppedge August 15, 2015 at 7:20 pm #

      Updated, thanks Bruce

    • soraya.attia@papilioconsulting.be November 11, 2015 at 8:11 am #

      Hi Bruce,
      Does this mean that there is a difference between import by end-user and import by administrator via the Import individual wizards?
      According to the documentation it says import wizard is up to 50K?

    • soraya.attia@papilioconsulting.be November 11, 2015 at 8:15 am #

      Bruce, disregard my question above, I found the reply just now and later in this section by John, it is indeed 500 for users and 50K for administrators.

  23. Bruce Frager April 6, 2015 at 11:01 pm #

    Since you are discussing ‘data manipulation’ in a broad sense, you might as well include the other 2 data wizards ‘ Mass Reassign Acct Teams’ and ‘ Mass Reassign Oppty Teams’.

  24. Bruce Frager April 6, 2015 at 10:52 pm #

    In addition to the objects that you have listed that can be imported via the user interface
    Accounts, Contacts, Leads, Solutions and Custom Objects, the object ‘Articles’ also has a object specific import wizard.

  25. Patricia Boyce March 26, 2015 at 9:20 pm #

    It seems that another reason to use Data Loader–even if your job is smaller, is if you want to load something that Data Import Wizard doesn’t support–all the rest besides Account/Contact, Lead, Solutions, Custom Objects. Is this something that might weigh into a test answer? I saw a Quizlet card that asked what you could do with Data Loader, Import 50,000 or less was a wrong answer. But you would use it for less if you wanted/needed to. Right?

    • JohnCoppedge March 27, 2015 at 4:51 pm #

      Yes you would… I always use the data loader, literally cannot remember the last time I’ve used the import wizards. Its more a question of which objects are exposed and personal preference.

  26. Naureen Hameed October 19, 2014 at 5:01 pm #

    Hi John
    I attempted the exam few months ago and remember a question i couldn’t answer.
    Tried searching for it but no luck. Hope you can help. Thanks

    Q) What happens if while importing, Wizard or Data Loader comes across an error.

    1)No impact on Importing process and it keeps going
    2)The import interrupts and stops
    3) Import resumes after fixing the error from the point where it stopped

    • JohnCoppedge October 23, 2014 at 12:51 am #

      If you get an error in processing with the data loader, those errors are handled on a line by line basis. For example, if you are updating 1000 records – and 600 of them have errors, then 400 will be processed. The process doesn’t stop when an error is detected, but those 600 lines with errors will not have any impact in system.

      • Alex Messinger December 18, 2014 at 3:23 pm #

        So, would that mean the answer to the question is #1?

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

          Yep, although I suspect the wording is a bit different – I wouldn’t use the term no impact, as the records that had errors would not be imported. But yes the process would continue.

          • ancrabtree September 23, 2015 at 3:33 am #

            This question is similar to one still on the test but it pertains to a validation error. And one of answers was it would continue but not import those with the validation error. Is that correct?

          • JohnCoppedge September 23, 2015 at 2:08 pm #

            Yes if you’re loading data using the data loader and a validation prevents a save, it will continue through the rest of the data set.

  27. gera;d dente June 25, 2014 at 11:18 am #

    Why did workbench.developerforce.com/ not make your list? Its what I have been using since I started exporting/importing data.

  28. Sudheendra Karri May 12, 2014 at 2:06 pm #

    I am using a Developer edition.

    When trying to import a bunch of users into my Org, I assigned the License Type = Chatter Free.
    However during the import, get an error “License Limit Exceeded”.

    I cannot assign a Salesforce license either since there are only 2 available.
    How do we go about importing users to the Org please ?

  29. Daniel Ryan March 2, 2014 at 12:35 pm #

    Does anyone know if the wizards or data loaders will allow for changes to be tracked and show in the chatter feed when updating records?

    • JohnCoppedge March 8, 2014 at 6:56 pm #

      Daniel- good question. I haven’t specifically tested this, but I would be surprised if an API-based change to data did not also feed Chatter. I know for certain that API-based changes do update field history tracking. If you get a chance to test that I’d love to know the results.

  30. Kaira Bergstra January 14, 2014 at 7:58 pm #

    Is there any reason why the Import Wizard covers the objects that it does? Why not import all standard objects, for example? I’m finding it hard to memorize because it seems so random.

    Thanks,
    Kaira

    • JohnCoppedge January 16, 2014 at 11:44 pm #

      Some aspects of Salesforce are a bit random. They likely developed organically based on customer needs and priorities at the time that seem random years later.

      I wouldn’t worry about memorizing everything though – I high doubt that you will be asked a question where you need to know that a specific data wizard doesn’t work on X object. The questions will be more focused around what methods exist and generally speaking what the best approach would be.

  31. John Stevens January 4, 2014 at 7:32 pm #

    Jitterbit Data Loader works well and supports Group and Professional editions in addition to Enterprise and Unlimited. It’s free too.

  32. Karen October 29, 2013 at 7:54 pm #

    CRMFusion’s DemandTools is the best.

Leave a Reply