Support Portal ContactGet in touch

Importing Updates into Dynamics from a Spreadsheet

   Words by CRM Consultancy

   on 04/10/2018 11:30:00

In our previous article we looked at how we can import New Data into Dynamics from a Spreadsheet Template.

Using Dynamics 365 we can also import series of Updates into CRM from a Spreadsheet already populated with the existing set of data.

So if we export the Spreadsheet from Dynamics, we can export this for re-import, and so allow us to circulate a Spreadsheet to our Users or Admin Team that can be used to make series of quick edits to the records in the Spreadsheet and then re-upload these Edits back into Dynamics.

This is a great feature for making groups of changes to Records without having to drill-down into each Record at a time.

Our first step here is building a System View or Advanced Find that contains the Fields we need from the selected Entity in Dynamics.

So if we take the ‘Investment’ Entity in our Dynamics Build below, we can use the View Editor to start building what our Spreadsheet View should look like:


Creating a New View in Dynamics

NOTE: Any Views we add to Dynamics can be created as part of a Solution or created as a Personal View – Personal Views are created more easily through the CRM Advanced Find but are more focused on the User who is creating the View – whereas Customised Views can be published from a Development Environment to a Live Environment for future use or for a good Release Cycle.  This article on the Advanced Find in Dynamics may be useful here.

Once we have our View or Advanced Find created, we can then start adding the different Fields or Columns we need in this View:


Any Columns from CRM can be added here, but, any fields we do add will form part of the resulting Spreadsheet and so we want to avoid overwhelming the User with a massive Spreadsheet if we only actually need a smaller cross-section of fields.

The other aspect to our View is providing a particular Filter – this is doubly important for an Export to Excel as otherwise we run the risk of exporting ALL the rows from CRM into our Spreadsheet and then reimporting all of these rows (effectively cleansing our entire Database for this Entity!) and this is likely more than we need.

So adding a Filter is key to limit the data involved to just what we need:


This builds our View – which we can then publish out to CRM so that all of our Users can see the new View. (which is another distinction to Personal Views produced in the Advanced Find, as these are only accessible to the current User until Shared)

We can then browse to the Entity and drop-down to this View, and in the View, select to EXPORT TO EXCEL via ‘Static Worksheet’ to take this data into Excel:


Exporting the View to Excel as a Static Worksheet


How this then appears in Excel

With our Data in Excel we can use this two-fold:

(1) Edit the existing rows to change the data we hold on these rows.

(2) Populate new rows at the end of the Spreadsheet to create new records in Dynamics

NOTE: Why does the Spreadsheet being at Column D?  This is because CRM puts several Hidden Columns in the Spreadsheet to match the Rows to the existing Records in CRM – notably the GUID ID of each Row.  It is not recommended to make these Columns visible and change the values, as you will then find the Spreadsheet difficult to re-import.

In any event, we will come to the point where we have made all the changes we want and are ready to reimport into CRM.

For this, we again use our IMPORT DATA option and upload our Spreadsheet into Dynamics:


This will start importing the Updates into Dynamics.

Similar to how we saw Imports in the first half of this article, this will log the Data Import in the Data Management Area of Dynamics:


Tracking the Import in Dynamics so see the Successful Rows vs Errors or other Failed Rows


The resulting list of Updated Records in Dynamics

NOTE: As we can see above, this has updated the Last Modified Date on ALL the records we have imported to via the Spreadsheet – this means that every record from the Spreadsheet is being updated in some fashion back into Dynamics, and so lengthy Spreadsheet may take some time and be doing more operations that needed as there is no ‘do-not-update-if-nothing-has-changed’ logic here.

This then gives us a good standard functionality way of bulk updating records in Dynamics.

Using Lookup Fields in Excel remains somewhat fiddly as we need to set the exact Name of the Record we want to associate into the Lookup field, but otherwise this approach is very useful.

However.. for full Data Migration or Cleansing, this has its limitations – essentially we are purely taking the data from Excel and updating Dynamics, and so more complex points are not possible:

(1) Setting Lookup Fields from other JOIN fields outside of the Primary Name Field – this requires a cleverer migration tool to handle the Link Entity / Join logic.

(2) Importing Updates from a 3rd Party Supplier is not possible, as the Spreadsheet must have been created from CRM and so contain those Hidden Checksum Columns

(3) Importing Multiple Entities in a single Spreadsheet – as the Import is limited to the Entity we specify and so not capable of multiple operations from a single Excel Row. (however some elements here can be implemented by combining this approach with Dynamics Workflow)

(4) Automatic data cleansing or checking against third party Webservices (such as Postcode Anywhere for Address Validation) are not going to be possible in this approach.

For this more comprehensive migration or cleansing logic we need an external Data Migration or Integration Tool or Consultancy Service – such as Scribe, Inaport or our OverTimeTask Framework as ways of uploading New or Changes to Existing Data into Dynamics from different Sources.

If you have more detailed requirements in this area that the default tools in Dynamics cannot solve, then do not hesitate to get in touch with a partner such as ourselves for more technical assistance.


In a more comprehensive Migration/Integration Tool each routine can have its own set of Data Maps and Lookup Maps that control how Columns are mapped to Fields in Dynamics – and this typically has ‘mapping types’ that allow for parsing of Names, Addresses and other ways of processing the data as well as importing it.

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

CRMCS Quick Start Guide: How To Produce a Microsoft Teams Live Event Dynamics 365 Marketing: Lead Scoring and Sales Acceptance Designing and Developing Microsoft Power Apps Portals Thank You for Attending CRMCS’ Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Thank You for Attending Our Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Webinar: Discover How CRMCS Have United Dynamics 365, SharePoint and Microsoft Teams To Create Sales Excellence Ignite your workflow by adding DocDrive365 to Office 365 The CRMCS guide to everything you need to know about integrating Teams with Dynamics 365 Saving Time By Keeping Documents In One Place TDE Database Encryption with On Premise Dynamics The Key to Successful Compliance in 2020 Part 2: Let’s get GDPR Compliant with Microsoft Power Automate Top 3 Essential Tips for Remote Working Dynamics 365 Marketing: Top 5 Best Features Dynamics Day in the Life - Puma Investments Can you use Teams to amplify collaboration in Dynamics? Part 1: Using a Scheduled Power Automate to Trigger Expiry Date Reminders The secrets of successful document collaboration in Dynamics CRMCS launches new AppSource approved DocDrive365 Dynamics Day in the Life - Moneypenny Release Management Add the App to Dynamics DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Building a New Scheduled Process using Flow
  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue

Content © CRM Consultancy.