The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Leverage Microsoft Excel to edit records

Leverage Microsoft Excel to edit records

  • Comments 4

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.

image

2. Tick the “Make this data available for re-importing by including required column headings” checkbox.

image

3. Open the downloaded file in Microsoft Excel. Edit the records as appropriate, and add new records if required. Save the file to disk.

image

4. Upload this file into the “Import Data Wizard”.

image

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.

image

6. Submit the import job and wait for it to complete.

image

7. And, you’re done!

image

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.

image

What all can be done?

  • Modify the value of any updatable field of a record (e.g. Account Name)
  • Create new records by adding rows to the exported Excel sheet
  • Assign the record to another user or team by changing the value in the Owner column
  • Change the Status/Status Reason of a record (e.g. mark an Account Inactive)

What isn’t allowed?

  • Delete records in Dynamics CRM by deleting the corresponding row in Excel.
  • Modify values for fields that are “Not Valid For Update” (e.g. Created On field of Account record type)
  • When creating new records, add data into fields that are “Not Valid for Create”(e.g. Modified On field of Account record type)
  • Modify values in related entity columns (e.g. the “E-Mail (Primary Contact)” column in the “My Active Accounts” view)

How are specific field types handled?

Option Sets

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.

Status / Status Reason

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)

Owner

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

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.

Single/Multiple Lines of Text

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

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.

Things to remember

  • If a row is not changed in Excel, then we do not call update on that row – you need not worry about workflows getting triggered unnecessarily!
  • Full Names (Contact, User, and Lead) should be entered in your organization’s Full Name Convention Code for lookups on Contact, User, and Lead to work correctly.
  • Fields of type Party List are not supported by data import – do not update such field types using the Data Import Wizard.
  • If the grid contains multiple columns that have the same display name, then you will not be able to export data in a re-importable format.
  • If a record type contains multiple fields with the exact same display name and your exported sheet contains one of these fields, then you will not be able to re-import this file.
  • Record updates will not happen in case data is changed in a field on which you do not have the correct permissions. (in accordance with your Field Security Profile)
  • If you are using Excel 2010 and haven’t installed Service Pack 1 yet, it is highly recommended to save the exported file to disk and unprotect it instead of directly opening it from Internet Explorer.

As always, we’d love to hear your feedback!

Cheers!

Hitesh Madan

  • Hi Hitesh,

    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.

  • Hello,

    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.

    Thanks Glenn

  • Do more like that very nice and informative blog

  • Hello,

    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.

Page 1 of 1 (4 items)
Leave a Comment
  • Please add 1 and 3 and type the answer here:
  • Post