Blog
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 works
Here'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.![](/posts/files/a0e4c038-0657-4ffb-a1cc-4afe031ed89e.png)
We can then paste the contents of the clipboard into the text box, and click the 'Generate formula' button.
![](/posts/files/1383582a-ce75-4f28-855c-b296ee30f705.png)
This generates the formula to call ClearCollect. We can copy the output and run the formula from a Power App to create the collection.
![](/posts/files/9ea5a4cd-83c6-4311-a53f-da7a10c52e59.png)
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.
- Categories:
- excel
Related posts
- 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