The Microsoft Dynamics CRM Team Blog
News and views from the Microsoft Dynamics CRM Team
Have you ever wanted to edit multiple Microsoft Dynamics CRM records from within Microsoft Excel, without having to open each record’s individual form? With this new Dynamics CRM 2011 feature, it is possible to update Dynamics CRM records using Microsoft Excel (2003, 2007, or 2010). All you have to do is export data from any grid, save it to disk, edit it in Microsoft Excel, and re-import the file using the Import Data Wizard (the file will get auto-mapped). Within minutes your records will be updated by a system job running in the background. You can also add new rows in the downloaded Excel sheet and we’ll go ahead and create those records for you.
Updates are carried out on the fields that were changed. In case a record was updated in Dynamics CRM between the time data was exported and re-imported, we skip over your changes to that record so that invalid updates aren’t carried out.
Let’s have a brief walkthrough before diving into more detail.
1. Export the data from whichever grid you want.
2. Tick the “Make this data available for re-importing by including required column headings” checkbox.
3. Open the downloaded file in Microsoft Excel. Edit the records as appropriate, and add new records if required. Save the file to disk.
4. Upload this file into the “Import Data Wizard”.
5. On clicking next, CRM will auto-map the file and save you the hassle of having to map source file columns to CRM fields.
6. Submit the import job and wait for it to complete.
7. And, you’re done!
8. If you look at the successes column in the imports grid, only two records were processed. Since the other records were not edited, they are not picked up by the import job.
To assist you when entering values in a field of type option set, all option set values for that field are exported with the data. You can see the list of values in a dropdown associated with these cells. Whenever a value is changed in such a cell, it gets validated against the list of possible values.
If status/status reason values are updated, a SetStateStatus SDK request is made for that record. However, this call might fail if there is some business logic associated with change of status for that record type. (E.g. Opportunity record type has special logic and requires WinOpportunity / LoseOpportunity SDK requests to change status)
We search for a User or Team having the same Display Name as specified by you in the sheet, and assign the record to that user.
Money fields get exported in a special way to make the sheet re-importable. The currency symbol is removed from the value and instead exported as a separate column (the “Currency” column). To change the currency of that record, you can change the value in this “Currency” column.
Any changes done to cells of type Text (as defined in Dynamics CRM) are validated for minimum and maximum text length. You can see this information in the tooltip associated with each cell.
Numbers and Dates are shown in Excel with the number/date formatting that is defined in your Operating System. It does not matter if you are using a different format in Dynamics CRM.
All changes are checked for the range of numbers/dates allowed in that field.
As always, we’d love to hear your feedback!
Great post, thanks for sharing, our customers love the fact that they can bulk edit records in Excel and then import them back to CRM, it can be a huge time saver to manipulate data in Excel instead of CRM. However, you mention that if a record was updated in CRM after the records were exported to Excel then the update will fail. I understand this mechanism helps to prevent inconspicuously overwriting data, but in many cases we would like to overwrite the CRM value regardless and it is hard to edit the spreadsheet fast enough to re-import it before anyone changes anything. I suggest there should be an option for overwriting even if the record changed in CRM since it was exported to Excel.
Firstly I want to say how impressed I am with CRM 2011. Keep up the great work. In regards to the importing/re-importing feature I have a few observations:-
1. Mandatory fields – One of the most frustrating 'features' is that the Business Required fields is ignored. It is next to impossible to provide importing to business users without writing our own plugin code on every entity to check the mandatory-ness of fields – this is something we shouldn't need to do – can you introduce a registry key, system setting or something where this can be enabled by default and large implementations can turn it off for performance if they don’t care about data quality.
2. Owner changing – when re-importing if the owner field wasn't exported by the user then on reimporting all records will be set to the user in the dialog on the import tool – not so obvious.
3. Lookup and uniqueness – If you put the name of the related record in a lookup field and it isn't unique the record isn't imported. When importing from scratch we've ended up doing some macros in Excel to get the GUIDs from the database on lookup fields to ensure uniqueness.
4. TFS Excel add-in – I'd like to hope that in the future we could hope for an Excel interface along the lines of TFS – this is so powerful and such a great extension that you can almost live in Excel when working in TFS.
5. Transactional – Would like the ability to import in an all or nothing state. We have records being created via Plugins and workflows when a record is created and having a partially failed import is a headache because if we want to roll it back we have to go hunting for associated records. All or nothing flag on the import screen would be useful.
Do more like that very nice and informative blog
I'm using Microsoft Dynamics CRM online. When I click Export to Excel I have the four radio buttons (static worksheet with records from this page, static worksheet with records form all pages, dynamic PivotTable and dynamic worksheet). There is no "Make this data available for re-importing by including required column headings" checkbox in my download dialogue.
Can you confirm what version of Dynamics this option is available in and/or how to enable it? Everything else looks the same as shown in your captures. Thanks.