How to Clean a CSV Before Importing into Salesforce
Updated
Salesforce imports fail for predictable reasons: missing required fields, picklist mismatches, date format errors and encoding problems. Whether you're using Data Loader or the Import Wizard, cleaning the file first saves hours of debugging error logs. This guide covers the field-level preparation for Contacts, Leads and Accounts.
1. Choose your import tool
Salesforce offers two built-in options. The Import Wizard (Setup → Import) handles up to 50,000 records and is simpler but less flexible. Data Loader handles millions of records, supports all standard and custom objects, and gives you an error log for every failed row.
For either tool, the CSV must be UTF-8 encoded with a header row whose column names map to Salesforce API field names (e.g. FirstName, LastName, Email, Phone, AccountId) or field labels.
2. Include required fields and Record Type
Every Salesforce Object has required fields. For Contacts, LastName is always required. For Leads, LastName and Company are required. For Accounts, Name is required. If your org uses Record Types, you must also include a RecordTypeId or RecordType.Name column — otherwise every record gets the default Record Type.
Check your org's required fields in Setup → Object Manager → [Object] → Fields & Relationships. Custom required fields specific to your org will also need values in every row.
3. Use External IDs for upsert operations
When updating existing records, Salesforce matches by record ID (18-character Salesforce ID) or by an External ID field you've configured. External IDs let you match on your own identifiers — a legacy system ID, an ERP code, an email address marked as External ID.
If your file has an External ID column, ensure the values are clean: no trailing spaces, no duplicates, and consistent casing (External ID matching is case-insensitive for most field types but case-sensitive for some custom fields).
4. Match picklist values exactly
Picklist fields (Lead Source, Industry, Stage, Status) reject values not in the allowed set. In Setup → Object Manager → [Object] → Fields, click each picklist field to see its values. Your CSV must use the API value, not the translated label if your org uses translation workbench.
- Check for variant spellings: 'Web' vs 'Website', 'Prospect' vs 'Prospecting'.
- Multi-select picklists separate values with semicolons (;), not commas.
- Blank picklist values are allowed unless the field is required.
5. Format dates and encoding
Salesforce accepts dates as yyyy-MM-dd (recommended) or MM/dd/yyyy. DateTime fields need yyyy-MM-ddTHH:mm:ssZ (ISO 8601). Mixed formats in one column will cause row-level failures.
Save the file as UTF-8 CSV. Data Loader accepts UTF-8 with or without BOM. If you see garbled characters (José instead of José), the encoding is wrong — re-export from the source or convert the file to UTF-8 before importing.
Pre-import checklist
| Check | Why it matters |
|---|---|
| UTF-8 encoding | Accented names import correctly |
| Required fields filled (LastName, Company, etc.) | Rows aren't rejected |
| Record Type column present (if applicable) | Records go to the right Record Type |
| External ID clean for upserts | Matching works, no duplicate records |
| Picklist values match API values | No row-level errors |
| Dates in yyyy-MM-dd | No date parsing failures |
| Emails trimmed and valid | Matching and dedup work |
| No duplicate IDs in the file | No silent overwrites |
Run your file through TheSpreadFix before loading it into Data Loader — catch the errors in seconds instead of reading error logs row by row.
Frequently asked questions
Should I use Data Loader or Import Wizard?
Use Import Wizard for simple imports under 50,000 records (Contacts, Leads, Accounts, Solutions). Use Data Loader for larger volumes, custom objects, upserts by External ID, or when you need detailed error logs.
How do I avoid creating duplicate records in Salesforce?
Use the upsert operation with an External ID field. Salesforce matches on that field and updates existing records instead of creating new ones. Without an External ID, use the Import Wizard's built-in matching by email or name.