Why?
Recently I had to import 9.000 records into a Dataverse table. I wanted to use the Excel Online option of Model Driven Power Apps in Dataverse. I like this option because that is one of the options that a “simple” end user has when using a Model Driven Power App. It is easy to use and we can also use it to bulk edit a big number of already existing records.
What?
This blog post is about my lessons learned on using the option of Excel Online in Model Driven Power Apps to import a large number of records into Dataverse. And track its progress… 🤓
How?
Full up-to-date instructions about the Excel Online option in Model Driven Power Apps of Dataverse can be found here: Open your model-driven app data in Excel Online – Power Apps | Microsoft Docs.
1) The way of work is pretty self-explanatory looking at the unified interface. Just go to the table you want to import (or bulk edit) a large number of records. Use the Export to Excel button to select Open in Excel Online:
2) This will open up the table in an Excel Online view and here I was able to paste my 9.000 records. I had a source Excel file where I had 9.000 records matching a 4-digit zipcode with a location of preference:
Even lookups to other tables will be able to match. Just make sure you match the primary column name of the lookup table using a unique name in that table. In my case Amsterdam was one of the 12 Locations in the lookup table:
3) The peculiar thing is that upon saving my changes a popup window appears. Apparently the data is not saved immediately but is queued the same way an import file is queued for processing:
This is totally fine by me as long as I am able to track the progress. So I clicked the Track Progress button. Now I see view of My Imports appearing in the same popup:
I could not find a direct link to this overview, because of the popup. I really wanted to have a direct link to this overview for example when troubleshooting failed updates using this Excel Online method…
Opening up a record from this view opened up a form with its own URL showing me all kind useful information on my import / updates. The link looked like:
https://[organisation url].crm4.dynamics.com/main.aspx?appid=617f5b47-9682-ec11-8d20-000d3a648513&pagetype=entityrecord&etn=importfile&id=c0642a57-999b-ec11-b3fe-000d3adb82dbThis gave me the idea to find the table of the importfile records:
Unfortunately I was not able to find this table using the the Power Apps Maker Portal. We will not find some tables of Dataverse in this user interface… So what if we edit the url of the Model Driven App to show the table by changing the url??
https://[organisation url].crm4.dynamics.com/main.aspx?appid=617f5b47-9682-ec11-8d20-000d3a648513&pagetype=entitylist&etn=importfileSuccess!
Now I was able to see all my recent imports and the details about them.
This makes it easier to compare the numbers about (partial) failures. And we can come back to this view any time we want 💪👍
