One of the most common scenarios is to be able to pick an Excel spreadsheet from a SharePoint Document location and create records in Dataverse.
There are several ways to do this. But, one of the most common scenarios could be to use Power Automate Flow and use Excel Online and SharePoint Online connectors to perform this operation!
Here’s the scenario which you can expand on and fit the same according to your Use Case –
- There’s a file in a SharePoint Document location called as AccountImport.
- This file has some Account information that needs to be inserted in Dataverse
Here’s the Excel content which has a Table in it.
- And this data needs to be Inserted in Dataverse [You can either Automate this Flow, Make it On Demand — based on whatever is suitable for you]
Let’s create an On-Demand Flow in order to pick this File and then insert into Dataverse.
Power Automate Flow
Here’s the Power Automate Flow which we’ll create. For the same of this example and to keep it simple, we’ll create an On-Demand Flow –
- Create an On-Demand Flow in Power Automate [You could even choose to run the Flow when a SharePoint file is created or changed or even to Run the Flow once every day — depending on what best suits your case]
Then, look for the action called as List rows present in a table from Excel Online connector
- In this Action, you can select what SharePoint Site is to be selected where you have the File in the Document Location.
Also, then select the Document Library where the Documents reside. That’s the ideal place where you would want to place your Documents.
Finally, select the File itself by navigating from the Folder icon on the File property as shown below.
And then select the File once you find it.
- Once you have selected the File, the Table itself will be available to pick up from the Table property. Make sure to convert the Excel data into a Table.
- Now, once all the Properties are set on the List rows present in a table action, select a For Each loop in the Flow.
And in the Inputs, give value (List of Items) from the List rows present in a table action which we just completed above.
- Once this is set, select Add a new row action from the Dataverse connector in order to create the records sequentially in Dataverse.
- Here, map the Columns to the fields in Dataverse. Firstly, select the Table in Dataverse you want to insert these records into.
Then, select the fields from the Excel which the Excel connector itself separated out for you.
- Once you complete all the Fields from the Excel to the Dataverse connector.
Save and Test the connector itself.
This will create the records in Dataverse (Dynamics CRM)
And it’ll Run in a few moments and succeed if everything goes right.
Now, let’s see the Flow in action –
- Because this is an on-demand Flow, you can Run it whenever you want. And when it Runs successfully, records in the Dataverse will be created as shown below
Hope this helps!
Here are some Power Automate posts you want to check out –
- Select the item based on a key value using Filter Array in Power Automate
- Select values from an array using Select action in a Power Automate Flow
- Blocking Attachment Extensions in Dynamics 365 CRM
- Upgrade Dataverse for Teams Environment to Dataverse Environment
- Showing Sandbox or Non Production Apps in Power App mobile app
- Create a Power Apps Per User Plan Trial | Dataverse environment
- Install On-Premise Gateway from Power Automate or Power Apps | Power Platform
- Co-presence in Power Automate | Multiple users working on a Flow
- Search Rows (preview) Action in Dataverse connector in a Flow | Power Automate
- Suppress Workflow Header Information while sending back HTTP Response in a Flow | Power Automate
- Call a Flow from Canvas Power App and get back response | Power Platform
- FetchXML Aggregation in a Flow using CDS (Current Environment) connector | Power Automate
- Parsing Outputs of a List Rows action using Parse JSON in a Flow | Common Data Service (CE) connector
- Asynchronous HTTP Response from a Flow | Power Automate
- Validate JSON Schema for HTTP Request trigger in a Flow and send Response | Power Automate
- Converting JSON to XML and XML to JSON in a Flow | Power Automate