Unanswered

Use excel as data source with 60,000 + records

(0) Share

Report

Posted on by CMSGuy

170

I have an issue and need advice and direction. I want to use an excel file as a datasource, but I get the following error when connecting : "Number of rows in the table exceeds maximum supported number of rows when populating a column. Number of rows: 67740, maximum supported: 64000." There really is not way to filter the excel file as it is a daily file generated from SAP data. And the number of actual records is variable on a day-to-day basis.

I am building an app that needs to query this data and only get records that match the selected employee number. Should I build a power automate job that runs on the gallery selection and goes out to the file and returns only the rows (from an oData query) and writes those to a temporary sharepoint file, or collection in the power app? That seems convoluted. Just wondering if there is an easier way to do this without going the Dataverse route (as I am not sure about premium licensing).

Thanks for the advice.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Hi @CMSGuy ,

You are going to have considerable query issues with an Excel file that big anyway - if you have access to SharePoint Lists, I strongly suggest you migrate to one of these - you can populate these with Power Automate from Excel.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
CMSGuy 170 on at

Like (0)

Report

Thanks Warren for the advice and I actually have gone this way too. Because this is a daily file, would I need to delete all current records from the SharePoint list and replace them with the new excel file records? How would that work? The excel file does NOT have a "primary" key. I guess doing the "CRUD" operations on running the excel file and replacing, updating current records confuses me.

Was this reply helpful? Yes No
WarrenBelz 153,034 Most Valuable Professional on at

Like (0)

Report

Hi @CMSGuy ,

Yes that is certainly a task - trying to sort out any update would probably be worse, so yes, you would need to delete the previous list (that will take some time in itself and be prepared for strange errors). It is possible to add records form Excel to SharePoint in batches of about 10,000 but this is a bit of a manual operation in itself.

There is no easy solution here - writing to an Excel table formatted for a Power Apps data source from outside Power Apps also can cause considerable issues as the "index" column is not written, and this is not allowing for the poor Delegation capability of Excel which I mentioned before. You might explore getting the SAP data into Azure SQL and connecting to that, but it is not something I can guide you on.

Was this reply helpful? Yes No
CMSGuy 170 on at

Like (0)

Report

Thanks. I am going to reach out to some others in our company for advice. If they do subscribe to a premium license connection somehow, they can give me direction and advice on which way to go. I know they have several on-prem sql instances. Not sure about Azure, but possibly.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@CMSGuy

See https://powerapps.microsoft.com/en-us/blog/introducing-the-sap-erp-connector/ for a direct connection to sap.

Was this reply helpful? Yes No