Kevin Altman emailed jar@crmtipoftheday.com with the following question about data import in Dynamics 365:

Users want to perform updates to existing data in CRM but the export for update/re-import process is too rigid as data often changes between the export and re-import, blocking the import (I guess CRM forces you to have no changes to re-import). Are there any workarounds?

First, a few details about how the data import works. When you export to Excel, you will notice that columns A-C are hidden.

Unhide columns A-C, and you will see three columns where the header name begins with (Do not modify). If you have read tip of the day for long, you know that we like to dare greatly, so we are going to modify the do not modify columns (we also removed the tags from our mattresses).

So what are these hidden columns?

A: The GUID of the record:this is how CRM knows what record to update. If it used the record name, it would potentially hit duplicates and not know what to do. By using the unique identifier for the record, it precisely matches to the correct record. This is also how import utility and immersive Excel know when a line is an update vs. a new record to create–if there is no value in column A, it will create a new record. This is also a field that you can use to trick import to updating records that are not included in the export.

B: Row checksum: a hash that Dynamics 365 uses to see if the record has changed. That way it only updates the records that have changed, while ignoring the rest.

C: ModifiedOn: This is the important one for our example. This is the modified on date of the record in CRM. When importing, Dynamics 365 compares the date in column C with the modified on date of the record. If they are different, the import will fail for that row. This is there for a reason–if you export and wait 3 months to import, and users have subsequently made changes to the record in CRM, if it didn’t compare the dates, newer data could get overwritten by older data from the import file.

Let’s say you have a busy CRM environment where system processes and data integrations are frequently updating records. You want to do a data cleanup of your contact data. You export out your active contacts to Excel, and you have your worker bees start cleaning up the data.

If your environment is active, chances are that at least some of the records will fail to import because their modified on has changed in CRM before your data cleansers have finished their cleanup. Modified On changes if anything with the record changes, sometimes if someone saves a record without changing anything, or if someone walks in the vicinity of the record and sneezes. So even if none of the fields in the export file have changed, the record will not update because the modified on date of the record in CRM is different than the date in column C of the spreadsheet.

So how can we get around this?

Note–the following solution is not supported (as Microsoft has told you not to modify these columns) but it works and has been tested by the author of this post. He is not responsible if you mess your data up though).

So we exported our contact data out, cleaned it up, and now we want to reimport it. Here is one approach to avoid the date restriction

1. Export a second copy of the data to be imported. In my example I called my second export export2.xlsx. Unhide columns A-C.
2. To the right of the data table in the original spreadsheet,add a new column and format to the format that matches column C
3. Click in row 2 of the new column and add a vlookup formula.

   =VLOOKUP(A2,export2.xlsx!Table1[#Data],3, FALSE)

   This is saying find the value in column A in the table in spreadsheet 2 and return the value from column C. This will get us the current modified on for the record. Copy this to every line of the table in the original spreadsheet (making sure the row number references are correct).

4. Copy the values in the new column and paste (values only) to column C, overwriting the existing modified on date values.
5. Remove the new column and hide columns A-C, then save.

You should now be able to import the file and update the records.

Photo by Sam Mgrdichian on Unsplash