Data Import++

This XrmToolBox tool allows you to create, update, upsert, delete the rows in the table by importing an Excel file and define the key columns for the row identifier and the lookup value mapping (developed by Joseph Merhej).

In the out-of-the-box Import Data Wizard tool, updating the existing data can only be done by the GUID of the row and not possible to update based on the key column (e.g. Account Number) or composite key (e.g. Account Name + City). The lookup column can only be mapped to the primary name column or one of the columns in the related table but it is not possible to define the composite key for the lookup mapping if there is no single unique column. 

To overcome all those problems, you can use this tool to define the key columns to identify the rows to be updated and the value to be populated in the lookup column.

• Tool Information
• Documentation
• Blog Post
• Functionalities
• Process the data based on the selected Excel file (Fig. 1, Pt. 3)
• Selected the Excel file to be imported (Fig. 1, Pt. 1) (Fig. 2)
• Selected the table to be imported (Fig. 1, Pt. 5)
• Selected the column mapping (Fig. 1, Pt. 15)
• Specify the key column(s) to identify the rows to be updated/deleted (Fig. 1, Pt. 14)
• Specify the behaviour if there are multiple rows found based on the key column(s) values (Fig. 1, Pt. 7)
• Do action (Update/Delete) for all matching rows
• Ignore and skip if there is more than one row that matches the key column(s) values
• Process the columns in the mapping to show advanced options for special columns (Fig. 1, Pt. 2)
• Choice
• Specify if the choice column values are in integer value or label text (Fig. 1, Pt. 8)
• Lookup
• Specify the table name for the lookup mapping (Fig. 1, Pt. 16)
• Specify the key column(s) to identify the lookup value (Fig. 1, Pt. 17)
• Specify the behaviour if there are multiple rows found based on the lookup mapping key column(s) values (Fig. 1, Pt. 9)
• Use the first row to populate the lookup
• Skip the whole Excel row without being processed
• Import the data with an empty value for the lookup column
• Yes/No
• Specify the text values for True, False and Default values (Fig. 1, Pt. 18)
• Specify the action to be done for blank Excel cells (Fig. 1, Pt. 19)
• Clear the value of the column
• Keep the existing value of the column
• Choose the action to process the data (Fig. 1, Pt. 6)
• Upsert (Update if a matching row is found or else, create a new row)
• Create
• Update
• Delete
• View the output logs after processing (Fig. 1, Pt. 13)
• Filter the logs based on the type (Fig. 1, Pt. 10)
• Copy the logs to clipboard (Fig. 1, Pt. 11)
• Refresh the logs (Fig. 1, Pt. 12)
• Additionally, the tool can
• Reset and clear all the column mappings and the selected file (Fig. 1, Pt. 4)
• Show the number of rows in the selected Excel file and the number of success and error rows after importing (Fig. 1, Pt. 20)
• Similar Tool
• Import Data Wizard

Fig. 1 - Colour-coded Privileges with ID

Fig. 2 - Import Excel File

This is part of the #TooLDR series where I test and review the tools for Power Platform. You can check out the information for the other tools on this page.