cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DAVIDPOWELL
Level: Powered On

import excel to a powerapps collection

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

1 ACCEPTED SOLUTION

Accepted Solutions
Wayne_A
Level: Powered On

Re: import excel to a powerapps collection

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)

 

 

View solution in original post

7 REPLIES 7
Wayne_A
Level: Powered On

Re: import excel to a powerapps collection

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)

 

 

View solution in original post

DAVIDPOWELL
Level: Powered On

Re: import excel to a powerapps collection

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
Level: Powered On

Re: import excel to a powerapps collection

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
Level 10

Re: import excel to a powerapps collection

Hi @DAVIDPOWELL ,

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

 

DAVIDPOWELL
Level: Powered On

Re: import excel to a powerapps collection

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

DAVIDPOWELL
Level: Powered On

Re: import excel to a powerapps collection

"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
Level: Powered On

Re: import excel to a powerapps collection

Great news, glad you got it sorted eventually!

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,445)