import excel to a powerapps collection

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

From the design view in Canvas app.

Is it posssible to import excel data into a specific Collection using a function or formula ?

I cant find specific information on how to import excel data directly into a Collection either ?

Thanks

david powell

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

Verified answer

Wayne_A 113 on at

Like
a
(0)

Report
Copy link

Link copied!

As long as the Excel workbook is online then yes you should be able to.

You need to set up a connection to the workbook first as you would do for any datasource (you can do this manually or if starting from scratch you have the Start from Data option on the PowerApps start screen), and I think (from memory) that your data should be in a Table.

Then when you have connected to your data (in this example, named Table1), you can use the ClearCollect function to add the data into the collection and then reference using your collection name from then on:
ClearCollect(YourCollectionNameHere,Table1)
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks wayne,
Created the Collection, then i created a one drive connection to the excel workbook.
Created a table in the spreadsheet "Table1"
Used the Clear Collect but keeps creating extra columns in the collection.

One thing that did work properly was i used the Collect() function to just create a new collection on the fly and no issues ?

Thanks
David
Wayne_A 113 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi David,

Glad it helped somewhat - but yes that's weird as the ClearCollect function should do the same as the Collect function but just clear the collection first before adding the new data?
v-siky-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,
Could you please share some screenshots and codes?
The Collect function and ClearCollection are almost the same, expect that the ClearCollect function will clear first before collect.
More information would be better for us to provide further assistant.
Best regards,
Sik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

I trimmed down my data in excel from 48,000 to 10,000..just to get something to work.
Syntax of course was the issue. my field names did not match. After getting all that squared away.

Finally created a "Collection" from the excel workbook, along with my 3 fields and was able to load/import all 10,000 records/rows.
For the heck of it just also loaded a "Data Table" directly from Excel. Quickly displayed the data and saved it as opposed to the "Collection" which it blew out the Data, after closing the App as expected.

david
Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

"Syntax of course was the issue. my field names did not match. After getting all that squared away."
what i meant by this was Case sensitive is an issue to be aware of.
david
Wayne_A 113 on at

Like
a
(0)

Report
Copy link

Link copied!

Great news, glad you got it sorted eventually!