You can use the following steps to retrieve the content of an attached excel file in PowerApps and save it as a collection:
- To attach the excel file, use the "Form attachment control" in PowerApps.
- Create a flow in Power Automate that uses the "Create file" action in OneDrive for Business to store the attached file in the cloud.
- To retrieve the data from the excel file, use the "Get table data" action in Power Automate. This action requires the "Excel Online (Business)" connector to be installed.
- Using the "Compose" action, save the retrieved data in a variable.
- To convert the data stored in the variable into a collection, use the "Parse JSON" action.
- Finally, return the collection to PowerApps by using the "Return" action.
- Use the "Collect" function in PowerApps to save the returned collection to a variable.
Here is an example of the syntax available in Power Automate (JSON):
{
"records": [
{
"Column1": "Value1",
"Column2": "Value2",
"Column3": "Value3"
},
{
"Column1": "Value4",
"Column2": "Value5",
"Column3": "Value6"
}
]
}
To retrieve the collection in PowerApps, use the following code (CSS):
Collect(MyCollection, [Column1], [Column2], [Column3])
You can use the File input control's "Source" property to retrieve the contents of the file and send it to Power Automate for processing. The following is an example of syntax (MakeFile):
Source: ThisItem.AttachmentFiles[0].Content
Please keep in mind that the syntax above assumes you only have one attached file. If multiple files are attached, you must specify the index of the file you want to retrieve.
Here are the URLs to the documentation for Microsoft PowerApps and Power Automate:
- Microsoft PowerApps can be found at https://docs.microsoft.com/en-us/powerapps/.
- Power Automate from Microsoft: https://docs.microsoft.com/en-us/power-automate/
The PowerApps and Power Automate documentation contains detailed information on the Form attachment control, the File input control, and the Collect function.
