Answered

Data load for Dataverse for Teams tables

(3) Share

Report

Posted on by G-Faulk

Can someone suggest some solutions for loading data into my Dataverse for Teams application? I've setup tables and have hundreds of records to add, but the only method I see is manually typing in the data.

Do we have to create some Power Automate to pick up data in a CSV or Excel file and add it to the tables? Or is there some better way?

Thanks for your ideas.

Geoff

Categories:

Microsoft Dataverse

All responses (12)

Answers (2)

MIA27 on at

Like (0)

Report

Re: Data load for Dataverse for Teams tables

Mr. Garry,

Noted you update today while searching,
Could you please review the link below which I posted in excel community that I am unable to post data via excel due to error or read only

https://answers.microsoft.com/en-us/msoffice/forum/all/how-add-the-additional-row-to-existing-table/dbabf286-580e-4ca6-8c6b-90f0f8171bb9

please guide
Community Power Pla... on at

Like (0)

Report

Re: Data load for Dataverse for Teams tables

Is it possible to import data generated from powershell to dataverse for teams? Thanks.
AlanBourke 37 on at

Like (0)

Report

Re: Data load for Dataverse for Teams tables

Thanks - the issue was that for some reason the Teams desktop client doesn't have this functionality, you have to do it in a browser.
Community Power Pla... on at

Like (0)

Report
Re: Data load for Dataverse for Teams tables

@AlanBourke wrote:
@GarryPopeThe information in the

Use “Edit in Excel” in Dataverse for Teams

link seems incorrect. It says:

From the Build tab select Tables, and then select your table.
Expand the ellipses menu to access the “Edit in Excel” feature.
I don't have an ellipsis menu.

Hi Alan,

After installing the MS Office PowerApps add-in, it took me a bit to find the ellipsis menu with the appropriate "Edit data in Excel" option within Teams. I discovered that you have to click the Build tab, then click the See All link. The "Edit data in Excel" option will not be on those menus even though your tables may be listed there. You still have to click the Tables tab on the left panel and only then can you select the ellipsis menu and get the "Edit data in Excel" option.

Hope it helps!
Community Power Pla... on at

Like (0)

Report

Re: Data load for Dataverse for Teams tables

Hi Everyone,

The Power Automate approach above looks like a great solution. However, one of the reasons we are using Dataverse in Teams is to avoid the premium costs of connecting to our onsite data, etc. and this Power Automate uses a couple of Premium Actions.

I have also tested the "Edit in Excel" in Dataverse for Teams and it works great, but it is a very manual process. I will need to update my 5 data tables 2-3 times per day so doing this manually will get tedious. Has anyone found another automated solution or built one with Power Automate? I am still learning Power Automate, so not sure what it's full potential is without using Premium Actions.

Thanks for any advice!
AlanBourke 37 on at

Like (0)

Report
Re: Data load for Dataverse for Teams tables

@GarryPope The information in the

Use “Edit in Excel” in Dataverse for Teams

link seems incorrect. It says:

From the Build tab select Tables, and then select your table.
Expand the ellipses menu to access the “Edit in Excel” feature.
I don't have an ellipsis menu.
G-Faulk 68 on at

Like (1)

Report

Re: Data load for Dataverse for Teams tables

I had not, but this will be awesome. I'll have to check it out.
Verified answer

CU01081947-0 716 on at

Like (5)

Report

Re: Data load for Dataverse for Teams tables

Hello @Geoff_Olives,
I'm sure you've seen this post already, but just in case anyone lands here, Microsoft have now provided functionality to import/edit data into Dataverse for Teams via Excel.
Here's the blog post.
Here's the Microsoft Docs.
Thanks,
Garry
EricRegnier 8,714 Most Valuable Professional on at

Like (2)

Report

Re: Data load for Dataverse for Teams tables

Yep, there might be a learning curve at first but from a developer/citizen developer perspective promise that you'll get the hang of it quickly. Also, note the Dataverse for Teams just came come out so we should expect a lot of features and enhancements in the next waves...

Unfortunately there isn't an upsert function with the connector but there's is with the SDK.

You should submit that idea at (if it doesn't already exists): https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

Cheers
Verified answer

G-Faulk 68 on at

Like (5)

Report
Re: Data load for Dataverse for Teams tables

I had to build a Power Automate flow.
There is no "upsert" CDS function, so I had to create a flow that looks at an Excel table and cycles through the records, for each row it queries the database to see if the record alread exists. If so, then update. If not, then insert.
This is so incredibly complicated for a tool that Microsoft is trying to put into the hands of the user! It took a few hours to learn how to put it all together correctly, and changes to the data are a pain. Tell me that any of my users would be able to do this, even with guidance.

Setup your import file:
Create an Excel file in Teams. Save the file in a channel.
Add columns and data
Format as a table
Name the table the name of your table to help keep it straight.
Import data via Excel in Dataverse for Teams:
Open Power Apps in Teams
Click the Build tab.
Select your team and click See all in the Built by this team panel.
Click the Flows page on the left and then click New.
Create a new flow, from manual trigger.
Add a step - Excel: List rows present in a table
Point the step to the sharepoint site for the team
Point the Document library to the library in the team
Choose the file. I called mine "data load".
Select the table that was named above.
Add a step - Apply to each
Output from this step is the Excel value
Add a step inside the apply to each - CDS List records. Look for an existing record with the filter.
Set the Entity name to the name of the Dataverse table
Show advanced options
Set Filter query to be the key column of the table, equal to, and set to column from your Excel table. The name of the column in the Dataverse table is not the same as the description. Open the table to see all the columns with the actual column name. My "Name" column was actually named "cre4f_name". "eq" mean equals. And then surround the Excel field from the previous step with single tick marks. The result looks like this:
Query: cre4f_name eq '[xl]Name'
Add a step inside the apply to each - Condition
Set the condition to be an expression. Check the length of the output to see if a record came back from the query. "List_records" below is the name of the previous step, with the space replaced by an underscore.
Expression: length(outputs('List_records')?['body/value'])
Add a step to the Yes condition.
Update a record 2
Set Item ID to Previous CDS entity (My table is called Member)
Set the new values for the fields. I updated Email, first name, last name, phone.
I had to use a formula to setup dates properly. "If the Join date field isn't empty, then set the join date to start of day(join date).
if(equals(empty(items('Apply_to_each')?['Join Date']),bool('True')),null,startOfDay(items('Apply_to_each')?['Join Date']))

Add another step to the no condition.
Create a new record. Entity is the CDS members.
Set the key.
Update all the fields as above.

Test. Fix. Repeat until it actually works.