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:

image

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:

image

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:

image

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:

image

Exporting the View to Excel as a Static Worksheet

image

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:

image

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:

image

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

image

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.

clip_image002

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.

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.