Editing data in SQL via Excel

This one is pretty straight forward, but it’s a neat and simple use case, for allowing users to edit data in a SQL database, while remaining comfortable and safe within Excel.

We will navigate to our Power Platform environment, with the previously created Virtual table, find said table and use the edit buttons as listed shown below.

The connection is created through the Excel add-in called “Microsoft PowerApps Office Add-in”, and we can see the contents of the Dataverse virtual table, which is essentially the SQL Server. I have created 1 record, just to have something in there.

Creating a new record is very straight forward, we will be using the menu in the add-in and pressing the “New” button, which will create a new blank line in our table within Excel.

Nothing happens on the SQL side of things, until we press Publish. As we press Publish, the “PeopleId” as well as the “People” column will also be automatically filled out, so some instruction here, as to not filling out the PeopleId and People column.

And just for good measure, some definitive proof that the data does indeed up in SQL, and the PeopleId as well as People columns are indeed populated!

Creating inserts on the SQL side of things, also will not show in Excel, so be wary depending on the amount of users, and dependence of the need of instant updates.

However, clicking the refresh button, will indeed re-query the SQL database, and return the current state at the time.

The end result as shown believe, with a record created using Excel, and being stored in the SQL database, as well as a record created in SQL, well actually two records, being shown in Excel.

In conclusion

As demonstrated above, editing any Dataverse table using Excel is pretty straightforward, however it has some obvious flaws. These include the need to manually refresh our data, as well as a big lack of input controls. Additionally, in this setup we are also limited to publishing 100 records at a time. Luckily though, it does respect constraints on the database side of things. So I am still unable to create duplicates in my primary key column (yay), and I cannot input data that doesn’t match the data types, i.e. 100 characters into a varchar(30) column.

In general the recommendation will not be to go with the above approach, except if we’re going for quick and dirty approach, or just have few very users, who need to use this quite rarely (say monthly, or even less frequent). We would much rather have our users using applications in the shape of both Model-driven apps and Canvas apps.