Excel connector: List rows present in a table

[bart_vermeersch]

Hello,

The action "List rows present in a table" from the Excel connector is giving us some headaches.

We use a simple odata filter. Most of the time it works correctly, but often (when the load is higher) it returns an empty result while it shouldn't. The exact same connector (and filter) does return results a few minutes earlier or later.

The connector should at least return an error instead of an empty result.

The excel is about 1MB, the table we fetch contains 900 rows and 40 columns. Pagination is enabled in the connector.

[manuelstgomes]

HI @bart_vermeersch 

The Excel connector is finicky in some areas and you're finding one. You're doing the right steps, but 1mb is not "that" big to run into issues. Indeed it should return an error, and that's an issue.

Just to check, when it returns zero, the ProjectID matches with data in the Excel correct? Can you check a run history that it returned 0 and see if, for some reason, the ProjectID had a strange or undefined value in it or a value that could not be in the excel? 

Would SharePoint be an option to store and manage this data? Migration would be easy and SharePoint is much more stable to deal with growing volumes of data. I understand that teams want to work in Excel, but it's just a suggestion. 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
​
Cheers
Manuel

[bart_vermeersch]

Hello Manuel,

SharePoint is not an option (the flow syncs data from SharePoint to an Excel). They are both needed in the current business process.

We have checked, the projectID matches the Excel. The exact same odata filter often does return the correct result.

Bart

[manuelstgomes]

Hi @bart_vermeersch 

If it matches then it's the connector acting up. You need to build probably a safeguard while running so that, when it's zero, trigger a warning so that someone can check the history and, if all is ok, run it again. 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
​
Cheers
Manuel

[yashag2255]

Hi @bart_vermeersch 

Can you disable the pagination setting and add 2000 to the top count field the in the list rows from excel action? 

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[bart_vermeersch]

Hello @yashag2255 

Disabling pagination and adding 2000 to the top count didn't help, it still returns wrong results under heavy load 😞

The returned results did change slightly from

{ "value":[]}

to

{ "@odata.context":"https://excelonline-ne.azconn-ne.p.azurewebsites.net/$metadata#drives('xxx')/Files('xxx')/Tables('xx...",

"value":[]}

Thank you anyway!