How to load csv files from Sharepoint portal to data lake in azure synapse

Question

I have a requirement to load csv files form a folder in our organization sharepoint portal into Data lake.
Any specific connector or config set up required to create such pipeline?

Answer 1

Hi @Bhoopathiraju Deepthi ,

Welcome to Microsoft Q&A forum and thanks for reaching out.

Could you please refer to below MS doc and see if that helps to achieve your requirement. Here is the MS doc: Copy file from SharePoint Online using Azure Data Factory

You can copy file from SharePoint Online by using Web activity to authenticate and grab access token from SPO, then passing to subsequent Copy activity to copy data with HTTP connector as source.

1. Follow the Prerequisites section to create AAD application and grant permission to SharePoint Online.
2. Create a Web Activity to get the access token from SharePoint Online:
   • URL: https://accounts.accesscontrol.windows.net/[Tenant-ID]/tokens/OAuth/2. Replace the tenant ID.
   • Method: POST
   • Headers:
     • Content-Type: application/x-www-form-urlencoded
   • Body: grant_type=client_credentials&client_id=[Client-ID]@[Tenant-ID]&client_secret=[Client-Secret]&resource=00000003-0000-0ff1-ce00-000000000000/[Tenant-Name].sharepoint.com@[Tenant-ID]. Replace the client ID, client secret, tenant ID and tenant name.
   Note: Set the Secure Output option to true in Web activity to prevent the token value from being logged in plain text. Any further activities that consume this
   value should have their Secure Input option set to true.
3. Chain with a Copy activity with HTTP connector as source to copy SharePoint Online file content:
   • HTTP linked service:
     i) Base URL: https://[site-url]/_api/web/GetFileByServerRelativeUrl('[relative-path-to-file]')/$value. Replace the site URL and relative path to file. Sample relative
     path to file as /sites/site2/Shared Documents/TestBook.xlsx.
     ii) Authentication type: Anonymous (to use the Bearer token configured in copy activity source later)
   • Dataset: choose the format you want. To copy file as-is, select "Binary" type.
   • Copy activity source:
     i) Request method: GET
     ii) Additional header: use the following expression@{concat('Authorization: Bearer ', activity('<Web-activity-name>').output.access_token)}, which uses the Bearer token generated by the upstream Web activity as authorization header. Replace the Web activity name.
   • Configure the copy activity sink as usual.

ADF does not directly support copying a folder/multiple files from SharePoint Online, in case if there is a requirement, here are workarounds to achieve it. For detailed explanation please refer to this article: SharePoint Online Multiple Files (Folder) Copy with Http Connector

Hope this helps.

----------

• Please accept an answer and up-vote if it helps. Original posters help the community find answers faster by identifying the correct answer. Here is how.
• Want a reminder to come back and check responses? Here is how to subscribe to a notification.