cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Bring an excel with 40k rows

Hey Guys, can you help me please?

 

I have a excel spreadsheet wich cointains 37k rows and i want to bring it to powerapps.

 

the data does not change, so i uploaded the excel via import via excel, and when i collect it brings me 15k rows.

 

but how can i separate in 15k15k and another one with 8k to bring all the datas?

 

I tried several ways here on the forum using concurrents, collecting ascending and then descending but i guess am doing something wrong.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Bring an excel with 40k rows

Hi @Anonymous 

I think what you need to do is to split the data in your source Excel spreadsheet so that your 37k rows are separated over 3 tables/worksheets.

You can then import these 3 separate Excel tables and ClearCollect them into a single collection.

ClearCollect(AllRecords,
ExcelTablePart1, ExcelTablePart2,
ExcelTablePart3
)

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Bring an excel with 40k rows

Hi @Anonymous 

I think what you need to do is to split the data in your source Excel spreadsheet so that your 37k rows are separated over 3 tables/worksheets.

You can then import these 3 separate Excel tables and ClearCollect them into a single collection.

ClearCollect(AllRecords,
ExcelTablePart1, ExcelTablePart2,
ExcelTablePart3
)

View solution in original post

Anonymous
Not applicable

Re: Bring an excel with 40k rows

Nice!

 

Worked like a charm!

 

Thanks!

 

 

Highlighted
Super User
Super User

Re: Bring an excel with 40k rows

Glad that works @Anonymous Hope you win another company prize soon!

 

Anonymous
Not applicable

Re: Bring an excel with 40k rows

Hey @timl , your solution worked, but now when I showed the app they requested me another thing to do that is similar, but different hahaha

 

I will try to explain:

 

I have a list with the 37k rows, in a gallery, showing only when filtered by 2 boxes.

The main point of the app is to do some kinds of checklists, search on the excel table and then submit.

 

I have an unique ID that is the EQUIPMENT, this EQUIPMENT will have many times on sharepoint list (after submitted the checklist).

 

What I need to do is: On the first gallery, shows an check mark if that EQUIPMENT is already done (search on SP list and mark ok, what I did is count if the Equipment appears more than 1 time on SP list and shows an mark, this is easy), the problem is, I have 37k rows of equipments so if I search the equipment on SP list, one day it will not bring anymore due to rows limit right? even if i collect sp list it only brings me 2k rows...

 

I thought in collecting the equipments done in a excel DB but if i collect also brings me only 4k rows. maybe i need to split in a lot of tables in this case ;(

 

Super User
Super User

Re: Bring an excel with 40k rows

Hi @Anonymous 

 

I think you might be OK here. If within your gallery control you carry out a count of your SharePoint list where the equipment IDs match, that query should be delegable and you might not have any delegation problems. It won't be fast but I think it'll work.

Alternatively, if you want to load your SharePoint list into a collection, you could use this technqiue that @martinav mentioned the other day.

https://powerusers.microsoft.com/t5/General-Discussion/Another-method-for-making-a-collection-from-Sharepoint-and-SQL/m-p/253149#M74217

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 334 members 1,784 guests
Please welcome our newest community members: