T#6 Data Import++ : Extending Import of data in CRM

Data Import++ is an XrmToolBox tool that allows you to Create - Upsert - Update and Delete records based on an Excel data import file.

The aim of this post is describing the steps for using the tool and exploring all of its functionalities.

1. Open XrmToolBox then open the tool

2. Pick the target entity where you would like your data to be imported to

3. Upon picking your entity, your CRM entity fields will be loaded by the tool

4. Click on BROWSE EXCEL and choose your Excel File.

5. Your Excel Columns will appear as Rows inside the grid. You can pick for each one the corresponding CRM field that it has to be mapped to.

Note that you can map many Excel Columns to the same lookup field in CRM.

In the previous image, the "Account_Name" and "Account Country" are mapped to the same "parentcustomerid" field.

In that example, we are choosing to do an UPSERT of contacts based on their CRM ID.

In case the contactid is found in CRM, the contact will be Updated with the excel data line. Otherwise the excel line will be created as a new record in CRM.

In another example you could have imagined importing contacts and choosing as a Key the First Name + Last Name + Email Address. The tool will fetch records matching these criteria and Update them or Create the record if no match is found.

6. Once the CRM fields have been chosen, click on PROCESS FIELDS.

This action will generate new Columns in your datagrid in case you have picked any of the following field types: Lookup - Boolean.

a. BOOLEAN field type:

For each of your Boolean field types you will be able to modify the True and the False values with the ones existing in your Excel File. To do so, just click on the gray cell and modify the string. In the previous example, the Boolean field is "Private?". We will modify "Yes" to "Private" and "No" to "Not Private" as these are the values present in our Excel File.

b. LOOKUP field type:

In order to process correctly your lookup fields, pick the entities that your lookup fields points to.

Then click again on "PROCESS FIELDS". That will get the entity fields of each one of the picked entities for the lookups.

You can now fill the Column "[Lookup] Field Name" with the respective fields.

The tool will search for the accounts with "name" and "address1_country" matching with the values present in the Excel lines being imported.

You can choose the behavior that you want in case more than 2 records are found matching the lookup criteria: "MAP THE FIRST FOUND RECORD TO THE LOOKUP" - "SKIP RECORD WITHOUT IMPORTING IT AT ALL" or even "IMPORT CRM RECORD WITH CLEARED LOOKUP". These are configured from the menu on the left under "Lookup field found > 1 record".

The "Filter on key found > 1 record" stands for finding more than 1 CRM record to Update / Upsert / Delete based on the chosen key(s). In this case you can choose to "DO ACTION FOR ALL" or to "IGNORE EXCEL LINE" without Updating/Upserting/Deleting.

If you have chosen fields of type OptionSet, you can choose to have Labels or Values (numbers) inside your Excel data corresponding to these fields. In case of choosing Labels, the tool will map the labels to the corresponding OptionSet values in CRM without taking into consideration Upper/Lower characters. In case you choose Values make sure having the correct integer number (without any spaces between numbers).

This configuration is picked under "OptionSet field format" on the menu to the left.

Make sure to choose the right CRM Action (Create - Update - Upsert - Delete) then click on IMPORT CRM.

This will trigger the chosen action and will import your excel data.

During the import, you can change the Filter Logs in order to show: SUCCESS - WARNINGS - ERRORS - or ALL the logs.

Changing the Logs Filter will automatically refresh the logs view. Otherwise, you can click on the small Refresh icon next to "Copy" in order to refresh the current import progress in the Logs. You can also click on "Copy" to Copy to your clipboard the Logs and then paste them to your preferred logs location (if needed).

In the tool's latest version, in the footer you can see the following information:

- Excel Rows Count: The number of Rows that will be taken into consideration during the import (excluding the first line : columns names)

- Success: Number of lines successfully created/updated/deleted.

- Error: Number of lines ignored or having generated an error with unsuccessful action.

- Created: Number of lines imported as a Create action in CRM.

- Updated: Number of lines imported as an Update action in CRM.

- Deleted: Number of lines imported as a Delete action in CRM.

Some of the tool's actual limitations:

- Lookup fields: You can only map String or Guid field types from within the related entity.

- OptionSet fields: If different optionset fields are present in your excel file, all of them should be of the same data type: Optionset VALUES or Optionset LABELS.

- Create CRM Action: You cannot force a guid even if mapping a guid excel column to the record's guid field. The CRM will generate a new Guid for the record.

Project on github: https://github.com/JosephMerhej/XTBPlugins.DataImport

Project on Nuget .org : https://www.nuget.org/packages/JosephMerhej.DataImport/

Hope this tool helps you extend the CRM limitations! Do not hesitate to rate it on XrmToolBox and to comment on this post in case you have suggestions to improve it. ;)

You can also directly share the post by clicking on the bottom left (social media icons).