How to import a CSV into Salesforce without wrecking your data
The short answer: To import a CSV into Salesforce safely, export a backup of the records you will touch, rehearse the load in a sandbox, fix the picklists, dates and required fields in the file, and load a small batch first. Use the Data Import Wizard for loads under 50,000 records on supported objects, and Data Loader for anything bigger. Updates need record Ids or an external ID.
The file arrives with the subject line “quick one”. It has 4,000 rows, a column called “Status” that is nearly your picklist, and dates that someone typed by hand. The sender is already on to the next thing. You are the person who finds out, on Monday, that 600 accounts now say “Prospct”.
Salesforce already ships two import tools that are good enough for most of this. The trouble is rarely the tool. It is what went into the file, and whether you could get back out.
Which tool should you use to import a CSV into Salesforce?
Use the Data Import Wizard for loads under 50,000 records on the objects it supports, and Data Loader when the load is bigger, the object isn’t supported, or the load repeats. Both are Salesforce’s own. Salesforce’s Help pages draw the line this way.
| Data Import Wizard | Data Loader | |
|---|---|---|
| Where it runs | In Setup, in the browser | Client application you install |
| Records | Up to 50,000 at a time | Up to 150,000,000 per CSV, per the “When to Use Data Loader” page |
| Objects | Accounts, contacts, leads, solutions, campaign members, person accounts, custom objects | Objects the wizard doesn’t support |
| Operations | Add, update, add and update | Insert, update, upsert, delete, hard delete, export |
| Good at | Duplicate prevention by account name and site, or contact or lead email | Complex mappings, scheduled loads |
Two cautions. Opportunities, cases, products and a few other objects aren’t on the wizard’s list, so check the list in your org (Setup, search Data Import Wizard in Quick Find). And Salesforce’s own articles don’t agree on where the wizard should hand over: one mass-update article suggests Data Loader from roughly 50,000 to 100,000 records and the Bulk API for hundreds of thousands, while the “When to Use” page gives the 150 million figure. Read the figure as a ceiling, not a target.
Data Loader is available in Enterprise, Performance, Unlimited and Developer editions. Features vary by edition and license, so check what your org has.
What should you check in the file before you load it?
Check three things: picklist values, dates, and required fields, because those are where most loads fail or, worse, succeed wrongly.
Picklists. Salesforce’s import FAQ says the wizards accept values that aren’t already in a picklist and warn before adding them. That is how a field ends up with “Prospct” as a permanent option. A restricted picklist behaves differently and rejects the value with an error such as INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST. Before loading, get a distinct list of each picklist column and compare it to the field’s active values in Setup. Use API names where the field uses them.
Dates. Salesforce’s Help explains that Data Loader converts dates in the CSV to GMT, so a machine in another time zone, or one that observes daylight saving time, can land a date a day off. Check whether the target is a Date or a Date/Time field, and spot-check a handful of rows after the first batch.
Required fields and rules. A required field left empty, a validation rule that evaluates to true, or a duplicate value in a unique field will fail rows. That is the good outcome, because you see it. Run your validation rules in your head against the file before you start.
One more, for updates: blank cells don’t clear existing values in the wizard, per Salesforce’s update guide. If you need to blank a field, plan a separate step for that.
How do you update existing records without creating duplicates?
Match on the Salesforce record Id, or on an external ID field, and never on a name you retyped. Salesforce recommends matching on the Id because it is unique and guarantees the right record is updated.
For accounts and contacts, the wizard can also match on name, site or email. Salesforce’s update guide warns that if two accounts or contacts share a name, the wrong record may be updated, and that changing the Record Owner, Account Name, Account Site or Contact Name columns in the file creates duplicates. Data Loader’s upsert matches on a column holding either an external ID or the Salesforce Id, updating when it finds a match and creating a record when it doesn’t.
Steps that hold up:
- Run a report of the records you want to change, with their Ids and the fields you will edit.
- Export it, edit only the columns you mean to change, and save as CSV.
- Match on Id (or your external ID) in the tool.
- For owner changes, use Mass Transfer Records instead, which Salesforce points to for ownership.
If the load will create new records as well as update, run duplicate checks on the result afterwards. A fresh load is the most common way duplicates arrive, which is why merging duplicate accounts safely is the sequel to this post.
Should you back up and test in a sandbox first?
Yes to both. Salesforce’s mass-update article says “Always back up your data before performing any data operation” and recommends running a small test before the full update.
A backup here means the records you are about to change, exported with their Ids and the current values of every field you will touch. A report export or a Data Loader export will do. It is the only thing that lets you restore an update.
The sandbox rehearsal is my advice, not a Salesforce rule, and it has a catch: a sandbox only tells you what production would do if the sandbox matches it. Picklist values, validation rules and automation drift. If you can’t get a recent copy, a very small batch in production is the next best rehearsal. Load 20 rows, open five of them, and read the fields as the owner would. Then load the rest.
What goes wrong, and can you undo an import?
I found no native undo for an import. What Salesforce documents is deleting what you added, and that only helps for inserts.
According to the Help FAQ on deleting imported data, an admin can use Mass Delete Records in Setup to remove imported accounts, contacts, leads or solutions, which needs the Modify All Data permission. For imported custom objects, an admin can use Data Loader to delete them. Deleted records go to the Recycle Bin unless you choose to delete them permanently, and Data Loader’s hard delete skips the Recycle Bin.
For updates there is nothing to delete. The way back is the backup you took: load the old values back by Id. This is the reason for the small first batch. A bad mapping on 20 rows is an afternoon. On 4,000 rows it is a week.
What to do Monday:
1. Write down which of the two tools your team uses, and the record count at which you switch.
2. Add a “backup export” step to whatever process you have for incoming files, with the Id column included.
3. Pick the object people load most often and list its picklist fields with their active values, so the comparison takes minutes.
Where we come in
Portage, by GTM Cloud, is in early access. It is a wizard inside Salesforce that checks every row of a CSV or Excel file against your live field rules and, for updates, shows the old and new value side by side before anything saves. It loads files a person brings to it, so it isn’t an integration, and updates need the Id column. If a preview step is what your process lacks, see Portage.
Questions people ask
How many records can the Data Import Wizard import at once?
Up to 50,000 at a time, according to Salesforce Help. It supports a defined set of objects and CSV files. For larger loads, or objects the wizard doesn’t support, Salesforce points to Data Loader. If you are planning something big, a data health check before the load shows what you are about to add to.
Do I need an external ID to update records?
No. You can match on the Salesforce record Id, which Salesforce recommends because it is unique. An external ID helps when the file comes from another system and doesn’t carry Salesforce Ids, and Data Loader’s upsert can match on one.
Can I import opportunities with the Data Import Wizard?
No. Salesforce Help lists opportunities, cases, products and a few other objects among those the wizard doesn’t import. Use Data Loader for them. Check the supported list in your org under Setup, Data Import Wizard, because it can change between releases.
Sources
- Data Import Wizard overview: 50,000 record limit, supported objects, CSV format.
- When to Use Data Loader: 150,000,000 record ceiling, when to prefer the wizards, editions.
- Mass update records in Salesforce: tool choice for updates, backup and small-test advice.
- Update accounts and contacts with the Data Import Wizard: matching options, blank cells, duplicate warnings.
- Insert, update, or delete data using Data Loader: operations and upsert matching.
- Why do date fields import incorrectly in Data Loader?: GMT conversion and the day-off problem.
- Can I import data into a picklist field if the values don’t match?: picklist behavior on import.
- INVALID_OR_NULL_FOR_RESTRICTED_PICKLIST with the wizard: restricted picklist errors.
- Can I delete my imported data if I make a mistake?: Mass Delete Records, Data Loader delete, Recycle Bin.