close

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

Hello Guys, How are you all? Hope You all Are Fine. Today We Are Going To learn about How to read SharePoint Online (Office365) Excel files in Python with Work or School Account in Python. So Here I am Explain to you all the possible Methods here.

Without wasting your time, Let’s start This Article.

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

  1. How to read SharePoint Online (Office365) Excel files in Python with Work or School Account?

    This section is straight from the github README.md using the ClientContext approach and gets you authenticated on your SharePoint server

  2. read SharePoint Online (Office365) Excel files in Python with Work or School Account

    This section is straight from the github README.md using the ClientContext approach and gets you authenticated on your SharePoint server

Method 1

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!

Method 2

To read the file from the command line, you can do the following:

curl -O -L --ntlm  --user username:password "https://yoursharepointsite.com/sites/documentsite/sites/documentsite/Documents/filename.xlsx"

The simplest method, for automating this with python, is based on request_nmtl:

conda install requests_ntlm --channel conda-forge

Code to download filename.xlsx from Sharepoint (python 3):

# Paste here the path to your file on sharepoint
url = 'https://yoursharepointsite.com/sites/documentsite/sites/documentsite/Documents/filename.xlsx'

import getpass

domain = 'ADMIN' # adapt to your domain to in which the user exists
user = getpass.getuser() 
pwd = getpass.getpass(prompt='What is your windows AD password?')

import requests
from requests_ntlm import HttpNtlmAuth
from urllib.parse import unquote
from pathlib import Path

filename = unquote(Path(url).name)

resp = requests.get(url, auth=HttpNtlmAuth(f'{domain}\\{user}', pwd ))
open(filename, 'wb').write(resp.content)

Summery

It’s all About this issue. Hope all Methods helped you a lot. Comment below Your thoughts and your queries. Also, Comment below which Method worked for you? Thank You.

Also, Read