Azure Data Factory - Copy SharePoint Files to Blob Storage - SharePoint file names contain #

Question

Hi,

I am setting up a pipeline in ADF to copy a load of files from a SharePoint Online site to Azure Blob Storage and I've got it working, except for the fact that it turns out a load of the files have the # character in their name, which means the call to SharePoint can't find the file.

The URL I'm using to retrieve the file from SharePoint is like:

https://myorganisation.sharepoint.com/sites/MySPOSite/_api/web/GetFileByServerRelativeUrl('/sites/BiMySPOSite/Shared%20Documents/Backup%20Files/Oct2024/Y%23skp621%5B202410270005%5D.log')/$value

Note is has 4 special characters URL encoded in it:

%20 = blank space

%23 = #

%5B = [

%5D = ]

Through testing I've narrowed it down to just the # causing the issue.

The file definitely exists but SharePoint returns the following error:

<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<m:code>-2130575338, Microsoft.SharePoint.SPException</m:code>
<m:message xml:lang="en-US">The file /sites/MySPOSite/Shared Documents/Backup Files/Oct2024/Y#skp621[202410270005].log does not exist.</m:message>
</m:error>

Before I resort to renaming all the files in SharePoint to remove the # I was just wondering whether someone might be able to save me from having to do so!

Kudos to a couple of YouTube videos that I found very helpful in getting this far as a newbie to ADF:

Extracting files from SharePoint: https://www.youtube.com/watch?v=FFfNu3cI-uw

Using the ForEach activity: https://www.youtube.com/watch?v=KsO2FHQdILs

Thanks,

Mark

Answer 1

@Mark Jones

Welcome to the Microsoft Q&A forum.

The issue you're facing is due to how SharePoint handles special characters, even when URL-encoded. While you're encoding the # to %23, SharePoint internally might be double-encoding or interpreting it differently, leading to the "file not found" error. Renaming the files is a last resort.

Please Check the below steps and confirm us

1. URL Encoding: Ensure that the # character is correctly URL encoded as %23. It seems like you've already done this, but double-checking might help.
2. Using SharePoint API: Instead of directly using the URL, you can try using the SharePoint REST API to search for files. This can sometimes bypass issues with special characters. For example:

      https://myorganisation.sharepoint.com/sites/MySPOSite/_api/web/GetFolderByServerRelativeUrl('/sites/MySPOSite/Shared Documents/Backup Files/Oct2024')/Files?$filter=Name eq 'Y#skp621[202410270005].log'
   

3. Power Automate: Use Power Automate (formerly Microsoft Flow) to rename the files temporarily, copy them to Azure Blob Storage, and then rename them back. This can be automated to handle multiple files.
4. Azure Function: Create an Azure Function that renames the files before copying them to Blob Storage. This can be integrated into your ADF pipeline.
5. Custom Script: Write a custom script (e.g., PowerShell, Python) to handle the file renaming and copying process. This script can be triggered within your ADF pipeline.
6. Check SharePoint Permissions: While less likely, double-check that the account used by your ADF pipeline has the necessary permissions to access the files in SharePoint, especially those with special characters in their names.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you