Read excel file in Dataframe from Share Point using python

Question

I have to read the excel files from share point. I need to use the default password as I have access to sharepoint.

import requests
from requests_negotiate_sspi import HttpNegotiateAuth
 
response = requests.get(
    r'https://sharepoint.io.com/site3516/COURM/Tools',
    auth=HttpNegotiateAuth(),
    verify=False
    )

print(response.status_code)

The code prints 200 as the status code.

I have followed the below link: https://qurios-it.de/2020/10/16/connecting-to-sharepoint-using-python/

but now I need to read excel file from the folder Tools into a data frame. How to go about.

Does this answer your question? [Pandas read\_csv from url](https://stackoverflow.com/questions/32400867/pandas-read-csv-from-url) — Iguananaut, Aug 31 '21 at 15:48

score 0 · Answer 1 · answered Sep 01 '21 at 09:50

#First of all, you need to import all the necessary libraries to allow you access SharePoint.

#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
import requests
import os
import glob
from pandas import DataFrame

#Then proceed to authenticate to your SharePoint account

#target url taken from sharepoint and credentials

url='https://sharepoint.io.com/site3516/COURM/Tools/'
username = 'johndoe@company.com'
password = 'johndoepass!'

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

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)

#Read path
path = os.getcwd()
xlsx_files = glob.glob(os.path.join(path, "*.xlsx"), recursive = True)

# Read each file in the folder into a DataFrame
for f in xlsx_files:
    df = pd.read_excel(f)
    
    print('Location:', f)
    print('File Name:', f.split("\\")[-1])
    
    print('Content:')
    display(df)
    print()

Will it run for those users who have windows Authentication access. User doesnt know the password to Sharepoint but have full access — Mamta Gupta, Sep 01 '21 at 10:40

Read excel file in Dataframe from Share Point using python

1 Answers1