Import data or get data from Excel

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.

Export data and import it in Dynamics 365 Microsoft Office 365, Microsoft Dynamics 365 image

The other place is on the entities themselves.

Export Data from the accounts entity

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.

Export data and import it in Dynamics 365 Microsoft Office 365, Microsoft Dynamics 365 image 2

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.

Import data or Get data from excel

All the options are fairly similar.

You will get to the dialogue that lets you upload a csv file that was exported earlier.

Export data and import it in Dynamics 365 Microsoft Office 365, Microsoft Dynamics 365 image 4

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.

Export data and import it in Dynamics 365 Microsoft Office 365, Microsoft Dynamics 365 image 5

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.

Export data and import it in Dynamics 365 Microsoft Office 365, Microsoft Dynamics 365 image 6

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.

Export data and import it in Dynamics 365 Microsoft Office 365, Microsoft Dynamics 365 image 7

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:

Export data and import it in Dynamics 365 Microsoft Office 365, Microsoft Dynamics 365 image 7

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,690970003

In 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)
Avatar for Pieter Veenstra

By Pieter Veenstra

Business Applications Microsoft MVP working as a Principal Architect at HybrIT Services Ltd. You can contact me using contact@sharepains.com

2 thoughts on “Export data and import it in Dynamics 365”
  1. 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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from SharePains by Microsoft MVP Pieter Veenstra

Subscribe now to keep reading and get access to the full archive.

Continue Reading