Automate Dynamics 365 Record Creation from Excel with Power Automate

Why Import Excel Data into Dynamics 365?

Many business processes begin with Excel spreadsheets. Common examples include:

• Customer Imports
• Lead Uploads
• Product Data
• Employee Information
• Project Records
• Vendor Details

Without automation, users must manually enter records into Dynamics 365, which consumes time and increases the risk of errors.

Solution Overview

The solution uses the following components:

• Excel File
• OneDrive or SharePoint (for file storage)
• Power Automate (for workflow automation)
• Dataverse (the data platform for Dynamics 365)
• Dynamics 365 (the target application)

The workflow is as follows:

1. Excel File
2. Read Rows
3. Apply to Each Row
4. Create Dataverse Record
5. Success Notification

Prerequisites

Before creating the flow, ensure:

• The Excel file is stored in OneDrive or SharePoint.
• The data is formatted as an Excel Table.
• A Dataverse environment is available.
• Required security permissions are assigned.

Step 1: Prepare the Excel File

Create an Excel spreadsheet with your data. For example:

Crucially, convert the data into an Excel Table. Power Automate requires table-formatted data for processing.

Step 2: Create a Power Automate Flow

Create a new cloud flow in Power Automate. You can choose from several triggers, depending on your needs:

• Manual Trigger: Start the flow manually.
• Scheduled Trigger: Run the flow at a specific time.
• File Uploaded Trigger (SharePoint): Start the flow when a file is uploaded to a specific SharePoint folder.

The trigger determines when the import process starts.

Step 3: Read Excel Rows

Add an action using the Excel Online (Business) connector. Select the List rows present in a table action.

Configure the action by specifying:

• Location: OneDrive for Business or SharePoint.
• Document Library: The library where your file is stored.
• File: Select the Excel file.
• Table: Choose the name of your Excel Table.

This action retrieves all rows from the Excel file.

Step 4: Process Each Row

Power Automate will automatically create an Apply to each control to iterate through the rows retrieved in the previous step. Each row becomes available for processing within this loop.

For each row, you can then add actions to create records. For example, you might add an action to create a Contact record in Dataverse.

The flow will continue this process until all records from the Excel file have been processed.

Step 5: Create Dataverse Record

Within the Apply to each loop, add an action using the Microsoft Dataverse connector. Select the Add a new row action.

Configure the action by specifying:

• Table Name: Select the Dataverse table you want to create records in (e.g., Contacts, Accounts, Products).
• Field Mapping: Map the columns from your Excel table to the corresponding fields in the Dataverse table.

For example:

• Excel FirstName maps to Dataverse ContactFirstName
• Excel LastName maps to Dataverse ContactLastName
• Excel Email maps to Dataverse EmailAddress

The flow will now create records automatically in Dynamics 365 based on your mappings.

Real-World Example: Customer Import Automation

Sales teams often receive customer spreadsheets. Instead of manual entry, Power Automate can:

• Read the Excel data.
• Optionally validate information (e.g., check for required fields).
• Create Contact records in Dataverse.
• Create related Account records if necessary.
• Send a completion email notification.

The result is that thousands of records can be imported automatically, saving significant time and effort.

Handling Duplicate Records

A common requirement is preventing duplicate records. Before creating a new record, you can check if it already exists.

Add the List rows action from the Dataverse connector to query existing records based on a unique identifier (like an email address).

Use a Condition control:

• If Email Exists: Skip the record creation.
• Else: Proceed to create the new record.

This improves data quality and prevents duplication.

Error Handling

Implementing robust error handling is a best practice. Use Scope Actions to group actions and manage potential failures.

Create:

• A Try Scope to contain the main actions (reading Excel, creating records).
• A Catch Scope to handle any errors that occur within the Try Scope.

This allows you to track failures, log errors, and generate reports, making troubleshooting easier, improving monitoring, and increasing the overall reliability of your flow.

Common Use Cases

• Lead Imports: Import marketing leads into Dynamics 365 from various sources.
• Customer Migration: Move customer records from legacy systems or spreadsheets to Dynamics 365.
• Product Uploads: Create product catalogs automatically in Dynamics 365.
• Employee Onboarding: Import HR records for new employees.
• Vendor Management: Create vendor records from procurement spreadsheets.

Best Practices

• Use Excel Tables: This is required for Power Automate to reliably process your data.
• Validate Data: Implement checks for mandatory fields and data format before record creation.
• Handle Duplicates: Use logic to avoid creating duplicate records.
• Log Errors: Maintain audit information for troubleshooting and monitoring.
• Process Large Files Carefully: For very large files, consider using pagination and batch processing techniques to avoid timeouts and manage performance.
• Test in Sandbox: Always validate your mappings and flow logic in a sandbox environment before deploying to production.

Benefits

• Faster Imports: Automate large-scaled data entry processes.
• Reduced Manual Work: Eliminate repetitive and time-consuming tasks.
• Improved Accuracy: Minimize human errors inherent in manual data entry.
• Better Data Quality: Ensure data is validated and consistent before it enters Dynamics 365.
• Scalable Automation: Efficiently handle large datasets and adapt to growing business needs.

Conclusion

Creating Dynamics 365 records from Excel using Power Automate is one of the most effective ways to automate data imports and reduce manual effort. By combining Excel Online, Dataverse, and Power Automate, organizations can build scalable solutions that improve efficiency, increase accuracy, and streamline business processes.

Whether importing leads, customers, products, or employee records, Power Automate provides a flexible and reliable approach for automated record creation.