Tools - Convert Excel to Collections Online

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.

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.