Need help automating data import to dataverse

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi,

I'm hoping someone might be able to help me identify the correct approach to import my data into Dataverse.

Scenario:

I receive an email every morning containing data. The data arrives as an Excel file attached to the email. There are no tables in the Excel file when it arrives. Currently, this info needs to be manually copied out of the spreadsheet and pasted into other places. I'd like to eliminate the manual steps in this process using Power Automate.

What I've done so far:

I built a flow that saves the Excel file to SharePoint and adds tables to the data.

Automate dataverse.png

Where I'm stuck:

I can't seem to figure out how to get the data out of Excel and into Dataverse. I'm not even sure if the connector shown above is the one I should be using to accomplish what I'm hoping to do. If it is, I'm not sure what to put in the "Row ID" field.

Of note, the number of rows varies from one day to the next. I'm not sure whether this is an important consideration, but figured I'd mention it just in case.

Thank you very much for any insight you may be able to provide!

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

grantjenkins 11,063 Moderator on at

Like (1)

Report
Copy link

Link copied!

I've you've added a Table to your Excel file then you can use List rows present in a table to get the rows from the Excel Table. Then in an Apply to each (using value from the List rows present in a table), you can use Add a new row to add rows into your Dataverse table. As soon as you select the Dataverse table you want to populate it will display the columns ready for you to add the corresponding Excel row values. See example below.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Thank you very much for your reply! That got me a lot closer to the finish line. The challenge I'm having now is determining how to dynamically reference the spreadsheet row values to fill in each of the columns in my Dataverse table:

I feel like it must be something simple that I'm overlooking somehow. Any tips?

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (1)

Report
Copy link

Link copied!

You should see the fields from your List rows present in a table that you can just add directly into your Add a new row fields.

Because you are using the value from your List rows present in a table as the input into the Apply to each 2, it will add a new row in Dataverse for each of the rows in your Excel Table.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

For some reason, all I seem to have to choose from are the same two options:

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
After quite a bit of trial and error, I've made it a few steps further.

I finally ended up grabbing the data using expressions. I couldn't seem to get the Excel file's column names to pop up under standard dynamic content no matter what I tried. The expression I landed on that ended up working was:
outputs('List_rows_present_in_table')?['Name of Column Here']

The flow now runs successfully. When I review the completed runs, I see the data I'm trying to capture listed as output values from the 'List Rows Present in Table' step.

Here's the strange part... despite the flow running successfully, it seems to only be adding blank rows to my Dataverse table rather than the data I want 😩

I'm not entirely sure what to make of this.

Here's the last step in the flow, which is supposed to be adding a new row to the Dataverse table:

I'm not sure if it matters, but 'Identifier' is an autonumber column.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (2)

Report
Copy link

Link copied!

@Anonymous The issues now looks like you don't have a loop for your List rows present in a table as you had before. So, after List rows present in a table you need to have an Apply to each that will loop over each of the rows, and within there you have your Add a new row (like you did before). You should also now see the field names appear for you to just select.

Was this reply helpful? Yes No
tommytables Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

I am having the same issue. I did use the loop as stated, It still won't give dynamic values. I used the work-around and it saves and says it is successful when tested, but adds blank records.

Was this reply helpful? Yes No