Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

By:   |   Updated: 2019-02-05   |   Comments (12)   |   Related: More > Azure


I have a number of Excel files in a SharePoint library. I want to load the data from those files into my database in Azure. I tried using Azure Data Factory, but SharePoint isn't listed as one of the sources. What are my options to get the data out of SharePoint?


At the moment, SharePoint is not supported as a data source in Azure Data Factory (ADF), the cloud-based data integration service by Microsoft. It is not listed as a supported data store/format for the Copy Activity, nor is it listed as one of the possible connectors. If we want to read the data from the Excel files and write it to an Azure SQL database, we need to explore other options. Luckily, there are many:

In this tip, we'll take a look at the last option. Logic Apps allow you to create serverless workflows that automate the integration between various services without writing a single line of code. Luckily, Logic Apps can connect to SharePoint, and many other sources as well, such as BizTalk, Office 365, Box, OneDrive, Power BI, Teradata and so on. The list is over 200 items long! The example in this tip uses SharePoint as a source, but Logic Apps can handle many different scenarios.

Creating a Logic App

The goal is to read data from an Excel file stored in a SharePoint document library. The sample data looks like this (it's retrieved from the employee dimension of the AdventureWorks data warehouse):

customer alternate key

In the Azure Portal, click on Create a Resource.

microsoft azure

Search for Logic Apps.

logic app

Click on the Create button to add a new Logic App.

logic app

Choose a name, a subscription, a resource group and a location.

logic app designer

When you go to the newly created Logic App, the designer will open. You can start with a blank workflow, or you can choose between any of the existing templates. If you start with a blank flow, you need to choose a trigger. The trigger will specify when the Logic App will run. In this example, we'll choose a HTTP request, so we can run the app on demand.

logic app designer

After choosing the trigger, the design canvas will be opened, with the trigger added to the top:

logic apps designer

Now we need to add a step to the workflow, which will be executed once the trigger has been called. Simply click on New step. A step will be added and you need to choose the action the step will perform:

choose an action

Search for SharePoint and in the resulting list, choose the List Folder action. This action will list all of the items in the document library. We can then use this output to loop over the items and read them their contents.


The next step is to sign into your SharePoint environment. If you have an on-premises SharePoint, you can connect through a data gateway (more info can be found in the tip Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1).


A pop-up will come where you can enter your credentials. It's possible this pop-up is blocked in some browsers. After signing in, you'll get the following window where you can configure your action:

list folder

If you click the first dropdown, you'll get a list of SharePoint sites you can connect to. Once you select one, you can click on the folder icon to browse to the desired library:

list folder sharepoint

Click on the arrows on the right to go to a subfolder, or on the folder itself to select it. Click on New step to add a new subsequent step. In the new step, choose SharePoint again as the connector. Then, select Get File Content. In the File Identifier, we want to use the identifiers of all the files in the folder, found by the List Folder step. To do this, select Add dynamic content. This allows you to use values/objects returned by previous steps.

get file content

In the list, select Id (the unique id of the file). The Logic Apps designer is smart enough to detect that you can actually have multiple files in the folder, so it's going to wrap the Get File Content step in a For each loop.

get file content

Don't forget to select a SharePoint site as well, which obviously needs to be the same site as in the List Folder step. The final step will write the contents of the file to Azure Blob storage (configuration of blob storage is out of scope for this tip, but examples can be found in the tips Customized Setup for the Azure-SSIS Integration Runtime or Copying SQL Server Backup Files to Azure Blob Storage with AzCopy). Click on Add an action. In the action screen, search for blob, choose Azure Blob Storage and then select Create blob.

create blob

If you have multiple storage accounts, choose the one you need for your setup. Give the connection a name.

create blob

Now you can configure the action to create the blob. You can use dynamic content to use the same file name. For the blob content, you need to choose File Content from the dynamic content.

create blob

Testing the Logic App

The logic app is now finished. Click on the save icon to save your work. Once the app is saved, you can run it from the designer.

logic apps designer

The logic app will then run, check if there are files in the SharePoint folder and then copy them to the Azure Blob container.

list folder

In the blob container, we can verify the Excel file is present:

logic app


In this we showed how you can easily create a workflow with Azure Logic Apps that transfers data from SharePoint (there are many other connectors available as well) to Azure Blob Storage, without writing a single line of code. The app created in this tip can be called by an HTTP request, but there are other types of triggers as well, such as when a file arrives in a folder.

If you've worked with Microsoft Flow before, creating Logic Apps will be very familiar. In fact, Flow is built upon Logic Apps and they share the same designer and connectors.

Next Steps
  • Try it out yourself. You can try to automate some of your tasks or replace some old SSIS packages with native cloud functionality. You can also incorporate the logic app into an Azure Data Factory pipeline by using the HTTP request.
  • An interesting article comparing Flow, Logic Apps, Webjobs and Azure Functions.
  • You can find more Azure tips in this overview.

Last Updated: 2019-02-05

get scripts

next tip button

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips
Related Resources

Comments For This Article

Friday, June 26, 2020 - 6:40:12 AM - Koen Verbeeck Back To Top (86052)

Hi Ratan,

I would try to filter out the list from the objects returned by the "List Folder" action. Maybe "Filter Array" from data operations can help.


Thursday, June 25, 2020 - 4:14:39 AM - Ratan Back To Top (86039)

Hi Koen,

We have a logic app setup which gets files from sharepoint and moves to azure blob. Trigger successfully moves files but flag shows error. For some reason in foreach Get File Content is trying to treat listname as file and trying to move the list and we getting error of status code =404. Please advice.


Monday, April 06, 2020 - 4:31:46 AM - Koen Verbeeck Back To Top (85292)

Hi Tyler,

I checked the logic app and the SharePoint connector has a "get all lists and libraries" action. Maybe you can use that one to get all folders, filter out the ones you want. Then put the solution described in this tip into a for each loop so you can loop over the folders you want and extract the files. In the tip I used "DisplayName", but there's also a Path variable.

Not 100% sure if this is going to work. At some point, if it becomes too complex for a Logic App, you might consider using an Azure Function instead where you have a lot of programming languages at your disposal (such as C#) and you'll have more flexibility.


Friday, April 03, 2020 - 4:26:07 PM - Tyler Back To Top (85271)

Hi Koen,

What design/actions would you recommend if the files were stored in many directories/folders?

Say I have 'Folder 1' > 'MyExcelFile1.xlsx', 'Folder 2' > 'MyExcelFile2.xlsx', etc, and I wanted to keep the same directory structure I have in SharePoint for Azure Blob Storage?



Friday, March 06, 2020 - 5:08:23 AM - Koen Verbeeck Back To Top (84967)

Hi Souffiane,

did you add the task 'get file content' before the 'create blob' task?

Thursday, March 05, 2020 - 10:39:30 AM - souffiane Back To Top (84953)

Hi, I dont see the file content in the dynamic content. is this a bug?

Tuesday, January 28, 2020 - 5:53:30 AM - Koen Verbeeck Back To Top (84009)

Hi Pramod,

Logic Apps are used in this to get files out of a SharePoint document library and move them to another location e.g. Azure Blob Storage, where they can be consumed more easily.
Personally, I wouldn't use Logic Apps to parse the Excel files, but rather use another tool such as SSIS. If it needs to be cloud, you can write a little Azure Function (in C# or Python for example) which extracts the worksheet data into a csv file.

You can probably do it in Logic Apps though, I see there's an Excel connector for Onedrive (Personal and Business) so you can maybe hook that one up to your SharePoint (since OneDrive for Business is basically SharePoint). There's an action to list the worksheets and an action to get data, so that's probably all you need.


Sunday, January 26, 2020 - 10:16:15 PM - Pramod Back To Top (83991)

Thank you very much for this article. It really helped a lot.

Can you also please explain how to pull only 1 particular tab from each excel among multiple excel files in SharePoint document library, then store it in a sql data base.

Monday, October 21, 2019 - 9:33:50 AM - Koen Verbeeck Back To Top (82843)

Hi edison,

does the 100MB error occur in SharePoint or in Logic Apps? If in SharePoint, you might need to speak with your admin to see if the restriction can be lifted. If in Logic Apps, I'm afraid there's not much you can do. Maybe split up the file if possible or compress it.


Sunday, October 20, 2019 - 11:55:18 PM - edison Back To Top (82839)

If the file is greater than 100MB, "get file content" activity will be failed due to the 100MB limit. Can you advise how to work around it?

Wednesday, February 13, 2019 - 9:16:29 AM - Koen Verbeeck Back To Top (79026)

Hi Kieran,

I assume so, since there is a "create file" SharePoint action.

Wednesday, February 13, 2019 - 7:18:25 AM - Kieran Kelly Back To Top (79024)

Can you do it the other way around, from Blob Storage to Sharepoint?


Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Process Blob Files Automatically using an Azure Function with Blob Trigger

Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets

Managing Azure Blueprints with PowerShell

get free sql tips
agree to terms

Learn more about SQL Server tools