import excel to a powerapps collection

(0) Share

Report

Posted on by Community Power Pla...

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

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

Wayne_A 113 on at

Like (0)

Report

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)

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
Wayne_A 113 on at

Like (0)

Report

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?

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (1)

Report

"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

Was this reply helpful? Yes No
Wayne_A 113 on at

Like (0)

Report

Great news, glad you got it sorted eventually!

Was this reply helpful? Yes No