Process CSV file uploaded SharePoint from MS Forms in ADF Pipeline

Question

Process CSV file uploaded SharePoint from MS Forms in ADF Pipeline

Steve Dunker 40

I need to be able to upload a file along with some questions, then run a ADF Pipeline that creates a new file based on the csv uploaded and the answers to the questions. My first thought was to leverage Azure Blog Storage to get the files and pass parameters for the specific file and answers to the questions. I have the pipeline and dataflow created and works great. Unfortunately, I can't find a way to get the file from the Forms location in One Drive for Biz or SharePoint to Blob storage. So I thought I would try a different direction such as directly accessing the file from SharePoint. I cant figure out the right way to connect the dataset to the file. Keeping in mind I will be passing in a different filename each time the pipeline runs. Any ideas would be appreciated.

Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-01-16T05:23:11.4266667+00:00

@Steve Dunker Just checking in to see if the below answer provided by @Amira Bedhiafi helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

2 answers

Your answer

Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-01-16T05:23:11.4266667+00:00

@Steve Dunker Just checking in to see if the below answer provided by @Amira Bedhiafi helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Amira Bedhiafi 41,111 Volunteer Moderator

Based on this old thread , 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. More links : https://learn.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list?tabs=data-factory#copy-file-from-sharepoint-online https://techcommunity.microsoft.com/t5/azure-data-factory-blog/sharepoint-online-multiple-files-folder-copy-with-http-connector/ba-p/2480426

Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-01-17T04:52:02.05+00:00

@Steve Dunker We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Steve Dunker 40 Reputation points

2024-01-17T19:06:16.7366667+00:00

Ok I have been trying to implement this and think it is in the right direction, however I am submitting the file via MS Forms in a Sharepoint Channel. My new problem is finding the SharePoint folder is where the files are being stored in order to create the linked service. Unless the linked service just needs to be at the group level? Only issue with that is that I don't see file in the documents folders.
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-01-19T08:04:01.5+00:00

@Steve Dunker You can create a linked service to the SharePoint site where the Forms files are being stored. You can use the SharePoint Online connector in Azure Data Factory to create the linked service. Once you have created the linked service, you can use the Copy activity to copy the file from SharePoint to Blob storage. You can pass the file name as a parameter to the Copy activity so that it can copy the specific file that you need. Here's the documentation on how to create a linked service to SharePoint Online: https://docs.microsoft.com/en-us/azure/data-factory/connector-sharepoint-online-list#linked-service-properties.
Steve Dunker 40 Reputation points

2024-01-26T21:39:09.1466667+00:00
Ok been trying to get this to work and I believe this is an issue with things being called different things during the setup of the App Registration and the Linked Service. I tried to setup the App Registration based on this link

https://learn.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app

From that setup I am not clear if I should setup a Secret Key, but did. Then not sure if I should use the value or the secret for the App Key. I actually have tried both and appear to have made more progress with the value. I am now getting this error

Failed to get metadata of odata service, please check if service url and credential is correct and your application has permission to the resource. Expected status code: 200, actual status code: Unauthorized, response is : 401 UNAUTHORIZED. Activity ID: 164b29b8-4e87-45e1-ad34-af9fd68f5163

When setting up the permissions in SharePoint I did it at the root of the SharePoint Site. Did I need anything from that for the Linked Service to be setup? When setting up the Linked Service I wasn't completely sure what to put for the redirect url. I put the complete url to the folder containing the files I want to process. Is that correct

https://XXXXXXXX.sharepoint.com/sites/PerformanceIndoorTraining/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2FXXXXXXXXXXX%2FShared%20Documents%2FApps%2FMicrosoft%20Forms%2FCreate%20Assignr%20Schedule%20File%2FQuestion&viewid=c94a165e%2Dd59e%2D4ad6%2Dac22%2D41c4fbab7d46

or should I have put something else?

Answer 2

Smaran Thoomu 32,525 Microsoft External Staff Moderator

Hi @Steve Dunker

It looks like the error message is indicating that the credentials or permissions are not correct. Here are some things to check:

Make sure that the credentials you are using to authenticate to SharePoint are correct. You can test this by trying to access the SharePoint site directly using the same credentials.
Make sure that the App Registration has the correct permissions to access the SharePoint site. You can check this by going to the App Registration in the Azure portal and checking the API permissions.
Make sure that the redirect URL in the App Registration matches the redirect URL in the Linked Service. The redirect URL should be the URL of the page that the user is redirected to after they have authenticated.
Make sure that the Linked Service is configured correctly. You can check this by going to the Linked Service in the Azure portal and checking the properties.

Regarding the redirect URL, it should be the URL of the page that the user is redirected to after they have authenticated. In your case, it looks like the URL you provided is the URL of the SharePoint folder containing the files you want to process. This may not be the correct URL to use as the redirect URL. You should check the documentation for the SharePoint Online connector to see what the correct format for the redirect URL is.

Steve Dunker 40 Reputation points

2024-01-29T16:44:47.9633333+00:00

Alright making progress. I cleaned up the permissions, redirection and had to generate a new secret but now the SharePoint Online list is not giving me an error message.

New problem is what is the right option for the list name? I selected "documents" and then clicked Preview Data and got nothing back. What would be the correct list for the List name?
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-01-30T10:22:06.86+00:00

Steve Dunker The correct list name depends on the specific SharePoint site and list that you are trying to access. You can find the list name by going to the SharePoint site and looking at the URL of the list. The list name should be the last part of the URL after the "/Lists/" segment. For example, if the URL of the list is "https://contoso.sharepoint.com/sites/HR/Lists/EmployeeData", then the list name would be "EmployeeData".

Once you have the correct list name, you can enter it in the Linked Service configuration in Azure Data Factory. Make sure that you also enter the correct SharePoint site URL and the credentials for the App Registration that you created. You can then use the Copy activity to copy data from the SharePoint list to Blob storage.
Steve Dunker 40 Reputation points

2024-01-30T16:46:54.16+00:00

Ok my URL does not have lists in it. The documents are being stored via a form in Teams. Below is a snippet of the url with identifying info modified.

XXXXXXX.sharepoint.com/sites/XXXXXXX/Shared%20Documents/Forms/AllItems.aspx?id=%2Fsites%2FXXXXXXX%2FShared%20Documents%2FApps%2FMicrosoft%20Forms%2FCreate%20Assignr%20Schedule%20File%2FQuestion&viewid=c94a165e%2Dd59e%2D4ad6%2Dac22%2D41c4fbab7d46

Is there a different way to get to the file that I am not aware of?
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-01-30T18:28:25.59+00:00
@Steve Dunker Based on the URL you provided, it looks like the file is being stored in the "Shared Documents" library of the SharePoint site. You can try using "Shared Documents" as the list name in the Linked Service configuration. Here's an example of what the Linked Service configuration might look like:

{ "name": "SharePointOnlineLinkedService", "type": "Microsoft.SharePointOnline", "typeProperties": { "siteUrl": "https://XXXXXXX.sharepoint.com/sites/XXXXXXX", "authenticationType": "OAuth", "clientId": "<your-client-id>", "clientSecret": { "type": "SecureString", "value": "<your-client-secret>" }, "tenantId": "<your-tenant-id>", "listName": "Shared Documents" } }

Make sure that you replace the placeholders with the correct values for your environment. Once you have configured the Linked Service, you can use the Copy activity to copy data from the SharePoint list to Blob storage.

Steve Dunker 40

Ok, I am using the UI to configure the SharePoint Online List and as you can see from the images my options are Documents and there is nothing for Shared documents.

User's image

This is the configuration of the linked service, obviously different from what you have.

{
    "name": "AssignrInputFiles",
    "type": "Microsoft.DataFactory/factories/linkedservices",
    "properties": {
        "annotations": [],
        "type": "SharePointOnlineList",
        "typeProperties": {
            "siteUrl": "https://xxxxxx.sharepoint.com/",
            "tenantId": "xxxxxxx-4054-48b6-9bb4-8b5eaa206253",
            "servicePrincipalId": "xxxxx-2ddb-41ed-9cf7-168c2cc8cd83",
            "encryptedCredential": "xxxxxxxCIyMDE3LTExLTMwIiwNCiAgIlByb3RlY3Rpb25Nb2RlIjogIktleSIsDQogICJTZWNyZXRDb250ZW50VHlwZSI6ICJQbGFpbnRleHQiLA0KICAiQ3JlZGVudGlhbElkIjogIkRBVEFGQUNUT1JZQDE2OTRGRTE2LTQ2NjUtNEFCQi05NTQ5LThFRkU3QTlDRjBDNV84OWI3NWEwZC0zNDc2LTRkYzUtYmY4Ny04YjFjMGI0MjE1ZGMiDQp9"
        }
    }
}

Even the code for the SPO List datasouce doesn't look like yours.

{
    "name": "SharePointOnlineListResource1",
    "properties": {
        "linkedServiceName": {
            "referenceName": "AssignrInputFiles",
            "type": "LinkedServiceReference"
        },
        "annotations": [],
        "type": "SharePointOnlineListResource",
        "schema": [],
        "typeProperties": {
            "listName": "Documents"
        }
    }
}

Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-02-02T13:49:43.7433333+00:00

Steve Dunker I agree that this issue looks strange, and I wasn't able to reproduce this issue. If you have a support plan, could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.
Steve Dunker 40 Reputation points

2024-02-07T17:53:16.4666667+00:00

Unfortunately I do not have a support plan. So your help to figure out why my configuration setting are different would be greatly appreciated. I really need to use this design pattern for quite a few ADF data flows.
Smaran Thoomu 32,525 Reputation points Microsoft External Staff Moderator

2024-02-09T11:07:41.47+00:00

I understand your frustration and the need to find a solution for your specific situation. While I can't offer direct troubleshooting without a support plan, I can suggest some alternative approaches and potential explanations for the differences you're encountering.
Steve Dunker 40 Reputation points

2024-02-09T21:50:09.9933333+00:00
Ok here is the entire problem I am trying to solve. I need to take accept a file (csv or xlsx) and then kick off an ADF pipeline. Once the pipeline is complete then provide a way for the end user to download the file.

My initial approach is to use MS Forms to get the file, I switched from user forms to using Teams forms so the file was initially in OneDrive to SharePoint. I was then going to use PowerAutomate to move the file to Azure Blob Storage and launch the pipeline. This was before I knew you could directly connect to the file ADF (obviously that isn't working right now). Hadn't quite worked out the download side of things but my thought was to put it on a SharePoint location to download. This is all contained within my o365 org.

What I currently have working

MS Form that accepts the file

Power Automate that will launch the pipeline when the form is submitted

ADF Pipeline that accepts parameters and processes a file from azure blob storage

If you have better approach or just a different approach that solves my problem I am open to it. Please keep in mind I am one man operation with a vast breadth of knowledge but depth in these areas especially SharePoint is my challenge.

Share via

Process CSV file uploaded SharePoint from MS Forms in ADF Pipeline

2 answers

Your answer