Recently I’ve been doing quite a data migrations in Dynamics 365 or the Common Data Services using the export data and import data operations.
Export Data
In this post I will look at export data and importing it in Dynamics and the many data issues that you may run into.
First things first. We need to get some data that we can import.
The Export data option can be found in two places. In entities right at the top next to the get data option.
The other place is on the entities themselves.
This will now generate a zip file with a csv file in it. The csv file will be named after the entity that you exported.
Ok, that is easy enough.
Get data from Excel (or import data)
The import option can be found in multiple places again. Earlier we already saw the option in the entity’s menu and we saw the same option in the main menu when the list of entities is shown.
The 3rd place is when you open a specific entity you can import data for that entity.
All the options are fairly similar.
You will get to the dialogue that lets you upload a csv file that was exported earlier.
If you have exported it from an environment with the same solutions installed and the same data model is used within your entity then this mapping will often go ok.
When things however are wrong you might see the yellow warning triangle as shown below or a red one if things are really going wrong.
You can now change the mappings of your fields in the csv with the fields in your Dynamics 365/Common Data Service entity.
Even if all the mappings are reported as ok, it is still wise to double check that the mappings are as you want them to be.
Once you are happy with the mapping click on the Import option.
This is where the real trouble can start. When there is something wrong with your data you will see the following overview of your data import.
Sometimes all imports will have failed sometimes part of the import will have failed.
Click on the Download import errors to get a file containing details on the error. The file zill be a zip file with a csv file in it that is named after the entity that you are trying to import. This is quite annoying as the export file uses the same file name and Excel will not let you open two spreadsheets with the same file name.
In general I rename the file with the data to something else so that I can open both errors and data at the same time.
Common issues
Time for the SharePains part of this post. When you an import is not successful you will find that the import data log will look like this:
There are a lot of different errors that you will come across. in this section I will go through the once that I found and if you find any other errors please feel free to contact me, or leave a comment below and I will add your issues too.
The value 0 of ‘…’ on record of type ‘…’ is outside the valid range.
This is one of the many Bad Request errors that you will see.
Reason: Bad Request, Header x-ms-client-request-id 5a57898e-48a9-4208-9655-34358fa8708b, Error code: 0x8004431a, Message: A validation error occurred. The value 0 of 'msdyn_travelchargetype' on record of type 'account' is outside the valid range. Accepted Values: 690970000,690970001,690970002,690970003In my case the msdyn_travelchargetype is displayed with its display text as in the export, while the import wants the numbers of my Option Set.
Now all I need to do is fix the export file and replace the empty fields with a valid code code used by my Option Set which in my case is 690970003.
… is not a valid status code on account with …
To resolve this error it is important to take note of the guid at the end of the message (620d9fca-b978-ea11-a811-000d3aba7712).
Reason: Bad Request, Header x-ms-client-request-id 052b69de-45bb-4c6a-ae7d-d7365d668858, Error code: 0x80048408, Message: 999999 is not a valid status code on account with Id 9f7f3e2b-7fd8-ea11-a813-000d3aba7712.The other clue is in the ‘status code’ . this is referring to the status code field on my accounts that I’m importing. The status code in my data have Active or Inactive specified. now the big question is why 999999?
One solution could be to remove the status code column from the data import file. When i did this I got a partially successful.
A record with matching key values already exists
This warning you can ignore. As you run multiple data imports and some records were imported before, you will find that more and more records already imported before.
Reason: Precondition Failed, Header x-ms-client-request-id 4ca30abc-cd98-4ed5-a128-0b8352653ea2, Error code: 0x80040237, Message: A record with matching key values already exists.Does not support untyped value in non-open type
This error occurs on empty columns. Removing the empty columns from the csv file makes this error disappear.
Reason: Bad Request, Header x-ms-client-request-id 0319398c-fa32-44de-9b81-0f7cbd1f1717, Error code: 0x0, Message: An error occurred while validating input parameters: Microsoft.OData.ODataException: Does not support untyped value in non-open type. at System.Web.OData.Formatter.Deserialization.DeserializationHelpers.ApplyProperty(ODataProperty property, IEdmStructuredTypeReference resourceType, Object resource, ODataDeserializerProvider deserializerProvider, ODataDeserializerContext readContext) at System.Web.OData.Formatter.Deserialization.ODataResourceDeserializer.ApplyStructuralProperties(Object resource, ODataResourceWrapper resourceWrapper, IEdmStructuredTypeReference structuredType, ODataDeserializerContext readContext) at Microsoft.Crm.Extensibility.CrmODataEntityDeserializer.ApplyStructuralProperties(Object resource, ODataResourceWrapper resourceWrapper, IEdmStructuredTypeReference structuredType, ODataDeserializerContext readContext) at System.Web.OData.Formatter.Deserialization.ODataResourceDeserializer.ReadResource(ODataResourceWrapper resourceWrapper, IEdmStructuredTypeReference structuredType, ODataDeserializerContext readContext) at System.Web.OData.Formatter.ODataMediaTypeFormatter.ReadFromStream(Type type, Stream readStream, HttpContent content, IFormatterLogger formatterLogger)
A couple of things in response to your post, Peter.
First off, you may have to go to the legacy customize the system experience to import certain data sets such as some Option Sets as they aren’t available in the Options Sets menu in the PowerApps make portal. Another issue, that I actually just found out about myself (and is considered a bug by MS) is that you will get a failure on import to D365 if you try and use a saved custom data map, even if all the mapping shows as good. You have to manually set the mapping EACH time.
Thanks for your feedback Lorne.