In this #PowerShot, I will show you how to use the Upload Excel PCF that @agarwal_ritika and I have recently created. You can use this control in Canvas Apps to directly import linear structured data and then perform further actions on it.
Let's Get Started
Step 1: In this step, we will look at creating the component. Follow the steps here to create a generic component structure of type field. Once the setup is complete, certain parts of the index.ts and ControlManifest.Input.xml files need to be modified. The same have been updated and present on the GitHub repository as mentioned below.
The source code for this component is on this GitHub repository here.
Note: For canvas app, only field type component is supported. Source
The next task is to configure components for canvas apps. This can be done in three easy steps and those have been listed out here.
After following steps 2, 3, 4 from the previous blog, follow the step below to implement the import from excel functionality in the Canvas Apps component on the canvas app.
Step 5: Add the control on the app screen. Make a note of the name of the control once it is placed on the screen.
Optional: You can rename the control to keep consistency in naming convention on your app.
Step 6: Add the below expression on the "OnChange" property of the PCF control added on the screen.
Clear(colPCFOutput);ForAll(Split(Substitute(Substitute(importdatafromcustomexcel1.Output,"[",""),"]",""),"},{"),With({SingleObject:Substitute(Substitute(Result,",",":"),Char(34),"")},Collect(colPCFOutput,{Column1:Last(FirstN(Split(SingleObject,":"),2)).Result,Column2:Last(FirstN(Split(SingleObject,":"),4)).Result,Column3:Last(FirstN(Split(SingleObject,":"),6)).Result,Column4:If(CountRows(Split(SingleObject,":"))>6,Last(FirstN(Split(SingleObject,":"),8)).Result)})))
Here, we are using a collection to store data from different columns detected in the excel file and performing string manipulations to extract the values.
In this example, we considered an excel file with four columns, if you have more columns, then you have to add additional columns in the expression.
If you notice Column4 has an if condition, which means if the excel file does not contain four columns, but instead it only has three columns, the expression will still work. In case, you have more than 4 columns, then you can add more columns in the expression as: Column5:If(CountRows(Split(SingleObject,":"))>8,Last(FirstN(Split(SingleObject,":"),10)).Result)
Replace the green highlighted part with (ColumnIndex - 1 ) * 2
Column 5 -> (5 - 1) * 2 = 8
Column 6 -> (6 - 1) * 2 = 10
Replace the yellow highlighted part with (ColumnIndex * 2)
Column 5 -> (5 * 2) = 10
Column 6 -> (6 * 2) = 12
Note: If you do not have a definite number of columns that the excel file will hold, then you can add a hard limit and add the expression based on that. Example: Your app supports an excel file with at max 25 columns.
Setup in Action
In this post, we saw how to use the upload excel PCF to import data directly from an excel table to canvas apps in Power Apps. This opens up a lot of options for users across multiple use cases to enable data upload, offline capabilities and etc. with Canvas Apps and Excel.
I hope you found this interesting and it helped you. Thank you for reading!