Advice regarding an application in dataverse & power apps for inputting excel data

[Anonymous]

We have a client who would like to have their customers import their data from excel to dataverse. The client has many external customers and every customer would need to import the data in their own dataverse tables, so that the client can then utilize this data in other programs (Power BI etc). The customers would only need to import their data, not to read, modify existing or delete etc.

I'm looking for advice what would be the best way to achieve this using Power Platform. The client wants this solution to be easy to use for their end users i.e. a ready solution so that the customer can only import an excel file (which is in the right format of course, column match dataverse table) and submit. We have been thinking about making a model-driven app which we would share with these external users and apparently this method has the import from excel function in it, but i'm not sure whether this is the most user friendly way of doing this, since the excel template would have to be mapped to the columns, and I'm afraid this is too difficult for the end users and errors would occur? I have seen some youtube videos of people doing this through a canvas app and power automate so that the client can only import the excel file and submit, but I don't know if this would be a better option to use in this case? The excel files should be in a standardized format so that the columns match the dataverse tables and the client understands this.

Help or anything to guide us in the right direction is very much appreciated.

[MarioRing]

The Dataverse allows you to generate very well-prepared Excel template files which contain all the columns of the Table (located on the forms) with additional data validation features (for example Excel provides all possible drop-down values and checks if the field hasn't exceeded the character limit).

It is very convenient to use, even for a non-technical user and it doesn't require data mapping during import.

Follow this document to generate an Excel template file for any table in your dataverse environment (for example Account).

Then fill the file with the data you want to import, save it and go to your application.

Navigate to the proper entity, click "Import from Excel", choose your updated file and start the import process. No mapping and additional settings are required.

[LinnZawWin · Accepted solution]

What else do the customers do other than import their data by uploading the Excel file? If they are just uploading the file but do not need any other functionality from the model-driven app or the canvas app, this seems more like the use case scenario for Power Pages. The customers would login to the portal, create a new record and upload the Excel file which will then trigger the cloud flow (Power Automate) to process the file and create the data in Dataverse tables.

[Anonymous]

Thank you, this seems like a good option for us to use. Is it also possible to make this take into consideration duplicate detection, so that if the customer for some reason tries to import the data twice, it can somehow highlight that the data is already in the table and throw an error if this happens?

[Anonymous]

The customers only need to import their data for now, with the possibility of the application to notify them if the data is already in the dataverse table, so that no duplicate records get imported. We were also thinking about Power Pages as a valid option, since the customers are external users,  but then we found that also Canvas apps and model-driven apps can in fact be distributed to external users as well, so we though that maybe it is easier to do the import with a template.

[MarioRing]

Every user of the Model-Driven App should have their own license. It can be not so convenient to manage (guest user accounts from external tenants) and may be somehow expensive. For this type of scenario consider the solution described by @LinnZawWin 

[Anonymous]

okey, we will look into the power pages option then as @LinnZawWin described. Thank you both!