CODEWITHSUNDEEP
pythonexcelpandassharepoint
nerkes
nerkes

Reputation: 1

Trouble downloading SharePoint file into Python environment

I'm trying to pull a table from an excel file stored on a SharePoint site into a pandas DataFrame. I'm using the below code. It successfully authenticates the user and gives a 200 response code indicating successful download, but when I get to the last line -- trying to "read_excel" -- I get an error saying "File is not a recognized excel file." Does anyone know what I'm doing wrong here? Any and all help is greatly appreciated!

url = 'https://mydomain.sharepoint.com/:x:/r/sites/SharePointSiteName/_layouts/15/Doc.aspx?sourcedoc=%ABC123%7D&file=Test%20File%20xlsx&action=default&mobileredirect=true'
username = '[email protected]'
password = 'Password123!'

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 into pandas dataframe
x = pd.read_excel(bytes_file_obj,sheet_name = None)[/i][/color]

Upvotes: 0

Views: 615

Answers (1)

waqas
waqas

Reputation: 11

Hope you already found some soultion. I can download the file with:

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

# target url taken from sharepoint and credentials

url = 'https://[company].sharepoint.com'
# 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")
    
SP_File = File.open_binary(ctx, "/sites/[download path]/[filename].xlsx" )

with open("C:/Users/[download path]/[filename].xlsx", "wb") as local_file:
    local_file.write(SP_File.content)
    print("Download Complete")

Upvotes: 0

Related Questions