Power Apps: Let users import data from Excel

Table of contents:

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

2. Part 1: Power Automate Flow for the data extraction

3. Part 2: Add the flow to a Canvas App

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

The topic: You want to import data from an Excel sheet 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:

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.

Part 2: Add the flow to a Canvas App

1. Add the flow to the canvas app

2. Add a Gallery and an AttachmentControl.

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.

Do you like this article?

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

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

Marc Sigrist (Powerfully GmbH, Switzerland)