Unable to read Excel from SharePoint using office365 with Python

Question

Using this answer, I am attempting to read an Excel document from SharePoint into a pandas dataframe. My code is as follows:

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.file import File 
import io
import pandas as pd

#target url taken from sharepoint and credentials
url = "https://name1.sharepoint.com/sites/name2/name3/name4.xlsx"
username = 'a.b@name1.com'
password = 'Pa55word'

ctx_auth = AuthenticationContext(url)
if ctx_auth.acquire_token_for_user(username, password):
  ctx = ClientContext(url, ctx_auth)
  web = ctx.web
  ctx.load(web)
  ctx.execute_query()
  print("Authentication successful")

response = File.open_binary(ctx, url)

#save data to BytesIO stream
bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0) #set file object to start

#read excel file and each sheet into pandas dataframe 
df = pd.read_excel(bytes_file_obj, sheetname = None)

When it gets to the ctx.execute_query() line, I get this error:

ClientRequestException: (None, None, '404 Client Error: Not Found for url: https://name1.sharepoint.com/sites/name2/name3/name4.xlsx/_api/Web')

I have tried different documents, but I get the same response.

In the answer linked at the beginning, the url has a cid parameter, which I am wondering if that is the problem. But I haven't been able to find a value to use as my cid, nor have I been able to find out what a cid is. I think the issue maybe in the url variable, but I'm not exactly sure how. Any suggestions much appreciated!

score 1 · Accepted Answer · answered Jun 25 '20 at 08:17

It seems that the provided url for context generation is incorrect. Please have a try following snnipet:

tenant_url= "https://{tenant}.sharepoint.com"
ctx_auth = AuthenticationContext(tenant_url)

site_url="https://{tenant}.sharepoint.com/sites/{yoursite}"

if ctx_auth.acquire_token_for_user("username","password"):
  request = ClientRequest(ctx_auth)
  options = RequestOptions("{0}/_api/web/".format(site_url))
  options.set_header('Accept', 'application/json')
  options.set_header('Content-Type', 'application/json')
  data = request.execute_request_direct(options)
  s = json.loads(data.content)
  web_title = s['Title']
  print("Web title: " + web_title)
else:
  print(ctx_auth.get_last_error())

Full code : https://github.com/kongmengfei/sharedproject/blob/master/PythonConsole-uploadfileSPO/PythonApplication2/ctx_auth_acquire_token.py

BR

Unable to read Excel from SharePoint using office365 with Python

1 Answers1