How to store sharepoint list columns in an excel using Azure Data Factory

Question

How to store sharepoint list columns in an excel using Azure Data Factory

Mansi Yadav 60

We have a sharepoint list that has more than 50 fields and lots of rows. Currently I'm using Web activity where I'm passing /sites/site-id/lists/list-id/items? expand=fields. Thr first field is title, second is organization, etc. But in the output I'm getting fields name as title, field1, field2, etc. I don't know why it's not picking other column names . I'm also handling odata.next link.

Secondly, I want to store these fields in an excel by mapping the fields. How can we do this in adf?

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-06-13T09:52:34.2266667+00:00
@mansi yadav

Thanks for reaching out to Microsoft Q&A

To get the SharePoint list data retrieval and mapping to Excel in Azure Data Factory (ADF).

Understanding the Issue with Field Names:

The $expand=fields parameter in your OData query retrieves all fields, but SharePoint returns them in their internal order, not necessarily matching your list's display order.

To get the actual field names, you'll need to use the list's metadata.

Solution:

Retrieve List Schema (Metadata):

In ADF, create a separate Web activity to fetch the list schema using the following OData URL:

/_api/web/lists/GetByTitle('your-list-title')?$select=Fields&$expand=Fields($select=InternalName, Title)

This will return an array of objects containing the internal name (InternalName) and display name (Title) for each field.

Process Data with Field Mapping:

In your existing Web activity that retrieves the list items, modify the success pipeline to handle the received data.

Use a Data Flow activity to transform the data and create the field mapping.

Data Flow Activity: Source: Your Web activity output Sink: Excel dataset Transformation:

Add a new column named "FieldName" using an expression like this: lookup(split(activity('GetSchema').output.Fields[i()].InternalName, '/'), 1)

Rename the existing "fieldX" columns to the corresponding "FieldName" values.

Use a dynamic rename operation for each column.
Write to Excel:

Configure the Excel dataset to map the transformed data with the correct column names.

Explanation:

The first Web activity retrieves the list schema metadata, providing the actual field names.

The Data Flow activity uses a lookup function to extract the field name from the internal name using string manipulation.

Dynamic renaming ensures each column is renamed correctly based on the retrieved field name.

Consider pagination with $top and $skip parameters in the OData query if you have a large number of items.

Handle potential errors gracefully using error handling activities in ADF.

By following these steps, you'll retrieve SharePoint list data with proper field names and store it in an Excel file with accurate column mapping in Azure Data Factory.

Hope this helps. Do let us know if you any further queries.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-06-14T09:25:54.16+00:00
@Mansi Yadav

The error message "Exception of type 'Microsoft.IdentityModel.Tokens.AudienceUriValidationFailedException' was thrown." indicates an issue with Azure Data Factory (ADF) authentication to SharePoint. Here's how to troubleshoot and configure the Data Flow activity:

Troubleshooting Authentication Error:

Check Azure AD App Registration:

Ensure your ADF has access to SharePoint through a properly configured Azure AD app registration.

Verify the app registration's "API permissions" grant access to the SharePoint sites you're targeting.

Double-check that the "Redirect URI" in the app registration matches the endpoint used by ADF for authentication.

Managed Identity vs. Service Principal:

If using a Managed Identity for ADF, confirm it has the "Contributor" role on the SharePoint site.

If using a Service Principal, ensure it has the appropriate SharePoint permissions and is assigned to the ADF workspace.

Review ADF Linked Service:

Verify the ADF Linked Service configuration for SharePoint Online.

Check if the connection details (like site URL, client ID, and secret) are accurate.

Data Flow Activity Configuration:

Source:

Type: Select "Web" as the source type.

Connection: Choose the ADF Linked Service you created for SharePoint Online.

URL: Use the corrected URL for retrieving list schema:

hclo365.sharepoint.com/_api/web/lists/GetByTitle('Demo2')?$select=Fields&$expand=Fields($select=InternalName, Title)

Sink (Optional):

Type: Select the desired sink type for storing the processed data (e.g., Excel, Azure SQL Database).

Connection: Choose the appropriate ADF Linked Service for the sink.

Schema: Define the schema for the sink based on the output data from the transformations.

Field Mapping:

Source Schema: ADF automatically infers the schema from the source data (list schema).

Sink Schema: Map the source columns to the sink schema columns based on the desired output format.

Transformations (Optional):

Use data flow transformations within the activity to manipulate data before writing it to the sink (e.g., adding the "FieldName" column as described previously).

Refer to the official ADF documentation for detailed configuration steps for Web Activity and Data Flow Activity: https://learn.microsoft.com/en-us/azure/data-factory/control-flow-execute-data-flow-activity

Consider using a separate Web Activity for retrieving list items to avoid cluttering the schema retrieval activity.
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-06-18T09:19:19.4433333+00:00

@Mansi Yadav 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.

1 answer

Your answer

phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-06-18T09:19:19.4433333+00:00

@Mansi Yadav 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.

Answer 1

I'm getting user configuration issue:-

{"error_description":"Exception of type 'Microsoft.IdentityModel.Tokens.AudienceUriValidationFail edException' was thrown."}

I have used below url:-

siteurl/_api/web/lists/GetByTitle('your-list-title')?$select=Fields&$expand=Fields($select=InternalName, Title)

https://hclo365.sharepoint.com/sites/Test/Demo/_api/web/lists/GetByTitle('Demo2')?$select=Fields&$expand=Fields($select=InternalName, Title)

Site name : Test/Demo

And can you provide complete explanation for data flow activity basically the configuration what exactly I should pass in the source, sink and fiel mapping part

Share via

How to store sharepoint list columns in an excel using Azure Data Factory

1 answer

Your answer