As suggested by Niels V try using the Office365-REST-Python-Client.
The client implements the Sharepoint REST API. Here’s an example of what you are trying to do:
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File
url="https://yoursharepointsite.com/sites/documentsite"
username="yourusername"
password = 'yourpassword'
relative_url="/sites/documentsite/Documents/filename.xlsx"
This section is straight from the github README.md using the ClientContext approach and gets you authenticated on your SharePoint server
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 "Web title: {0}".format(web.properties['Title'])
else:
print ctx_auth.get_last_error()
If you just want to download the file then using File.open_binary() all you need is:
filename="filename.xlsx"
with open(filename, 'wb') as output_file:
response = File.open_binary(ctx, relative_url)
output_file.write(response.content)
However if you want to analyze the contents of the file you can download the file to memory then directly use Pandas or your python ‘.xlsx’ tool of choice:
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)
You can take it from here. I hope this helps!