Excel (Business) problems

[efialttes]

Hi all

I have defined a very simple Flow#1 that is executed manually from Sharepoint when I select an Excel file in a Library. I am using Excel (Business) operations to list the rows from a table.

When executing it an error message in displayed

Action 'List rows present in a table' failed

Fix It

If this error was temporary, you can try resubmitting the run and the issue may have been resolved.

Error type

This action failed because of an error in the connector.

Error Details

The error message from the service is below. This maybe useful for support:

BadGateway

This Excel (Business) action block is working fine in another flow (Flow#2) with the same connector. Also in Flow#1, both Excel (Business) and Sharepoint connectors, I am using the same corporate user. Can both connectors be used in the same flow? Is there any incompatibility between them?

Suggestions, welcome

Thank you in advance!

[efialttes]

Hi again

I think I have identified the problem

I have updated my flow by adding a new "Excel (Business) List rows present in table" action block.

I call this second action block "List rows present in table 2" and add it just before the first one. When executing it, the following result is displayed:

 

Both action blocks use the same connector, both of them target the same excel file, so the problem is not there. What's the difference between them?

 

In the one that works fine I select "File" input from the picker, so I navigate through the directories and choose the target file. I select "Table" input from the dropdown menu.

In the one that fails, I fill "File" input with Dynamic content. Also I fill "Table" input with a custom value.

Please also note the inputs on both action blocks are apparently the same:

 

 

The problem is, I want to "List rows present in a table" from the Excel file selected by the user on the SP Library, so I cannot implement the first "List rows present in a table" (the one that works); I need to the second "List rows present in a table" (the one currently failing).

Is this a bug? Cn you think of a possible workaround?

Suggestions, welcome

Thank you in advance!

[v-yuazh-msft · Accepted solution]

Hi @ efialttes,

Microsoft flow support to select a file from the picker in the "Excel (Business)" connector currently.

Choosing a dynamic content in the "File" field to select a file is not supported in Microsoft flow "Excel (Business)" connector currently.

Although the inputs on both action "List rows present in a table" blocks are apparently the same,the inputs on select a file from the picker is a friendly format of the file identifier,you couldn't choose a dynamic content in the "File" field to select a file.

If you want to choose a dynamic content in the "File" field to select a file,I afraid that there is no way to achieve your needs in Microsoft Flow currently.

If you would like this feature to be added in Microsoft Flow, please submit an idea to Flow Ideas Forum:

https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Regards,
Alice Zhang

[efialttes]

Thanks for your fast response, Alice

I've already posted a new idea. If someone reading it needs this new functionality, you can vote it here:

https://powerusers.microsoft.com/t5/Flow-Ideas/Dynamic-content-in-File-amp-Table-inputs-Excel-Busine...

[Anonymous]

What if you are using the file picker and still get the BadGateway?:

Thanks @v-yuazh-msft @efialttes

[KarimH]

This is not true. The Action "List rows present in a table" works fine with dynamic File Path. The problems you describe occurs only if you put your path together in the action itself. If you use a Variable that you define before the action it works fine.

[efialttes]

Thanx for sharing, @KarimH ! I will doublecheck it next week and if so, mark your workaround as 'Accepted solution' 

[samlasota]

@KarimH How did you initialize the variable FilePath? I see you used a split expression to generate the correct string but I am having trouble implementing this on my Logic app. Could you provide the method for obtaining the correct expression?

Many thanks,

-Sam

[KarimH]

Hi Samlasota,

here is the expression for the split action:

split(split(variables('Thumb'),'items%2f')[1],'%3fversion%3d')[0]

I initialized the variable Thumb in a previous action with the file property "Thumbnail large"

I hope this helps

Karim Hamed

[RachelRig]

Just want to say that I used KarimH's method and IT WORKED. First initialize a variable that gets the value of Thumbnail Large. The name of this variable (for me) was "varThumb". Then initialize another variable for the File using this expression as the value: 

split(split(variables('varThumb'),'items%2f')[1],'%3fversion%3d')[0]

Then use the List Rows action and set the File field to the FileID variable. The table value you see in the screenshot just came in automatically.

In my case, I have a variety of users across a large organization who will create these Excel files. I get the files copied to a SharePoint library, but then I need to extract the rows within and copy them to a SharePoint list. From there, on item creation, I trigger a Flow that sends approval tasks to various people based on the contents of the line item. I write the approval responses and comments back to the SharePoint list to create a comprehensive log of approvals. 

I can't have users simply submit items to a list form because there may be dozens or even hundreds at a time. The Excel files are generated from SQL data -- like a report they export to Excel. I needed a way to consolidate line items from all Excel files and then get approval on each. Not a great system, but this just made everything a whole lot easier. Thanks very much for posting, KarimH.

Details on the Apply to Each step which creates the SharePoint list items:

[Anonymous]

@RachelRig 

Hello I am trying to follow your flow. I ran into a problem at the List rows present in a table step. You said that the table populated automatically for you and I am not getting that option. Could you identify where I am going wrong?

[RachelRig]

@Anonymous  I replied to the email I got with your question but I'm not entirely sure that will make it back to you. So, I would just ask if you've got your Excel data formatted in a Table. The rows and columns must be in a Table named Table 1.

[Anonymous]

@RachelRig Thank you for the quick reply, yes my data is formatted in an Table

[Janneke123]

hi @RachelRig,

Thank you for offering this solution! I will try it out quickly. 

I have one more question related to your business case: my case is similar to yours but I have a standard Excel sheet containing different tables. From this, I always ONLY need to get Table1.

Power automate is reading all tables, and therefor runs in an error. Do you know how to make sure its always taking Table1 by default only?

thnx!

Janneke