As mentioned in Part 1: Bulk Edit, this material is based off my presentation done on CRMUG: The Advanced Basics: Where DO I Go Next as a CRM Admin? In that session, we covered a few topics including out of the box data management techniques. I wanted to dedicate a few blog posts to these topics to help more new CRM administrators!
Export to Excel is an awesome feature that gives administrators the power to make many changes very quickly. Since you are potentially editing a large volume of records there is also potential for a large amount of disaster. So follow these tips to make prevent as many errors as possible.
How to Use Export for Re-import:
- Create an Advanced Find of the records you want to modify. I recommend you narrow the results to just the records you want to change and only the columns you intend to edit.
- Export Static Worksheet (all records)
- Note: If you are on CRM 2011 you will need to click an additional check box to make it available for Export for Re-Import
- Pro Tip: Save a copy of this file right after export as a backup!
- Make changes in excel
- Back in CRM, Click Import Data and browse to the file
- Submit the file
- Monitor the import progress in Settings -> Data Management -> Import
- Backups can be saved as you work with the data. This gives you a place to look if the final import is incorrect. The GUID is a hidden column so you can use this to match back later if necessary.
- Workflows and Plugins will still fire similar to a regular record update (compared to direct database changes)
- Excel formulas can be used to populate/edit the date (please read associated disadvantage before getting too excited)
- Read Only Fields can be edited
- Fields not on the form can be edited
- You need to be very careful when working with the Excel workbook
- Do not remove columns
- If you add columns for work/calculations – these must be removed before import
- Be careful with sorting – you do not want the hidden GUID to be associated with the wrong row of information. This could cause you to overwrite a valid record’s data with data from another record. This was much easier in 2011 before the new export format.
- Be careful with formulas – it is best to do all formulas in a separate column (just remember to delete column before re-import) and then copy and “Paste as values” into the column you want to update
- Limited by the CRM Export Limits (if you are editing more records than you can export then you might want to find a way where you are not the potential single source of blame/failure)
- Cannot edit completed Activities
- You can edit some inactive records for example Won/Lost Opportunities
- Always test in a non-production environment to make sure your plan works
you’ve scared me a bit that users could export, screw up and re-import bad/empty data. Is this function restricted to Admins out of the box?
How have you used Export for Re-import? Any tips or warnings I missed? Any success or horror storied to share?