Transfer Files from SharePoint To Blob Storage with Azure Logic Apps

By:   |   Comments (37)   |   Related: > 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.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Koen Verbeeck Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, March 18, 2024 - 9:11:40 AM - Koen Verbeeck Back To Top (92083)
Furthermore, both seem to be on-premises tools. Where are you going to host them in your cloud environment? On a virtual machine? On a container?

Monday, March 18, 2024 - 9:10:06 AM - Koen Verbeeck Back To Top (92082)
Gs Richcopy 360 is about $50 for the standard edition. It will take you a very long time until you have consumed $50 with Azure Logic Apps when you're just copying simple SharePoint lists.
Syncback is a backup solution, at first glance it doesn't even support SharePoint Lists.
My personal opinion is to not make the solution more complex (from an architecture point of view) by adding 3rd party tools from unknown vendors into the mix, while there are solutions in Azure which are easy to use.

Saturday, March 16, 2024 - 5:36:56 AM - goria Back To Top (92079)
Instead of using logic apps to Transfer Files from SharePoint To Blob Storage, you can use a third-party tool like Gs Richcopy 360 or Syncback, both are easy, quick, and copy directly between the clouds.

Friday, February 16, 2024 - 3:50:00 AM - Koen Verbeeck Back To Top (91974)
Logic Apps have an Excel Online connector, where it seems it's possible to add rows (or worksheets) to an existing Excel file.
This probably won't have the best performance though, since it's row-by-row. Logic Apps are a workflow orchestrator, not an ELT tool.
ADF doesn't seem to support Excel as a destination. Personally, I'd look into an Azure Function.

Tuesday, February 13, 2024 - 10:15:15 PM - Vamshi Back To Top (91944)

Is there a way to copy CSV or JSON data to the SharePoint as Excel using adf or logicapp from ADLS2


Friday, July 7, 2023 - 3:09:04 AM - Koen Verbeeck Back To Top (91365)
Hi samyuktha,

do you see anything in the pop-up for the file identifier? Make sure the site address is correct.


Friday, June 30, 2023 - 3:55:40 AM - samyuktha Back To Top (91357)
i am following the exact steps as mentioned in this article but i am not getting shared documents folder in the list while selecting file identifier.Can you please tell me what could be the reason for it.I have full control permission to the sharepoint site but i am not the owner.

Thursday, March 9, 2023 - 5:22:36 AM - Randy JackSon Back To Top (90992)
Great info and I would share that you can use also Sharegate or Gs Richcopy 360 to easily, quickly, and directly copy from SharePoint/Onedrive to Azure Blob / AWS S3

Friday, March 3, 2023 - 2:29:27 AM - Koen Verbeeck Back To Top (90976)
Hi Thao,

is that the only error you get? Most of the time there's a bit more information.
Bad gateway can mean anything. Usually you get this message when Logic Apps gets a result it doesn't expect.


Thursday, March 2, 2023 - 10:15:41 AM - Thao Back To Top (90970)

I got this error "BadGateway More diagnostic information: x-ms-client-request-id is 'C95A7A61-17B7-4193-B5C9-E74DCA009131'." when working with file identifier, do you have any idea on possible solutions?

Thanks in advance.

Monday, May 2, 2022 - 9:21:25 AM - Koen Verbeeck Back To Top (90054)
I believe it will only copy the files, not the folders. Everything is dropped in the same blob container.
I think if you want to traverse all folders, you have to built in some recursive loop:

Friday, April 29, 2022 - 5:41:36 PM - Jose Flores Back To Top (90052)
Follow up question that I cant seem to find online. Does this only copy files or does it also copy folders? will it copy the same folder structure you have on Sharepoint or will the files all be under the container with no folders. I was able to get it to copy files using power apps but when I upload a folder to sharepoint it is not looking in that folder to copy the files. only the files I copy directly to the documents folder.

Friday, April 29, 2022 - 1:18:32 PM - Jose Flores Back To Top (90051)
Thank you for the update, I was able to configure it with dynamic content and finished the rest. Currently working on troubleshooting. I am getting 404 error File not found on the "Get File Content", cant figure out what part is wrong

Friday, April 29, 2022 - 5:13:16 AM - Koen Verbeeck Back To Top (90050)
Hi Jose,

you should specify a folder in the List Folder action. In the Get File Contents, you should configure it with dynamic content.


Thursday, April 28, 2022 - 4:38:28 PM - Jose Flores Back To Top (90049)

Currently stuck in the Get File Content part. For the *File Identifier it wont let me pick my Shared Documents folder, it makes me drill down to sub folders and select an actual file. For some reason it wont let me pick a folder when i click on the folder so I cant move on to the next step.

Tuesday, June 8, 2021 - 11:03:18 AM - Koen Verbeeck Back To Top (88822)
Hi sree,

I believe you have to use an self-hosted integration runtime to access the on-premises SharePoint.


Tuesday, June 8, 2021 - 7:58:35 AM - sree Back To Top (88820)
Hi koen,

How can connect to Sharepoint (not sharepoint online) from Azure to ingest via Data factory (ADF) incrementally everyday

Friday, April 9, 2021 - 12:15:00 AM - Manu Back To Top (88501)
Hi Koen,

Thanks for the link, solved my task.

Thursday, April 8, 2021 - 8:15:39 AM - Koen Verbeeck Back To Top (88497)
Hi Manu,
you can check out the following blog post:

Friday, April 2, 2021 - 2:09:35 AM - Manu Back To Top (88483)
Hi Koen,

Perfect place where I can solve my tasks.

could you suggest me how to pick only excel files among different file formats in SharePoint.

Tuesday, January 12, 2021 - 3:55:58 AM - Koen Verbeeck Back To Top (88032)
Hi Erick,

sure, that should be no problem.


Monday, January 11, 2021 - 9:07:58 AM - Erick Back To Top (88026)
This looks really useful to me! Should this also be possible if the Sharepoint and the Blob Storage are on different storage accounts?

Monday, October 12, 2020 - 1:52:23 PM - Koen Verbeeck Back To Top (86632)
Hi Roy,
you can read this tip about incremental file load in ADF to get some inspiriation:


Saturday, October 10, 2020 - 6:00:30 AM - roy Back To Top (86627)
how to store latest file in blob?

Tuesday, September 29, 2020 - 10:39:58 AM - Rency Back To Top (86551)
Thank you for the post , this saves my day

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 6, 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 3, 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 6, 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 5, 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?

get free sql tips
agree to terms