Salesforce Account and Contact Update CSVs: Matching by Email, ID, or External ID

Updating existing Salesforce Accounts and Contacts from a CSV succeeds or fails on one decision: the match key you use to tie each row to the record it should change. Get it right and 5,000 rows update cleanly. Get it wrong and you create 5,000 duplicate contacts or overwrite the wrong records. You have three realistic keys: the Salesforce record ID (the safest), the email address, or a custom External ID field. This guide covers when each one applies, how the Data Import Wizard and Data Loader differ, and exactly what to check in the file before you point it at production.

This is the update-and-upsert companion to our Salesforce Data Loader error catalogue. That post explains the field-mapping and validation errors Data Loader throws. This one is about match-key strategy and dedupe: how to make sure every row lands on the right existing record instead of spawning a new one.

Which match key should you use to update records?

A match key is the column Salesforce reads to answer one question per row: does a record already exist that this row should update, or is this a brand-new record? Salesforce ranks your options by reliability, and so should you.

Data Import Wizard matching vs. Data Loader upsert

Salesforce gives you two tools, and they handle matching differently. Pick based on the key you have.

Data Import Wizard (update mode)

The Data Import Wizard is the point-and-click tool in Setup. To update, choose the object (Accounts and Contacts), select 'Update existing records', then set 'Match Contact by' and 'Match Account by'. Your options include Salesforce.com ID, Email, Name, and any External ID field on the object. Salesforce recommends matching by Salesforce.com ID because it is unique. One behavior worth memorizing: the Data Import Wizard will not overwrite an existing field with a blank value from your file. If a cell is empty, the current value in Salesforce stays put — the wizard treats blank as 'no change', not 'clear this field'.

Data Loader (upsert with an External ID)

Data Loader is the heavier tool for larger volumes. Its upsert operation is the reason External IDs exist: you pick a field marked as External ID, and Data Loader decides row by row whether to update a matching record or insert a new one. Upsert with an External ID also lets you set lookups and parent-child relationships (for example, tying a Contact to its Account) without ever knowing Salesforce's internal 15 or 18-character ID. If you are matching purely on the Salesforce record ID, a plain 'update' operation in Data Loader is enough — no External ID needed.

The 15 vs. 18-character ID trap

This is the single most common reason ID-based updates silently mismatch. A Salesforce record ID exists in two forms: a 15-character case-sensitive version and an 18-character case-safe version. The 18-character ID is the 15-character ID plus a 3-character checksum suffix, and it survives tools that don't preserve letter case. Excel and many CSV tools treat 'a0B' and 'a0b' as the same string. When a 15-character ID passes through Excel and gets case-folded, Salesforce can no longer match it — the update fails or, worse, hits the wrong record.

The fix: always use the 18-character ID when data leaves Salesforce for a spreadsheet. If your export only has 15-character IDs, convert them with the CASESAFEID() formula function in a Salesforce report or formula field before exporting, which returns the case-insensitive 18-character form. Never retype an ID by hand and never let Excel autocorrect it.

Why duplicate contacts get created on update

The frustrating outcome — you ran an 'update' and ended up with duplicates — almost always traces to the match key, not the tool. If you match by email and two rows in your file share the same email, or the file's email doesn't exactly equal what's in Salesforce (extra whitespace, different case, a typo), the row won't match an existing record and Salesforce inserts a new one instead. Blank match-key cells are the worst offenders: a row with an empty ID or empty email has nothing to match on, so it becomes a fresh record every time.

There is a related gotcha with Salesforce's own duplicate rules. By default, duplicate rules fire when a user creates or edits a record in the UI, but they do not fire on records inserted through Data Loader, the API, or bulk imports unless the org has explicitly configured enforcement for those paths. So a bulk update can happily create duplicates that the UI would have blocked. And when duplicate rules ARE set to 'Block' for bulk paths, the opposite happens: the load fails with a DUPLICATES_DETECTED error and the offending rows are rejected. Either way, the reliable prevention is a clean, deduplicated match key — not the rules engine.

Required fields and owner assignment

Even on an update, Salesforce enforces the object's required fields on any row it treats as an insert. For Contacts, LastName is the only universally required standard field — First Name is optional. For Accounts, Name is required. If your match key fails and a row falls through to insert, a missing LastName or Account Name will throw a 'required fields are missing' error. Your org may also have validation rules requiring Email, Phone, or custom fields, so check those before assuming a clean file.

On owner assignment specifically: the safest update file contains only the match-key column plus the fields you actually intend to change. If you don't want to touch ownership, leave OwnerId out of the file completely. Including it forces a decision on every row.

How to preflight the file before touching production

Before you run anything against live data, validate the file offline. The goal is zero blank keys, zero duplicate keys, and clean IDs. Here is the checklist we run on every Salesforce update file.

Before and after: fixing an update file

Here is a Contact update file straight out of a spreadsheet. It looks fine at a glance, but it has four problems that will produce duplicates and mismatches.

Before (broken):
Id,FirstName,LastName,Email
0038c00002aBcDe, Jose ,Ramos,jose.ramos@acme.com
5.0038E+17,Amy,Chen,amy.chen@acme.com
0038c00002aBcDe,Jose,Ramos,jose.ramos@acme.com
,Priya,Patel,priya.patel@globex.com
0038c00002xYzWv,Sam,,sam.lee@initech.com

Problems:
- Row 1: 15-char ID + whitespace around 'Jose' (leading/trailing spaces).
- Row 2: ID mangled into scientific notation by Excel — unrecoverable.
- Row 3: duplicate of row 1's Id — two rows targeting one record.
- Row 4: blank Id — will insert a NEW duplicate contact.
- Row 5: blank LastName — required field, errors if it inserts.

After (clean):
Id,FirstName,LastName,Email
0038c00002aBcDeQAK,Jose,Ramos,jose.ramos@acme.com
0038c00002pQrStQAK,Amy,Chen,amy.chen@acme.com
0038c00002xYzWvQAK,Sam,Lee,sam.lee@initech.com

Fixes:
- 18-char case-safe IDs (re-exported with CASESAFEID), kept as text.
- Whitespace trimmed from every cell.
- Duplicate Id row removed — one row per record.
- Blank-Id row pulled out for manual review (no key = no safe update).
- Missing LastName filled from the source system.

The cleanup steps here are exactly the kind of prep PipeSheets handles: trim whitespace across every column, standardize blank and null placeholder values, and drop empty rows in one pass with the Quick Clean preset, then preview the before/after with detected column types before you export. Because PipeSheets keeps ID and email columns as literal text, it won't strip a leading zero or flip an 18-character ID into scientific notation the way Excel does. It won't dedupe by Salesforce match logic for you — that judgment stays yours — but it gets the file to the point where the match key is clean, trimmed, and consistent, which is where most duplicate problems start.

Run a small test batch first

Once the file passes preflight, don't run all 5,000 rows at once. Load 10 to 20 representative rows first, confirm in Salesforce that they updated the records you expected (and created nothing new), then run the rest. If those first rows created duplicates, your match key is wrong and you've caught it at a scale you can clean up by hand instead of across the whole database. A clean key plus a small test batch is what separates a five-minute update from a weekend of merging duplicate contacts.