Power automate with teams excel files

[Andym1981]

Hi, 

Has anyone managed to use power automate to grab excel row data from a file sat in a teams group (files tab) and dump those results in to an SQL table?

I am really struggling to find any examples/sources on this. 

Ps - new user to flows so really need the basics explained. 

Thankyou 

[trice602]

Hi @Andym1981 ,

This is a pretty quick flow and most likely the biggest challenge for you is going to be connecting to your SQL data source, although it is not overly complex but if this is a company SQL database, you will need to jump through the I/T security protocols established by your organization.  That said, here's the flow.

So remember, when you say excel files stored in the Files tab in Teams, all that really means is excel files stored in SharePoint.  That's because Teams uses SharePoint as the file storage.  So in this example, I press a button to start my flow, let's say I want to run it once a day on-demand.  Next I use the action List rows present in a table, and finally use the action insert row (V2) to add these rows to my SQL table.

In my List rows present in a table action, here's the parameters that point to the "teams file tab".  Key point, remember Excel data lives in a table, unless it is renamed or more than one, Table1 is the default.

Next, I will Insert row (V2) in my SQL table.  When I select the content from List rows present in a table, this action will wrap itself in a for each action because...

"For each row in excel, add to SQL table".  Then for each column in SQL, you reference the dynamic content from Excel you want to add (I added testing to the DOB column as an example).

And these are the basic steps. 

[Andym1981]

Amazing thank you, I will try this tomorrow and report back, really appreciate you taking the time to explain this! 

[trice602]

Hi @Andym1981 ,

Great to hear; always glad to help! 

[andymfoster]

Hi @trice602

I note you mention an on-demand flow, I would ideally like an automate daily/weekly extract of the data from the excel file in sharepoint. I was looking at flow options, there is one that says 'When an item is created or modified' with 'sharepoint' shown underneath, would that be the most suitable option to use/configure?  

I have this so far, after selecting 'insert row (V2) it gives me this SQL Server flow, which is nothing like yours above. 

Out of interest I tried to set this up anyway, but get really stuck with a gateway option it wants

The database is held on a server where I believe the gateway we use is not.

I feel like I have the wrong option. 

Options I see:

[trice602]

Hi @Andym1981 / @andymfoster ,

You can use a scheduled trigger, such as this example, where here I run daily at 5 AM M-F.  This is completely configurable, and you can customize to your needs.

On the SQL action and connection:  unless you have already connecting to a SQL server, my recommendation is to get the information you need from your I/T department resource ~ it may be very, very difficult to walk you through your organization's connection string, security requirements, and more which is why I didn't go into those details.  It's really not overly complex, you just need the person responsible for your DB to provide the information to you.

That said, this is the appropriate action to connect to an SQL database.  Your screen looks different because in mine, I have already connected to the database and I am looking at the columns in my table.  You have to connect first to see that database table.

[andymfoster]

Thanks @trice602 

Fortunately I work in the team who administer the server/database (Also ex IT so pretty familiar with server related details)

The screenshot below is with the server and database completed and it's just the gateway issue, I assume it would be automatically detected if the connection worked, I am quite certain the gateway is not sat on the same server as the database is installed though, it's actually on a different virtual machine so perhaps the obstacle to overcome is now to install a new gateway on this server. 

When I choose 'New on-premises data gateway' the install file appears to download, or I can grab it from this URL it seems as it's been a long time since we installed any gateway. 

https://learn.microsoft.com/en-us/data-integration/gateway/service-gateway-install