Let us understand a few points about the Import Data feature with a simple example –
Say for example we have exported the following 4 records.
The exported file will have the view name along with the timestamp.
The exported file –
It has 3 hidden columns that allow for re-import of these records possible.
1st column contains the GUID of the record, the second contains the Row Checksum which it uses to make sure to update only those records or rows which are changed, and the 3rd column is Modified On which it uses to track if the record has been modified since it was exported.
Now let us update one of the records we exported inside CRM.
On importing the same file,
as expected we get 1 failure.
Error message – “You cannot import data to this record because the record was updated in Microsoft Dynamics 365 after it was exported” for the same record which we updated inside the app.
Now let us update one of the records (which has a possible duplicate) and re-import the file.
During import, we have let the Allow Duplicates settings be default i.e. No.
This time also apart from the previous error we get one partial failure.
The error message says – “A record was not created or updated because a duplicate of the current record already exists.“
This is because the record that was modified and imported was having another record having the same email id, triggering our duplicate detection rules. Also, we had specified Allow Duplicates as No while importing the file.
This time let us set Allow Duplicates as Yes and try the import with the same file.
As expected there was no partial error this time.
And the record got updated this time.
Another point to remember is if we export and import the file without any changes, or for the rows with no modification, it will not perform any action, i.e. trigger a create/update for any of the records.
For tracking the changes, as we mentioned earlier it uses the Row Checksum column’s hash value.
Now let’s update the GUID of the record in the 1st row (changed b to c in the end) and the modified date (changed to 55 from 53 minute part) for the 4th row and see the results.
As expected both these records failed – 1st one where we changed the GUID with the error message – “The record could not be updated because the original record no longer exists in Microsoft Dynamics 365” and the 4th row with the error that “You cannot import data to this record because the record was updated in Microsoft Dynamics 365 after it was exported” as we had changed the Modified Date in the imported excel file.
Lastly, let us restore the original value of those 2 fields of the hidden columns and try the import to see the results.
As expected we do not get any error for those 2 rows as we had restored the original value.
- For .zip files 32 MB is the max size limit for other file types i.e. xlsx, csv, txt and xml it is 8 MB.
- Microsoft recommends import to be limited to 20K rows or fewer.
Just in case we are interested to see how the data validation works especially for the option set field in the exported excel file from CRM.
Select the field in excel and open the Data Validation from the Data tab, which refers to the hiddenSheet.
To see the hiddenSheet, right-click the worksheet and select the View Code option.
It opens the VBA window, where we can select the hiddenDataSheet object and change its Visible property to -1-xlsSheetVisible.
Back in our excel, we can see the hiddenSheet visible and the values of our option-set fields.
Hope it helps..