Import from Excel in Canvas Power Apps V2

In this #PowerShot, I will show you how to use the Import from Excel PCF V2 that @agarwal_ritika and I have recently created. You can use this control in Canvas Power Apps to directly import linear structured data from an Excel sheet and then perform further actions on it.

Link to the first version of Import from Excel PCF component for canvas apps.

You can download the managed solution or clone the repository from here.

What has changed?

1. Reset Property: In the first control, once the file was uploaded, a user would have to upload another file just by clicking the Choose File button. In this change, we have added a "Reset Control" property that allows you to clear the imported file before importing another one.

2. JSON function: In the first control, after the file was uploaded, we used a lot of text manipulations to get the data appropriately into a gallery. In the updated version, you will be able to use the JSON function in Canvas Apps to parse the excel data.

Implementation

• Add the control to the app using Get More Components. Once the control is added, place it on the screen.

• Add a button to the screen (Reset Button in the screenshot below). The expression on the button is as follows:

OnSelect: UpdateContext({varConReset:true}) //This is to update the variable context when the button is clicked.

Text: "Reset" //Display text of the button.

• Add the below expressions for the Import from Excel control:

ResetControl: varConReset //Pass the variable context.

OnChange: UpdateContext({varConReset: false});
Clear(colDataExcelImport);
If(
    !IsBlank(importdatafromcustomexcel1.Output),
    Collect(
        colDataExcelImport,
        ForAll(
            Table(ParseJSON(Text(importdatafromcustomexcel1.Output))),
            {
                FirstName: Text(ThisRecord.Value.'First Name'),
                LastName: Text(ThisRecord.Value.'Last Name'),
                EmailAddress: Text(ThisRecord.Value.Email),
                Phone: Value(ThisRecord.Value.Phone)
            }
        )
    )
); //Expression to parse the excel data and structure it into a collection for displaying on the gallery control.

• Add a gallery control to the screen and use the expression below:

Items: colDataExcelImport //The collection with excel data.

• In the individual cell items of the gallery add relevant expressions for data mapping. e.g., for First Name, ThisItem.FirstName

---

Setup in Action

---

In this post, we saw an updated version of the Import from Excel PCF component for Canvas Power Apps. This control, helps you to directly implement a functionality that enables users to upload an excel file and work with the data on a screen in Canvas Power Apps.

I hope you found this interesting and it helped you. Thank you for reading!