DEV Community

Richard C
Richard C

Posted on AI-assisted

Check a Salesforce Contact export after a load: orphans, duplicate External IDs, and picklist mismatches

#salesforce #csv #soql #tutorial

AI-Assisted

By Richard C, AIL Studio

After a Salesforce insert or upsert, the success file can say every row loaded while the Contact export still has problems you can see without logging into Setup. A Contact can have a blank AccountId. Two Contacts can share the same External ID. A picklist column can hold a value that is not in the field’s allowed API names.

This walkthrough checks the files you already exported. You will count orphans, find AccountIds that do not exist in the Account file, find duplicate External IDs, and compare one picklist column to an allowed-value list. Optional sections repeat the same work in Python (standard library only) and confirm the counts with SOQL.

All tables below are EXAMPLE DATA. They are not from a customer org. Replace column headers such as Legacy_Id__c with the External ID your org actually uses.

Salesforce Help documents that External ID values are used to match related records in Data Loader (Import Related Records with an External ID), and that a duplicate in a unique field produces a “Duplicate value found” error (Common error messages in Data Loader). Picklist values in a CSV must match a value in the field; restricted picklists reject values that are not in the defined set (Prepare a CSV File for an Import or Update, INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST). If the picklist is not restricted, Data Loader can write a value that is not in the defined list (Data Loader Allows Invalid Picklist Values to Be Created). Checking the export against the allowed API names catches both cases.

SOQL results store picklist API names, not the labels users see (Understanding Picklist Value Labels and API Names). Compare your file to API names, not to the label column in Setup, unless you have confirmed they are identical.

1. Export the two files

Use Data Loader (or another export path your admin already approves) and include at least:

Contacts — Id, AccountId, LastName, Email, your Contact External ID, and one picklist (this example uses LeadSource; replace it with the picklist you actually loaded).

Accounts — Id and the Account External ID if you have one.

Keep Salesforce Id in every file. Prefer Id later if you build an update file. Do not invent Account Ids in the sheet.

A Contacts report export is fine for a small review set. Use Data Loader (or an equivalent bulk export) when you need every row; some reports do not return the full object.

2. EXAMPLE DATA

contacts.csv (EXAMPLE DATA)

Id LastName Email AccountId Legacy_Id__c LeadSource
003000000000001AAA Example renee@example.invalid 001000000000001AAA CT-001 Web
003000000000002AAA Example sam@example.invalid CT-002 Phone Inquiry
003000000000003AAA Example jo@example.invalid 001000000000999AAA CT-003 Phne Inquiry
003000000000004AAA Example lee@example.invalid 001000000000001AAA CT-001 Partner Referral

accounts.csv (EXAMPLE DATA)

Id Name Legacy_Id__c
001000000000001AAA Aster Labs AC-001
001000000000002AAA Blue Orchard AC-002

allowed_leadsource.csv (EXAMPLE DATA) — API names from the picklist value set:

LeadSource
Web
Phone Inquiry
Partner Referral

Expected findings on this set:

  • One orphan: Sam, because AccountId is blank.
  • One unresolved parent: Jo, because 001000000000999AAA is not in the Account file.
  • One duplicated External ID: CT-001 appears on Renée and Lee.
  • One picklist mismatch: Jo’s Phne Inquiry is not in the allowed list.

The Ids above are fabricated placeholders, not real Salesforce Ids.

3. Spreadsheet checks

Put Contacts on a sheet named Contacts (headers in row 1). Put Accounts on a sheet named Accounts. Put allowed picklist values in Allowed starting at A2.

Treat a blank AccountId cell as an orphan candidate. If your export tool writes #N/A or the literal null, normalize those to blank before you count.

Orphans (blank AccountId) in G2, filled down:

=IF(D2="","orphan","")
Enter fullscreen mode Exit fullscreen mode

Count:

=COUNTIF(G:G,"orphan")
Enter fullscreen mode Exit fullscreen mode

Unresolved AccountId (a value is present but it does not appear in the Account Id column). In H2:

=IF(D2="","",IF(COUNTIF(Accounts!A:A,D2)=0,"unresolved",""))
Enter fullscreen mode Exit fullscreen mode

Count:

=COUNTIF(H:H,"unresolved")
Enter fullscreen mode Exit fullscreen mode

Duplicate External ID (Legacy_Id__c in column E). In I2:

=IF(E2="","",IF(COUNTIF(E:E,E2)>1,"duplicate external id",""))
Enter fullscreen mode Exit fullscreen mode

Count of flagged rows (each extra copy is flagged, including the first):

=COUNTIF(I:I,"duplicate external id")
Enter fullscreen mode Exit fullscreen mode

Picklist not in the allowed API names. In J2:

=IF(F2="","",IF(COUNTIF(Allowed!A:A,F2)=0,"picklist mismatch",""))
Enter fullscreen mode Exit fullscreen mode

COUNTIF here is case-insensitive in common spreadsheet tools, so web and Web would both match. If your org’s picklist is case-sensitive at the API-name level, use an exact-match method (EXACT in an array, or the Python section below).

Filter each flag column to copy the flagged rows onto a review sheet. Do not attach an orphan to the first Account that “looks similar.” A wrong AccountId is a different defect from a blank one.

4. Optional: the same checks in Python

This script uses only the standard library. Save it next to the three example CSVs and run python check_export.py. It prints counts and the flagged External IDs or row emails. EXAMPLE DATA only until you point it at your own files.

import csv
from collections import Counter

def rows(path):
    with open(path, newline="", encoding="utf-8") as handle:
        return list(csv.DictReader(handle))

contacts = rows("contacts.csv")
account_ids = {r["Id"] for r in rows("accounts.csv")}
allowed = {r["LeadSource"] for r in rows("allowed_leadsource.csv")}

orphans = [r for r in contacts if not (r.get("AccountId") or "").strip()]
unresolved = [
    r for r in contacts
    if (r.get("AccountId") or "").strip() and r["AccountId"] not in account_ids
]
ext_counts = Counter((r.get("Legacy_Id__c") or "").strip() for r in contacts)
dup_ext = [value for value, n in ext_counts.items() if value and n > 1]
picklist_miss = [
    r for r in contacts
    if (r.get("LeadSource") or "").strip() and r["LeadSource"] not in allowed
]

print("orphan_count", len(orphans))
print("unresolved_count", len(unresolved))
print("duplicate_external_ids", dup_ext)
print("picklist_mismatch_count", len(picklist_miss))
for row in orphans:
    print("orphan", row.get("Email"), row.get("Legacy_Id__c"))
for row in unresolved:
    print("unresolved", row.get("Email"), row.get("AccountId"))
for row in picklist_miss:
    print("picklist", row.get("Email"), row.get("LeadSource"))
Enter fullscreen mode Exit fullscreen mode

On the example files this prints one orphan, one unresolved AccountId, duplicate External ID CT-001, and one picklist mismatch (Phne Inquiry). Re-run it against your export after you change the file names and field headers.

5. Optional: confirm the counts with SOQL

The file is not always the same as the org (filters, permissions, a report cap). If you can run SOQL, compare the file counts to the live object.

Orphans:

SELECT COUNT()
FROM Contact
WHERE AccountId = null
Enter fullscreen mode Exit fullscreen mode

List:

SELECT Id, LastName, Email, Legacy_Id__c
FROM Contact
WHERE AccountId = null
Enter fullscreen mode Exit fullscreen mode

Replace Legacy_Id__c with your field. Duplicate External IDs in the org:

SELECT Legacy_Id__c, COUNT(Id)
FROM Contact
GROUP BY Legacy_Id__c
HAVING COUNT(Id) > 1
Enter fullscreen mode Exit fullscreen mode

GROUP BY / HAVING needs the field to be groupable; if your External ID is not, use the spreadsheet or Python check on a full Data Loader export instead.

A picklist value that is not in the allowed set will not always appear as a SOQL error after the fact. If the field was unrestricted at load time, the stored API name can be a string that Setup does not list. That is why the file-versus-allowed-list check still matters after a “successful” load.

No Salesforce org was queried to write this article. Paste the queries in a tool you already have permission to use (Developer Console, VS Code, Workbench, or your approved admin path).

6. What these checks do not prove

  • A blank AccountId can be an intentional private Contact. Treat orphans as a review list, not a delete list.
  • An AccountId that exists but points at the wrong company will not show up as unresolved. That needs a source-to-target key compare, which is a separate check.
  • Duplicate names or emails are not the same as duplicate External IDs. This article only flags the External ID column you name.
  • Permissions matter: SOQL you cannot see, you cannot count.
  • Matching a picklist label instead of its API name will produce false mismatches (picklist labels vs API names).

Do not load a “fix” file until someone has read the flagged rows.

AIL Studio is not affiliated with Salesforce. Salesforce is a trademark of Salesforce, Inc.

If you would rather have someone run these checks on your exports and send back a findings report, AIL Studio offers a Salesforce post-migration data check on Fiverr.

Top comments (0)