How can i retrieve the files inside the folder in the sharepoint to Synapse

Question

How can i retrieve the excel files inside the sharepoint folder to the synapse

What dataset should i use to achieve that?

Answer 1

Please refer the below MSFT Doc:

https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory#copy-file-from-sharepoint-online

You can copy the file using binary format with HTTP connector as the source

Answer 2

Hey Darryl,

No worries, it’s not as scary as it sounds just a bit of Azure magic and some elbow grease.

First, you’ll need to use a Linked Service in Synapse to connect to your SharePoint. Think of it like giving Synapse the keys to your SharePoint house. You can set this up in the Synapse Studio under 'Manage' -> 'Linked Services' Just pick SharePoint Online as your source and fill in the details (URL, credentials, etc.).

Once that’s done, you’ll use a Dataset to point to the specific folder in SharePoint where your Excel files live. Create a new dataset, choose SharePoint as the source, and configure it to point to your folder. It’s like telling Synapse, “Hey, the files are over here, in this drawer.”

Now, to actually move the data, you’ll use a Pipeline. Create a new pipeline, add a “Copy Data” activity, and configure it to pull the Excel files from your SharePoint dataset into Synapse. You can land the data in a SQL pool, a data lake, or wherever makes sense for your workflow.

O, and don’t forget to handle the file format! Synapse can read Excel files, but you might need to tweak the settings to make sure it understands the structure. If the files are massive, consider breaking them down or converting them to CSV for smoother processing.

And hey, if you hit any snags, just remember: SharePoint and Synapse are like that couple who argue sometimes but still get the job done. You’ll figure it out!

Rgds,

Alex