Tools - Convert Excel to Collections Online
March 3. 2022
If you need to create a local collection based on data from an Excel spreadsheet, I've created an online tool to generate formula that builds a collection, based on data that's been copied from an Excel spreadsheet. Here are the details of how this works.
When writing formula to carry out data manipulation tasks, I often find it useful to build local collections. I can then use the collection to easily experiment with formula.
To make it easier to create collections, particularly when we have existing data in an Excel spreadhseet, I've created a page that enables you to paste data from an Excel spreadsheet, and generates the formula to call ClearCollect.
The link to this page is here:
How the Excel to Collection page worksHere's a summary of how this page works. The first step is to copy the data from Excel into the clipboard. The first row should contain the column headings.
We can then paste the contents of the clipboard into the text box, and click the 'Generate formula' button.
This generates the formula to call ClearCollect. We can copy the output and run the formula from a Power App to create the collection.
At the moment, the tool produces collections where all fields are of data type 'text'. However, it should still hopefully be of use to anyone who wants to quickly create a collection based on Excel data.
- How to use formulas to perform calculations in Excel and to display the results in Power Apps
- Excel - What to be aware of when building apps with Excel - Service Rate Limit
- Dataverse - Excel Import - How to fix Microsoft ACE OLEDB 12.0 Error
- Bug - Excel Online (Business) connector fails to search or to filter against columns with a space in the name
- Excel - Vote to add numeric column support to the 'Excel Online Business Connector' connector!
- Excel - All you need to know about the 'Excel Online Business' connector
- Data - Excel vs Google Sheets