Error 403 when copying Excel file from Sharepoint (other files work)

Question

Hi!

I am following the tutorial:

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

Data Factory fails to load the Excel file from SharePoint. I get following error when I want to choose a sheet name in the Dataset:
"One or more errors occurred. The remote server returned an error: (403) Forbidden."

I can copy PDF files with the same pipeline from the same SharePoint when I change the Datasets. Only Excel files don't seem to work.

Any suggestions?

Best regards

Answer 1

Hello @Kazer ,

Welcome to the Microsoft Q&A platform.

I am assuming that you are encountering the below issue after entering the Sheet Name and Clicking OK

Error Text

One or more errors occurred.\r\nFailed to read data from http server. Check the error from http server：The remote server returned an error: (403) Forbidden.

If this is not the case, can you please add some screenshot or context to the question?

Alternatively, if this is your case, the error is occurring because ADF is trying to read the schema by connecting to the file. When you enter the Sheet Name - The Import Schema section automatically changes to the From Connection/Store - in turn tries to read the file without any token.

However, at this stage - we do not have a token - do not have the output of the Web activity. This setting is just for the ADF to understand the Schema of the file. I had set it to None at my end

You could one of the following at your end :

• Set the Import Schema to None or from a Sample File
  

If you set it to from a Sample File - you will have to upload a sample file that will resemble the file in Sharepoint Online Manually.

• You could generate a token to access the file and Hardcode the Authorization Header and continue to use the From Connection/Store

Alternatively, you could also consider copying the file as is binary type.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification