Blogs

Excel online and Import data tool in Microsoft Dynamics 365

Inkey, August 1, 2018 5962 Views

Today in this blog, I will explain how the Excel Online and the Import data tool, the out-of-the-box features of the Microsoft Dynamics 365 are helpful and in what scenarios they can be used.

Import Data

This tool offers the feature to create or update single or multiple records in Microsoft Dynamics 365. If you need to insert or import data in bulk or need to update many fields of the record(s) then, the Import Data tool is highly recommended.

While inserting / updating the records using this tool, you need to keep certain points in mind for successfully completing the process.

1. If there is any lookup field in your import sheet then, make sure that you map the GUID of the lookup field value to the lookup field in the import data wizard.

For example:-

In the Account entity, there is a lookup field named “Parent Account”. And suppose that, there are two accounts with the same name, for example: “ABC”. Now, if you try to import account records with one of the records having “Parent Account” as “ABC” then, you will get an error as below at import history):-

Import utility has thrown this error because, while setting the “Parent Account” as “ABC”, it found two Accounts with the same name “ABC”. And, as not able to decide which account is to be set, it just thrown an error “A duplicate lookup reference was found” for that particular record and continued inserting the next records in the import sheet.

Now, to solve this, you need to add another column (for example: “Parent Account GUID”) in the import sheet with the value as GUID of the “Parent Account” and then map this field to the “Parent Account” lookup in the import data wizard as shown below:-

This time, the data will be imported successfully with no errors.

Note:- You can also solve this error by mapping any unique field instead of the GUID.

For example:- If you have a unique custom field on Account entity named “ID Number” then, you can just add this column to your import sheet and map this field to the “Parent Account (Lookup)” by selecting the field “ID Number” in the “Lookup reference for field ID Number” pop-up window as shown in below image:-

2. By default, the import data tool will map the lookup attribute with the system’s “Name” field. You can change it if required.

3. If the source file is exported from the CRM then, the import wizard will automatically map the fields. But, if the source file is created manually then, save that file as a CSV format file and then map all fields (having column name different from CRM field name) manually.

4. If any column name in your import sheet is not same as the CRM entity field name to which you are importing the data then, first you need to save your sheet as a CSV format file and then import the data. And, in the import data wizard, you need to map that column to the CRM field manually. Because, the import data tool maps only the columns with the same name as in the CRM by self.

5. Fields which are mandatory on the entity, needs to be mapped while importing the data. Otherwise, the import data tool will throw an error.

Please note:- This is an exceptional behaviour, i.e., some times for some entities, the required fields are not required to be mapped while importing the data. But, as this behaviour is not stable. It is recommended to always map the required fields while importing the data.

6. You can also save your mapping in the import wizard, if you are going to use the same mapping frequently. This will reduce the time for applying same mapping again.

Excel Online

You can use the Excel Online to update the existing records in the CRM, if the update details are not complicated.

For example:- If you are updating a lookup column then, the Excel online doesn’t provide a way to map the GUID of the lookup value. Thus, if duplicate value error is thrown for the lookup field (as explained in the top of this article) then, there is no way to solve it in Excel Online. So, choose the Import Data tool in this case.

If you are updating any record with no changes (i.e., all field values are same as the existing) then, the CRM will not fire any update request for that record. Thus, it improves the performance.

In the Excel Online sheet, you can delete the records from the online sheet, which you don’t want to update.
Please know, it will not be deleted from the CRM.

I hope this helps you!!

Happy CRMing.

---

 

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator