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!

 

 

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 ;(

 

Highlighted
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
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 351 members 5,690 guests
Please welcome our newest community members: