Data load for Dataverse for Teams tables

[Geoff_Olives]

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

[HSheild]

Hi @Geoff_Olives ,

I’m haven’t tried this scenario yet but it looks like you will have to build a Power Automate Flow at this stage based on the documentation.

https://docs.microsoft.com/en-us/powerapps/teams/data-platform-faqs#how-do-users-import-data-into-ta...

security.

How do users import data into tables in Dataverse for Teams?

Makers have the opportunity to bring in data through both the apps they develop as well as via connectors in Power Apps and Power Automate.

[EricRegnier]

Hi @Geoff_Olives,

Haven't tried this either, but Dataflows might work with the CSV or Excel connector:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows 

EDIT: might not be possible after all following this thread: https://docs.microsoft.com/en-us/powerapps/teams/data-platform-compare#business-intelligence-and-pro...

Cheers

[Geoff_Olives · Accepted solution]

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:

1. Create an Excel file in Teams. Save the file in a channel.
2. Add columns and data
3. Format as a table
4. Name the table the name of your table to help keep it straight.

Import data via Excel in Dataverse for Teams:

1. Open Power Apps in Teams
2. Click the Build tab.
3. Select your team and click See all in the Built by this team panel.
4. Click the Flows page on the left and then click New.
5. Create a new flow, from manual trigger.
6. Add a step - Excel: List rows present in a table 
   1. Point the step to the sharepoint site for the team
   2. Point the Document library to the library in the team
   3. Choose the file. I called mine "data load".
   4. Select the table that was named above.
7. Add a step - Apply to each
   1. Output from this step is the Excel value
8. Add a step inside the apply to each - CDS List records. Look for an existing record with the filter.
   1. Set the Entity name to the name of the Dataverse table
   2. Show advanced options
   3. 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:
   4. Query:  cre4f_name eq '[xl]Name'
9. Add a step inside the apply to each - Condition
   1. 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.
   2. Expression:  length(outputs('List_records')?['body/value'])

1. Add a step to the Yes condition.
   1. Update a record 2
   2. Set Item ID to Previous CDS entity (My table is called Member)
   3. Set the new values for the fields. I updated Email, first name, last name, phone.  
   4. 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.

[EricRegnier]

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

[GarryPope · Accepted solution]

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

[Geoff_Olives]

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

[AlanBourke]

@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.

[Anonymous]

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!

[Anonymous]

---

@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!