Dataverse Table in Excel

(0) Share

Report

Posted on by Sajarac

3,273

Hi there,

could someone know about this?

I have a table in Teams (PowerApps) a CDS table "Dataverse" I have exported that table to allow some user copy/paste some data from excel files. I am trying to copy 5000 records to publish back to my table, but for some reason excel is only publishing 100 records. Any idea?

And also in excel in the table design I am removing the extra columns that by default CDS put in my spreadsheet such as user, created, modified, etc. to match my excel file after remove those extra columns then I am not able to "Publish" my file again.

thanks in advance for any help.

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (0)

Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

I will.

thank you very much for your advice.

regards.

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @sajarac,

In my opinion 500 records still seems quite low to hit the throttling limits and should be much higher (I think it's 5000 requests every 5 min if I'm not mistaken). You may want to open a support ticket with Microsoft to advise if this is just an undocumented limit you're running into, or if it's a bug.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

You are right. It only posts 500 records. And Publish data sequentially is uncheck.

I just did a test copying 400 records at once, then publishing. Waiting for excel to load the cache. Noticed that once the data is submitted Dataverse send databack to my excel file such an owner's information etc. So copy/paste in this way "YES" excel and the connector works. After that I have selected in my original excel file 1000 records, copy paste to my connected file, then it gets stuck processing 500 changes.

So any workaround? I need to copy over 100K records.
😞

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @sajarac,

You might be running into Dataverse API throttling limits. What happens if you uncheck "Publish data sequentially"? Does that make any difference?

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

Estrange and weird. I can not pass over 500 records
Gets stuck at that point.

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @sajarac,

It mentions in the limitations for the Open in Excel is 1 million cells (rows multiplied by columns) at a time, not a limit of the database. It also mentions if there is more than that loaded at a time then it will have issues and not publish all the data changes. So for large datasets that exceed that then you'd need to break it up into multiple.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

Hi, I have made another test I only copy / paste one column from my excel file now it was able to publish 500 record. Well at least I made a progress.
Question? When you say the limit is 1M cells?. this is the amount of data in the database? or this is the amount of data than I can publish in one shot? So if I have 4 millions already in my database I can pass another million ? or I already reached the capacity?

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @sajarac,

The limit should be 1 million cells, so unless you have more than 200 columns in your 5000 row file, it shouldn't be an issue. You can test the theory by making a simple Test table with a single custom column and just try to fill in 5000 rows and see if it publishes more than 100.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Sajarac 3,273 on at

Like (0)

Report
Copy link

Link copied!

Could be !
but why is only saving in chunks of 100’s?

Was this reply helpful? Yes No
ChrisPiasecki 6,422 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @sajarac,

Is there perhaps some special characters in one of your text columns that may be causing issue? You can try removing your custom columns one at a time from the Excel file via the designer and keep trying to publish until you find the offending column, then from there it's just finding the row(s) with the issue.

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No