Data Management Part 2: Export for Re-Import

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:

1. 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.
2. Export Static Worksheet (all records)

1. 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
3. Pro Tip: Save a copy of this file right after export as a backup!
4. Make changes in excel
5. Back in CRM, Click Import Data and browse to the file
6. Submit the file
7. Monitor the import progress in Settings -> Data Management -> Import

Advantages of Export for Re-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

Disadvantages of Export for Re-import:

• 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

Also note that this will not enforce Business Rule logic that is only firing on the form (if the scope is entity, this still should run). This can be an advantage or a disadvantage depending on the case.

In addition, any record that is modified after you export will not be updated by the import. This means that if an end user updates a record the data in your spreadsheet will not overwrite that data. So you do not want to export this data and then wait a few days to do the re-import. Best to do it all at once if possible. These types of errors will be shown in the Settings -> Data Management -> Imports area.

Security Note:

On the webinar we got a very interesting question:

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?

What a good question! No. This is not restricted to Admins. Looking at the out of the box "Salesperson" security role they have access to Export to Excel and Data Import for user owned records. So this means that they could potentially modify all of the data for their records in just a few clicks!

How have you used Export for Re-import? Any tips or warnings I missed? Any success or horror storied to share?