How to Import Records in Dynamics 365 CRM Solutions

When you need to add or change a large number of leads, contacts, or other records in Dynamics 365 CRM, you can save time by using the import functionality. This feature is often used by, for instance, salespeople just getting back from an in-person event with a spreadsheet full of new prospects or contacts.

In this article, I’ll explain how to use import for several different import scenarios, and I’ll also help you handle some of the most common problems.

• Importing New Records into Dynamics 365
• Common Issues with Importing Data
  • File Format Errors
  • Problems With Lookups (Including Duplicate References)
  • Problems With Option Sets
• Updating Records with Dynamics 365 Import
• Steps to Import Data with Non-Typical Entity Lookups
• Import with Office 365 (Microsoft 365) Integration

Note: There are now 5 different Dynamics 365 apps that serve various CRM needs. In some contexts, you may also see those products referred to as “Customer Engagement” or “CE” apps. Importing records works the same no matter which of those apps you’re using.

Importing New Records into Dynamics 365

When importing new records into Dynamics 365, there are two options for the source file:

A. Produce your own file:

Generally, this would be a CSV (or other delimited) file with your own headers. In an ideal world, you will want to make sure the column headings match your field names in Dynamics 365. When walking through the import process, Dynamics 365 will try to automatically map fields based on this information. Some up-front work on this will save you time when manually mapping these fields.

B. Use an import template:

In Dynamics 365, you can download an import template from: Settings > Data Management > Templates for Data Import.

You might have some trouble creating the file here if you have two columns in your table sharing the same name.  In this case you will have to rename a column — that is to say, all the column names must be unique.

Another important tip is that, when using one of these files, do not remove the hidden columns A, B, or C. The system uses the data from those columns to know what records you are importing into the system. For ease of use, you may want to delete any columns representative of the fields for this entity in Dynamics 365 to make the file easier to use. Just leave columns A, B, and C alone!

Importing Data

Once your import file is complete, navigate to: Settings > Data Management > Imports > Import Data. This will launch a wizard-esque interface, allowing you to upload your data file, define field mappings, and then import.

If you are familiar with Power Platform, you can go to admin.powerplatform.com in your favourite supported browser, select your environment, then go to settings at the top. As time goes on Microsoft will move more and more features from the legacy settings to here.

The import feature may take anywhere from minutes to hours to run, depending on the number of rows, number of fields, and types of fields. It will provide you with the total number of records, the number of successes, the number of partially imported records, and the number of failures.

To further diagnose failures, the system provides detailed insight into why any individual row failed. If/when an issue is encountered, Dynamics 365 will fail on that row, and proceed to the next, allowing the import process to continue, and not immediately fail the remainder of the records in the import file.

Did you get some failures? A great feature is that it will allow you to export the failed rows in an import-friendly format so that you can fix them and import them again. Use the Export Error Rows button. Typical reasons you’ll see for failures are missing lookups, duplicate names in the primary column, or perhaps unexpected data (too many characters in a field).

Common Issues with Importing Data

Below are three of the most common issues experienced during the data import process.

File Format Errors

One type of problem you may encounter when importing records to Dynamics 365 CRM has to do with file format. This may happen if you have a csv file that has special characters in the file like commas.

Because the import file uses commas to determine where one column starts and the other ends, you will have to add quotation marks around the commas that are used to separate the fields.

For instance, you might have a field storing the address as follows:

100 N.Street, Hope, BC

Because of the commas, the import function would see this a 3 columns, not just 1. The solution would be to put it in double quotation marks, making it look like just 1 column to the D365 import process:

“100 N.Street, Hope, BC”

Error: “Import file does not have the format”

An error you might come across is as follows:  “The import file does not have the format that Dynamics 365 uses to map the data to Dynamics 365 fields. It is possible some hidden sheets that the system uses have been modified or corrupted. Try again with a file exported from Dynamics 365.”

Usually, if you have an error like this, you can solve it by saving the file as a CSV (UT8), because CSV files are only allowed one sheet. Alternatively, you could save the file with a different name, unhide the sheets, and remove the ones that are not applicable to your import.

If that doesn’t work, your problem may be caused by a more difficult issue, in which case you should reach out to your system administrator or Dynamics 365 CRM Partner.

Problems With Lookups (Including Duplicate References)

If using standard mappings, the import process in Dynamics 365 will attempt to resolve lookup fields referenced in the file based on the entity’s name field. This will work anywhere except for where the data is duplicated, such as Accounts with the same name, or if the data does not exist in Dynamics 365.

If you do have records with the same name, but these are not true duplicates, the import process has a method where you can resolve these lookups against some other distinct record ID. I would say it is also not uncommon to create fields specifically for this.

The above error (“the lookup reference could not be resolved”) indicates the import is looking for a value referenced in the import file, but cannot find it in D365.

The above error (“a duplicate lookup reference was found”) indicates that while looking for a value in your import file within D365 it has found more than one match and it can not decide which to use. The value in the lookup table needs to be distinct (unique) in order for the import process to find and insert a match.

Problems With Option Sets

Any value within your source file must match the option set values in Dynamics 365. If using an import template produced from Dynamics 365, this business logic is built in. If using a CSV file, the values in the file must align. If the values are different, you will need to map the values to a corresponding value in Dynamics 365 when defining your field mappings.

Deleting Imported Data

By the way, there is a handy feature that will allow you to delete that data you just imported if things did not go as you planned.

Best Practices When Importing New Records

When importing data, you should first take a back-up of your Dynamics 365 database prior to importing, just in case any unanticipated data issues arise.

You will also want to consider any automations which may exist for your data. These may be Business Rules, Processes/Workflows, Power Automate flows, or other functionality driven by custom code.

Business Rules: If you are driving field values based on other field values but have not imported all data in alignment with these Business Rules, your users will likely receive a message opening any record as the Business Rule triggers for the proper field update. Users would then be prompted to save the record.

Processes/Workflows: You may have Processes/Workflows which trigger on the creation of new records. You may want to take this into consideration, inactivating Processes/Workflows which you do not want to trigger when you import.

Updating Records with Dynamics 365 Import

You can also update records through an import within Dynamics 365. Fortunately, Microsoft has provided this functionality for quite some time. However, using this feature requires the data to be in a very specific format.

Step 1: Data Extraction

For the first step, it is vital the data is exported from the Dynamics 365 application using the Excel export features available. If any of your Views (System or Personal) include the column(s) of data needed for the update, you can navigate to this View and select the Export to Excel option.

If your Views do not contain the column(s) of data needed for the update, the best option is using Advanced Find for this, as you can easily add the column(s) of data needed. Your other option is to create a system-level View, but this may require some additional coordination for development and deployment.

Exporting Data from a View:

Exporting Data from Advanced Find:

Step 2: Data Update in Excel

Once your data is in Excel, you can update the data as needed. Keep in mind, you need to make sure data alignment exists for any option-sets, and/or lookup values in the system. Once you have the data updates needed, save this file.

It should be noted, in this Excel file, columns A, B, and C should not be updated. These columns contain critical information needed for re-importing the data. Make sure this data does not become misaligned with the rest of the dataset in the Excel file. These columns are hidden by default.

Step 3: Import of Data

Prior to importing data, I would recommend securing a back-up of your Dynamics 365 instance, just in case the import has unexpected results. Within Dynamics 365, there are several ways to access the import process. I generally navigate to: Settings > Data Management > Imports. Select Import Data.

Proceed through the wizard. When you get to the window “Review Settings and Import Data” (shown below), it is critical the window displays the following message: “This action will update existing records, and if required, create new records.”

If this message is not displayed, stop: chances are high that new records will be created, and the update will not occur. Otherwise, continue through the rest of the import wizard.

Importing Records with Non-Typical Entity Lookups

In Microsoft Dynamics 365, there may be times when you need to import child records in a one-to-many (1:N) relationship, which include a relationship to a parent record — but instead of having access to the Primary Field used by Dynamics 365 for the parent record, you have access to some other identifier such as an Account Number or ID.

You might come across this challenge if you are importing a contact and you want to also link the contact to its company at the same time, but you have the company’s ERP account number not its name like CRM is expecting.

The example I’ll provide works when importing child records with a parent association. The Primary Field in Dynamics 365 is not available, but a different distinct parent identifier is available.

First, we need to understand what Dynamics 365 uses for the Primary Field for the import, as the import will try to resolve against this for the parent entity. For this, we will be looking at the relationship between an Account and its related Contacts. From a Contact record, this relationship is established by the Account Name field.

In the screenshot below, you can see how this 1:N relationship between an Account and its Contacts is made:

The data displayed in the Account Name field on the Contact is coming from the corresponding Primary Field for the Account, which is the Account Name field.

To determine what data is expected from the system for this, open a solution (shown below) containing this information; it may be easiest to use the default solution.

• Select the entity representing the parent record, in this case, Account.
• Next, select the Primary Field tab. This tab displays the field the import process will try to resolve against.

Using this information, if we have an Account with Account Name ACME, when we try to import Contact records, and make it associated to an Account, the import process expects ACME to be in the Contact file for Company Name field.

When this Account Name is not available, instead you have some other Account Number or ID, such as ACME001. With this distinct value in both our Contact file and already existing on the Account record, we can still successfully import the Contacts with this Account relationship – we just need to go through some extra steps.

For this, we have ACME set-up as an account, with their Account Number/ID: ACME001 stored in the Account Number field. Our Contact file contains the following data:

 

Steps to Import Data with Non-Typical Entity Lookups

Step 1: To import this data, navigate to: Settings > Data Management > Imports, and click the Import Data button.

Step 2: On the Upload Data File window, select your Contact file and click Next. On the Review File Upload Summary window, make any adjustments and click the Next button.

Step 3: On the Select Data Map window, map an appropriate selection. I usually use System Data Maps – Default (Automatic Mapping). Make an appropriate select and click the Next button.

Step 4: On the Map Record Type, select the appropriate record. In our scenario, this would be Contact. Make an appropriate select and click the Next button.

Step 5: This is where we depart from the normal process. On the Map Fields window, we need to take a step to map our Account ID (in the Company Name field in our file) to the corresponding Account field: Account Number. For this, click the icon adjacent to the drop down for the standard mapping:

On Account, use the lookup to open the Select Fields window. Unselect all fields, and then select your Account Number/ID field, in our case: Account Number. With this selected, click OK to return to the previous window. Click OK to close. Back on the Map Fields window, click the Next button.

Step 6: Proceed through the rest of the import wizard windows to finish importing the Contacts, with their Account relationships.

While this walkthrough was for a Contact, and the association to Account, this same methodology can be extended to other scenarios in the system.

Note: Although the Dynamics 365 import tool is incredibly easy to use, always secure a back-up of your data prior to importing.

Note: Import with Office 365 (Microsoft 365) Integration

If you have Office365 Online there is a quick way you can update your data leveraging the above tools with an easy interface.

• Go to the Table/Data and select the view for which you would like to update the data. You can refine your view here using filters or the keyword filter.
• Next in the top right select Export to Excel and choose Excel Online.

• Excel Online will open with a sheet that looks very similar to your D365 View. I will in this case update the email from test@here.com to test89@email.com then click Save. (Make sure to exit the cells you edited, or your data will not get updated and you will receive an error about a duplicate record existing.)

• At this point your data is being reimported to D365. If you would like you can click on track progress to see the progress.

Read Microsoft’s import instructions here.

Importing data is one of those common tasks in CRM that can be quick if and error-free if your people are trained well and your system is configured for your needs. If you have any questions about training, support, implementations, or integrations for Dynamics 365, please connect with us. We’d love to talk.