Dataverse : Excel data import with AI-assisted mapping

Introduction

Microsoft has introduced an enhanced Import from Excel experience for Dataverse, making it easier to bring structured data into existing tables.
The new feature implements AI-assisted mapping. The process is more streamlined, intelligent, and reliable, making it easier to ingest large volumes of data while ensuring better accuracy and contextual mappings.

At the time of writing this article, this feature is still in preview:
Preview features aren’t meant for production use and might have restricted functionality.

Improvements in the new Experience

Improvements:

• Sheet selection
  Multi-sheet Excel files are now supported
  When uploading a multi-sheet Excel file you have to select the worksheet to import
  In the legacy interface you get the following error message
• Record update support
  Existing records can be updated in a table if a primary key is mapped during the import. Otherwise, records will be added as new
• Assisted mapping 
  Vector-based search to provide more contextually relevant column mapping suggestions
  Mapped : columns mapped automatically
  Possible match : a possible match has been identified for these columns based on the source column’s name or data pattern. You can accept the match or reject it. All possible matches must be resolved to continue the import process.
  Unmapped : no possible match has been identified for these columns. You can skip the mapping or map the column to the field of your choice
• Notifications: Real-time feedback on import status, including success, in-progress, and error states
• Performance improvements: Asynchronous ingestion offers better reliability and speed for large files

How to Enable the New Import from Excel

1. Connect to Power Platform Admin Center
2. Select your environment
3. Go to Settings > Product > Features
   
    
4. Enable the feature Import Excel to existing table with assisted mapping

Import data

In the example below I use a file 'contacts to import.xlsx' to import contacts.
Procedure to import data:

1. Open Power Apps Portal and select your environment
2. Select the table into which you want to import data
3. After selecting the import option, you can see the option Import data from Excel (Preview) in the button Import in the command bar
   
    
4. Click on Select from device to browse and upload your Excel file or drag&drop it.
    
5. If several worksheets are present in the file then select the worksheet you want to import.
    
6. Then the AI assistant will analyse the file and generate 3 tabs Mapped, Possible Match and Unmapped.
   In tab Mapped you can view columns automatically mapped by the system.
   You can manually select a different source column from the dropdown Source column.
   
   In tab Possible Match you have access to columns with a possible match detected.
   For each of them you have 3 possibilities:
   - Accept Match : confirm the suggested mapping. If you click on this button the mapping moves into Mapped tab.
   - Reject Match : discard the suggested mapping. If you click on this button the mapping moves into Unmapped tab.
   - Alternatively, you can manually select a different source column from the dropdown Source column without rejecting the match.
   
   In tab Unmapped you have access to  unmapped columns.
   For each of them you can select a column from the dropdown Source column. The mapping will move into Mapped tab.
   
   Finalize the mappings and click on Import to start the process.
   
   IMPORTANT: The Import button will remain disabled until all possible matches are reviewed or resolved.
    
7. Then you can monitor the import status from notifications:
   - In progress: Ongoing import activity with the message Your data is importing displayed
   - Success: Data imported successfully with the message Upload completed displayed
   - Error: Upload completed with some errors displayed that includes a link to download logs for troubleshooting
   Below the 2 notifications generated: 
   

How to update data

In the procedure above we have imported data as new records.
If you want to update records in Dataverse you have to define a primary key during the import.
In my example I import contacts, so the primary key is contactid.

When a primary key is selected the corresponding column (contactid) is added to the tab Mapped and is mandatory.
So your Excel file must contain a column with the unique identifiers (Guids) of the records to update.