Validate Salesforce Picklist Fields Before CSV Import
Salesforce enforces strict validation on dependent picklists (State/Country). When importing trade show leads, source data contains wild variations: 'US', 'U.S.A.', 'United States', 'USA' all refer to the same country, but Salesforce rejects anything that doesn't exactly match its configured picklist. ISO 3166-1 alpha-2 codes (US, GB, DE) are the Salesforce standard. Crucially, Salesforce's Data Import Wizard (max 50K records) rejects the ENTIRE batch if any row fails picklist validation—it doesn't skip bad rows. Data Loader (max 5M records) offers 'skip invalid rows' but requires CLI setup. Neither provides a pre-flight validation report. This SOP cross-references your Country/State columns against standard picklists, applies regex-based normalization, and generates a clean file ready for a 100% successful upsert.
Why This Matters
A demand gen team importing 14,200 trade show leads hit a wall: 1,847 rows contained 'United States' variations that didn't match the org's picklist ('US'). The admin spent 4 hours writing nested SUBSTITUTE formulas in Excel, only to have the second attempt fail because 312 rows had 'Quebec' instead of 'QC'. The 48-hour delay meant the sales team missed the post-event follow-up window, tanking the event's ROI by 40%. Stop fighting Excel VLOOKUPs—normalize picklists structurally before touching Data Loader.
Why Excel & Python Fail Here
The all-or-nothing rejection is the killer: Data Import Wizard fails the ENTIRE batch if even one row has an invalid picklist value. You get a generic error after a 10-minute upload, with no row-level detail. The fix requires downloading the error file, fixing rows, re-uploading—a 30-minute cycle per attempt. With 50+ country variations and 60+ state/province abbreviations, manual mapping in Excel requires nested VLOOKUPs across reference tables that break on edge cases.
| Criterion | Excel / Sheets | Python / Cloud | DataPrep (This Page) |
|---|---|---|---|
| Max Rows | ~150K (crashes) | RAM-limited | 1M+ via Wasm |
| Setup Time | Manual formulas | 30–60 min env setup | 0 seconds |
| Data Privacy | Local but fragile | Uploads to cloud | 100% in-browser |
| Cost | License fees | EC2 / SaaS $49+/mo | Free forever |
Step-by-Step Solution
We will use the CSV Schema Validator to execute this entirely in your browser.
- 1
Upload your lead CSV and select picklist columns
Drag and drop the file. Select which columns contain Country and State/Province data. The tool auto-detects common variations: 'Country', 'Country Code', 'Billing Country', 'Ship To Country'.
- 2
Normalize against ISO 3166-1 and Salesforce picklists
The tool maps 200+ country variations to ISO 3166-1 alpha-2 codes (US, GB, DE, FR) and 150+ state/province variations to Salesforce's standard abbreviations (CA, NY, QC, ON). A diff report shows every change made.
- 3
Download validated CSV + rejection risk report
The output file passes Salesforce picklist validation. A risk report flags any remaining rows that couldn't be auto-mapped (e.g., 'Kosovo' if your org doesn't have it configured) for manual review.
Privacy Guarantee: Zero Bytes Uploaded
All processing happens in your browser via WebAssembly. Your data never touches a server. Try it: disconnect your Wi-Fi right now—the tool will still work.
Common Errors & Fixes
| Error / Symptom | Root Cause | Fix |
|---|---|---|
Data Import Wizard: 'Batch failed — invalid picklist value in row X' | Country 'United States' doesn't match picklist value 'US' | Normalize all country values to ISO 3166-1 alpha-2 before import |
Second import fails after fixing countries | State/Province values also need normalization ('Quebec' → 'QC', 'California' → 'CA') | Normalize BOTH Country and State columns in the same pass |
Frequently Asked Questions
Does this work with custom picklist values my admin added?
Should I use Data Import Wizard or Data Loader?
Ready to clean your data?
100% local processing · Zero uploads · Blazing fast
Trusted by 2,400+ data teams · 18M+ rows processed monthly