cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
efialttes
Super User
Super User

Excel (Business) problems

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

Excel (Business) connector Problems.png

 

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



1 ACCEPTED SOLUTION

Accepted Solutions

Hi @ ,

 

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

View solution in original post

14 REPLIES 14
efialttes
Super User
Super User

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:

 Excel (Business) connector Problems 3.png

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?

 

Excel (Business) connector Problems 2.png 

 

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:

 

 Excel (Business) connector Problems 4.png

 

 Excel (Business) connector Problems 5.png

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!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi @ ,

 

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

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...



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Anonymous
Not applicable

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

 

Thanks @v-yuazh-msft @efialttes

KarimH
Advocate I
Advocate I

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.

FlowExcel1.pngFlowExcel2.png

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

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



@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

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"

thumb.png

 

I hope this helps

 

Karim Hamed

RachelRig
Kudo Collector
Kudo Collector

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.
 
Excel-FileID.PNG
 
Details on the Apply to Each step which creates the SharePoint list items:
 
Excel-FileID-2.PNG
Anonymous
Not applicable

@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?

Capture1121.PNG

 

 

@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.

RachelRig_0-1605204940090.png

 

Anonymous
Not applicable

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

Janneke123
Frequent Visitor

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

Rushil
Frequent Visitor

Hi All,

 

I'm facing one issue while getting data out of Excel Online - "List all rows present in excel". I have 3 columns from excel which are coming out to be null but instead they do have values present with those particular columns. You can even see the values in the output of Excel Online - 

Rushil_0-1660755472058.png

I'm trying to get the values of these columns into compose but it's showing empty or null.

Rushil_1-1660755529619.png

int(outputs('Compose')) - using this to convert the value to integer.

 

Output of Compose is showing as null.

Rushil_0-1660756376970.png

 

Error;

Rushil_2-1660755649039.png

 


Can anyone please help me with this issue?

Thanks in advance 🙂

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (4,330)