Forum Discussion
How to access SharePoint Lists Attachment in Fabric
Hi,
I was thinking if I have a list in which I have multiple attachments within different or same row items, how I can access this using Fabric, I mean creating any DataFlow or something such that I can store it in the Lakehouse or so.
I see. If I understand correctly, you basically just want to store a copy of the files somewhere.
I guess you can use Notebook or Data Pipeline to copy the files into a files folder in a Lakehouse.
Or you could create a folder for each list item, and insert the files which are attached to each list item. (automatically).
I don't know the code for doing that, and unfortunately I don't have the time to test that now, but it sounds like something which is doable.
---
It seems maybe you will need to create an app registration (service principal) in Azure and give this service principal access to the Sharepoint list, and then use the service principal to authenticate to the Sharepoint list attachments from either a Notebook or Data pipeline.
Perhaps this documentation will help:
Working with folders and files with REST | Microsoft Learn
I am guessing use GET method to get each attachment file from Sharepoint via API, and then have the Notebook or Data pipeline save the file in a folder in Fabric Lakehouse.
---
If a low code approach is preferred, I am thinking maybe Power Automate could be an alternative way of extracting the attachments from the list:
How to store all attachments from a SharePoint list to one folder? - Microsoft Q&A
But per my knowledge there are no Fabric connectors in Power Automate. So if the aim is to store the files in Fabric, I think it would need to use some API: Solved: Automate file movement lakehouse > onderive - Microsoft Fabric Community
If going ahead with Power Automate, maybe the easiest is to just store the attachment files in another folder in Sharepoint which is "visible" in the Sharepoint document hierarchy (as opposed to the list attachments folders, which seem to be "hidden" in Sharepoint).
9 Replies
What is the file format of the attachments? (I.e. is it csv, json, excel, image files, word, etc.?)
I guess Data pipeline, notebook or dataflow gen2 could be relevant, depending on the file format of the SharePoint attachment, and whether you want to store it as table in Lakehouse or in files in Lakehouse?
Is this a one time job, or something you want to run automatically e.g. every day?
(Will the number of rows and number of attachments increase?)
I would look for SharePoint list connector or SharePoint folder connector.
Or I would try to do REST API or Web activity.
Depending on the tool you wish/need to use, I guess there are different ways of accessing the attachments (browsing or use folder path or file path).
frithjof_v These files will be financial files and will be in excel or csv format, there can be multiple files within each row item of the list, and this needs to be refreshed daily, the data within the attachment can change, even the attachment can be refreshed as per the requirement, Is it really possible to read list items in fabric, if yes please guide me how?
Unfortunately, I don't have the time to test it now. Hopefully someone else can help.
However, my main questions to guide hopefully a solution would be:
What will be the final goal (reason why you want to get the files into Fabric Lakehouse)? Are you planning to make one (or more) Power BI reports based on the file content?
Do you want to combine multiple SharePoint attachment files into one Table in Fabric Lakehouse?
Or you want to save each file as a separate Table in the Lakehouse?
Or you want to save each file as a separate file in Lakehouse? (Purely copy the files from SharePoint to Fabric).
Do all the files have similar content inside the file? (Identical column names, etc.)
Or does each file have different layout inside the file?
Depending on the tool (dataflow gen2, data pipeline, notebook) I guess there can be different ways to access attachment files.
However the choice of method and tool depends on what you want to achieve as the final product.
E.g. with the dataflows gen2 tool, I think you could use one of the SharePoint connectors (list or folder):
https://learn.microsoft.com/en-us/fabric/data-factory/dataflow-support#s
By using the SharePoint online list connector, I think it can be possible to navigate to find the attachment. Maybe also create a custom function to find the attachment for each list item, if necessary.
You could probably also use the SharePoint Folder connector to open the folder which contains all the attachments. I'm not sure what the path to the attachments folder is, but you would need to find the path to the attachments folder, maybe something like this combined with this: .../Lists/<ListName>/Attachments/
https://learn.microsoft.com/en-us/fabric/data-factory/connector-sharepoint-folder
https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder#determine-the-site-url
https://learn.microsoft.com/en-us/power-query/connectors/folder
https://learn.microsoft.com/en-us/power-query/combine-files-overview
