top of page
  • Writer's pictureMarc Sigrist

Power Apps: Let users import data from Excel

Updated: Sep 21, 2023

Table of contents:


The topic: You want to import data from an Excel sheet to Power Apps

Import from Excel to Power Apps

Many times I have had the request that users want to import data from Excel. The reasons for this are manifold. Although most of the time the goal is to prevent such an import by building an interface to another system, it can still be useful to be able to provide such a solution.


For example, if we have a legacy system and the data needs to be imported only sporadically, an excel import function can be a pragmatic solution.


For this reason, I will show you below how you can build an import function for your Power Apps users with which an Excel sheet can be imported. And this without the need to prepare the Excel beforehand. This means that the Excel sheet does not have to contain an Excel table as is the case with other approaches. However, one requirement is that we know the column names.


In the following example I work with this Excel sheet:

Excel sheet

Part 1: Power Automate Flow for the data extraction


Although our goal is to be able to import an Excel sheet into Power Apps (canvas), we will start with a Power Automate Cloud Flow.


The 4 steps of the flow, can be seen in the screenshot below.


  1. Add the trigger, Power Apps (V2), with a user input of type file. We will use this later to pass the Excel from Power Apps to the flow.

  2. The actual data extraction is achieved with two actions from Encodian. If you don't have an Encodian subscription yet, you can create one at https://www.encodian.com/products/flowr. With the "Free" subscription you can use 50 flow actions per month for free.

  3. Add the two Encodian Actions (Convert Excel and Parse CSV) as shown below.

  4. Add a "Respond to PowerApp" action with an output variable of type "text". This will pass the output of the Parse CSV action back to Power Apps.

And already our flow is ready.

Power Automate Flow with Encodian Actions

Part 2: Add the flow to a Canvas App


1. Add the flow to the canvas app

Add a Microsoft Cloud Flow to Power Apps


2. Add a Gallery and an AttachmentControl.

Power Apps Import Control

AttachmentControls can unfortunately not be inserted directly in Power Apps, but have to be copied out of an "Edit Form" control, for example with a Sharepoint data source. You can find very good videos on this topic if you search for it.


3. For the AttachmentControl add the following formula to the OnAddFile property. This formula sends the file to the flow and stores the flow's response in a collection, including appropriate parsing of the JSON. The blue marked terms in the formula should be replaced by the corresponding Excel column names.

OnAddFile:

Set(VarJsonFlow,
ImportXlsxFlow.Run({file:{
         contentBytes: Last(Self.Attachments).Value,
         name: "ABC.xlsx"
     }}).jsonflow)
;
Reset(Self);

     ClearCollect(
    collInputData,
    ForAll(
        Table(
            ParseJSON(VarJsonFlow)),
        {   FirstName: Text(Value.'First Name'),
            LastName:Text(Value.'Last Name'),
            Phone: Text(Value.Phone),
            Mail: Text(Value.Mail)
        }
    )
)   

4. Add the collection "collInputData" to the Gallery as an item property and add labels to the Gallery (for example with ThisItem.FirstName).

Now you can test the solution in Play Mode. Drag your Excel sheet to the AttachmentControl. The content will be sent to the flow and the response will be stored in the collection and displayed in the gallery.


🥳🥳🥳 And we are already done.


Part 3 (optional): Import the data to a datasource

With the demonstrated solution, the data is stored in a Gallery. Of course, you could also save the data directly to any data source. For this I would recommend that this is already done in the flow. I.e. you could insert a "Parse JSON" action and then save the data in any data source. See screenshot below.

Microsoft Dataverse

Do you like this article?

Please write a comment or leave a like below and follow me on LinkedIn.

Marc Sigrist Powerfully GmbH


If you have any questions, I'm happy to help.

Power Platform Specialist

Marc Sigrist (Powerfully GmbH, Switzerland)


Comments


bottom of page