How to read SharePoint Online (Office365) Excel files into Python specifically pandas with Work or School Account?

Question

The question is very similar to the link below. How to read SharePoint Online (Office365) Excel files in Python with Work or School Account?

Essentially I would like to import an excel file off SharePoint into pandas for further analysis.

The issue is when I run the code below I get the following error.

XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'\r\n<!DOCT'

My code:

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.file import File 

url = 'https://companyname.sharepoint.com/SitePages/Home.aspx'
username = '[email protected]'
password = 'password!'
relative_url = '/Shared%20Documents/Folder%20Number1/Folder%20Number2/Folder3/Folder%20Number%Four/Target_Excel_File_v4.xlsx?d=w8f97c2341898_random_numbers_and_letters_a065c12cbcsf=1&e=KXoU4s'


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()
  #this gives me a KeyError: 'Title'
  #print("Web title: {0}".format(web.properties['Title']))
  print('Authentication Successful')
else:
  print(ctx_auth.get_last_error())


import io
import pandas as pd

response = File.open_binary(ctx, relative_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 file into pandas dataframe
df = pd.read_excel(bytes_file_obj)

print(df)

PlagTag · Accepted Answer · 2020-09-08 11:55:21Z

22

For those of you that ended up like me here at this issue, I found that one has to path the full URL to File, not just the path:

#import all the libraries
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File 
import io
import pandas as pd

#target url taken from sharepoint and credentials
url = 'https://company.sharepoint.com/Shared%20Documents/Folder%20Number1/Folder%20Number2/Folder3/Folder%20Number4/Target_Excel_File_v4.xlsx?cid=_Random_letters_and_numbers-21dbf74c'
username = '[email protected]'
password = 'Password!'

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)

edited Sep 8, 2020 at 11:55

PlagTag

6,4996 gold badges41 silver badges49 bronze badges

answered Mar 28, 2019 at 14:53

Angel Roman

6381 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

zwornik Over a year ago

Are you using Anaconda? I am trying to install office365 library in Anaconda (anaconda.org/jholdom/py-o365) and it fails.

bugmenot123 Over a year ago

@papelr That's not what comments are for nor how they work. Post your problem as a new question.

bugmenot123 Over a year ago

I didn't report your comment. I added a comment to help you get an answer.

Dmitrij Holkin Over a year ago

i getting error here ctx.execute_query() and 404 Client Error: Not Found for url myurl & _api\web what is api/web? why it append it to my url?

Shashank Shekher Over a year ago

@Angel Roman : getting error like 'AADSTS53003: Access has been blocked by Conditional Access policies. The access policy does not allow token issuance'. Any comment on how to get this resolved?

|

PlagTag · Accepted Answer · 2025-04-11 14:35:30Z

9

Maybe worth to note that the official repository holds many examples on common operations for sharepoint, drive and teams:

https://github.com/vgrem/Office365-REST-Python-Client/blob/master/examples/sharepoint/files/download_file.py

One note for installation:

pip install Office365-REST-Python-Client

There is also a office365 package but the above one is the correct one

edited Apr 11 at 14:35

answered Sep 8, 2020 at 11:49

PlagTag

6,4996 gold badges41 silver badges49 bronze badges

2 Comments

Ajay Kumar Chawla Over a year ago

link is giving 404 error

user3503711 Mar 19 at 16:51

2025: Updated link: github.com/vgrem/Office365-REST-Python-Client/tree/master/…

milad bahari javan · Accepted Answer · 2022-12-05 21:03:07Z

3

The following works with Client ID and Secret Code (Lib: Office365)

# Credential to connect to your SP Site
SITE_URL ='https://XXXXXX.sharepoint.com/sites/yoursitename'
CLIENT_ID = 'xxxxxxxx-xxx-xxxx-xxxxxxxxxxxxxxxxx'
CLIENT_SECRET= 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

# Establish the connection
context = ClientContext(SITE_URL).with_credentials(ClientCredential(CLIENT_ID, CLIENT_SECRET))

response = File.open_binary(context, '/'.join(['/sites/yoursitename/Shared Documents/Work/OnlyFolderName', 
                                           'yourfilename.xlsx']))


df = pd.read_excel(io.BytesIO(response.content))

answered Dec 5, 2022 at 21:03

milad bahari javan

4165 silver badges7 bronze badges

Collectives™ on Stack Overflow

How to read SharePoint Online (Office365) Excel files into Python specifically pandas with Work or School Account?

3 Answers 3

6 Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

Comments

Linked

Related