How to read excel from SharePoint folder using python Pandas library

Question

I'm attempting to read an Excel file from SharePoint using the office365 library in Python. I've tried the following code:

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


url = 'https://company.sharepoint.com/:x:/r/sites/folder1/folder1/_layouts/15/Doc.aspx?sourcedoc=%random_letters_and_numbers%7D&file=excel%20file%20name.xlsx&action=default&mobileredirect=true'
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)

bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0) 
df = pd.read_excel(bytes_file_obj)

However, when I execute the code, I encounter the following error on the last line:

ValueError: File is not a recognized Excel file

Could someone please help me troubleshoot this issue? Is there something I'm missing or doing wrong in my code?

Thank you in advance for your assistance!

Try reading your byte stream and figuring out whether it is actually an Excel file being returned from the web. — ifly6
– ifly6, Commented Aug 24, 2023 at 2:55

christian heins - velero · Accepted Answer · 2023-10-12 11:13:27Z

I adjusted the code in the following links and works like a charm:

https://github.com/bashamsc/sharepoint_python/blob/main/sharepoint_read_file_python.py

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

#Importing required libraries

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

#Constrtucting SharePoint URL and credentials 

sharepoint_base_url = 'https://mycompany.sharepoint.com/sites/sharepointname/'
sharepoint_user = 'user'
sharepoint_password = 'pwd'
folder_in_sharepoint = '/sites/sharepointname/FirstFolder/'

#Constructing Details For Authenticating SharePoint

auth = AuthenticationContext(sharepoint_base_url)

auth.acquire_token_for_user(sharepoint_user, sharepoint_password)
ctx = ClientContext(sharepoint_base_url, auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
print('Connected to SharePoint: ',web.properties['Title'])

   
#Constructing Function for getting file details in SharePoint Folder

def folder_details(ctx, folder_in_sharepoint):
  folder = ctx.web.get_folder_by_server_relative_url(folder_in_sharepoint)
  fold_names = []
  sub_folders = folder.files 
  ctx.load(sub_folders)
  ctx.execute_query()
  for s_folder in sub_folders:
    fold_names.append(s_folder.properties["Name"])
 return fold_names
 
#Getting folder details

file_list = folder_details(ctx, folder_in_sharepoint)

#Printing list of files from sharepoint folder
print(file_list)

#Reading File from SharePoint Folder
sharepoint_file = "/sites/sharepointname/FirstFolder/" + file.xlsx
file_response = File.open_binary(ctx, sharepoint_file)

response = file_response
st.write(response)

#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, sheet_name="Sheet1", engine="openpyxl")
print(df)

Hello, an answer should be "standalone". References are great but insufficient. Solution should be explained in the post.

Collectives™ on Stack Overflow

How to read excel from SharePoint folder using python Pandas library

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related