Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 36 ยท Data Management

Importing Data: Import Wizard and Data Loader

Load data into Salesforce safely: Data Import Wizard versus Data Loader, CSV preparation, external IDs and upsert, field mapping, error files and a pre-load checklist, with JavaScript stand-ins.

Data is why a CRM exists, and at some point you must get it in: the leads from a trade show, the customers from the previous system, the monthly update from finance. Importing is also where administrators do the most damage by accident, because a wrong mapping or a duplicate load can change thousands of records at once. This lesson explains the two standard tools, Data Import Wizard and Data Loader, how to prepare a file, how external IDs and upsert keep data clean, and how to read the result files.

You cannot run the real tools on this site. The menus and commands are described in words, and the demonstrations are JavaScript stand-ins that mimic the logic on small arrays. No real tool output appears here.

Choosing the tool

Data Import Wizard Data Loader
Where Setup, then Data Import Wizard (browser) A desktop client you install, or the command line
Size Up to 50,000 records per import Up to 5 million records per operation
Objects Accounts, contacts, leads, solutions, campaign members, and custom objects Any object, including standard objects the wizard does not support
Operations Add, update, add and update Insert, update, upsert, delete, hard delete, export, export all
Duplicate matching Built in (by name, email, Salesforce ID, external ID) You manage it with external IDs, Salesforce IDs and duplicate rules
Automation None Command-line mode, batch files, scheduled runs

Other tools exist: the Bulk API, dataloader.io, Workbench, Salesforce Inspector, and integration platforms (MuleSoft and ETL tools). The Data Loader needs a user with the API Enabled permission, and it uses your username, password (plus security token if you are outside trusted IP ranges, or OAuth in newer versions).

Prepare the file (CSV)

A clean CSV prevents most failures.

  1. One header row with the names of the columns; the first row is read as headers.
  2. UTF-8 encoding, so accented characters survive. Excel's default CSV can corrupt them; choose "CSV UTF-8".
  3. Dates in ISO form (2026-03-15) or a form your locale accepts; date-times with a time zone.
  4. Picklist values must match the existing values (or the import creates errors, depending on field settings).
  5. Required fields must be present: for example, Last Name on contacts and Company on leads.
  6. Record owner: provide an owner (username or Id), or the running user becomes owner.
  7. Parent links: to attach contacts to accounts, include the Account Id (a Salesforce Id of 15 or 18 characters) or an external Id of the account.
  8. Remove formulas and merged cells from the spreadsheet; export values only.

Always load a sample of about 10 rows first, in a sandbox if you can (see Sandboxes and environments), then check results before loading everything.

Salesforce IDs, external IDs and upsert

Every record has a Salesforce Id (15 characters, case sensitive; or 18 characters, case insensitive, which is what you should use in spreadsheets). A spreadsheet cannot know new Ids, so for repeat loads you use an external ID field: a custom field (text, number or email) marked External ID (and often Unique), holding the key from the other system, like Customer_Number__c.

With an external ID, upsert does the right thing without you looking up Ids:

  • If a record with that external Id exists, update it.
  • If not, insert a new one.

You can also use the external Id to relate records: a contact row can contain Account.Customer_Number__c as the parent reference instead of an Account Id. This works in the Data Loader mapping (relationship field), and it removes the VLOOKUP step.

flowchart LR
  F["CSV file"] --> M["Map columns to fields"]
  M --> V{"Operation"}
  V -->|"Insert"| I["Create new records"]
  V -->|"Update"| U["Needs Salesforce Id"]
  V -->|"Upsert"| S["Match on external Id: update or insert"]
  I --> R["Success file + Error file"]
  U --> R
  S --> R

The Data Loader flow: map, choose the operation, then read the success and error files.

Using the Data Loader

  1. Choose Insert, Update, Upsert, Delete, Hard Delete, Export or Export All (Export All includes deleted and archived records).
  2. Log in, then choose the object (and the external Id field, for upsert).
  3. Select the CSV.
  4. Create or Edit a Map: match CSV columns to Salesforce fields. Use Auto-Match Fields to Columns and fix mistakes by hand. Save the map as an .sdl file to reuse it.
  5. Click OK, choose where to save the results. Click Finish.

Two result files appear: a success file (the rows that loaded, with the new record Ids) and an error file (the rows that failed, with an ERROR column describing why). Fix the error rows and reload only those.

Settings worth knowing: Batch size (200 by default; lower it if triggers or flows time out), Use Bulk API (for big loads, with parallel processing; disable parallel mode if you hit row-lock errors), Insert null values (needed to blank a field), Assignment rule (to fire lead/case assignment rules), and Timezone.

Common errors

  • REQUIRED_FIELD_MISSING: a required field is empty.
  • INVALID_CROSS_REFERENCE_KEY: a parent Id (account, owner) does not exist or is not accessible.
  • STRING_TOO_LONG: text is longer than the field length.
  • DUPLICATES_DETECTED: a duplicate rule blocked the record (see Duplicates and data quality).
  • UNABLE_TO_LOCK_ROW: parallel batches touched the same parent record at the same time; reduce the batch size or run serially.
  • FIELD_CUSTOM_VALIDATION_EXCEPTION: a validation rule blocked the row.
  • Flow or trigger errors: automation ran on the loaded rows and failed (turn off non-essential automation for the load, or load in smaller batches).

Stand-in: upsert on an external ID

This stand-in is not the Data Loader. It does what upsert does: update the record if the key exists, insert otherwise, and write success and error lists.

JS Stand-in: upsert with success and error files

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Stand-in: a pre-load checker

JS Stand-in: validate a CSV before loading

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Pre-load checklist

  1. Back up affected data first: export the current records (see Backup, export and recovery).
  2. Test in a sandbox with 10 to 100 rows.
  3. Check validation rules, required fields, duplicate rules, flows and triggers that will run.
  4. Decide the operation: insert, update or upsert; confirm the key.
  5. Save the mapping; run with a small batch; verify; then run the rest.
  6. Keep the success and error files with the ticket or change record.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. You must load 2 million rows into a standard object. Which tool?

    The wizard handles up to 50,000 records; Data Loader handles millions.
  2. 2. What does upsert do with an external ID?

    Upsert matches on the external Id and chooses update or insert for each row.
  3. 3. Which file lists the rows that failed and why?

    The error file contains the failed rows with an ERROR column.
  4. 4. Why use the 18-character Salesforce Id in spreadsheets?

    The 15-character Id is case sensitive; spreadsheets often ignore case, so use the 18-character form.

Summary

  • Use the Import Wizard for small, simple loads and the Data Loader for large volumes, deletes, exports and automation.
  • Prepare a UTF-8 CSV with valid picklist values, required fields and parent keys.
  • External IDs and upsert make repeat loads safe and avoid lookups of Salesforce Ids.
  • Read the success and error files, reload only failures, and test in a sandbox first.

Next: Duplicates and data quality