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 = 'user123@domain.com'
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)
Please help!! Trouble downloading SharePoint file into Python environment!
Please help!! Trouble downloading SharePoint file into Python environment!
Last edited by nerkes on Wed Aug 18, 2021 5:20 pm, edited 1 time in total.
- L'In20Cible
- Project Leader
- Posts: 1533
- Joined: Sat Jul 14, 2012 9:29 pm
- Location: Québec
Re: Please help!! Trouble downloading SharePoint file into Python environment!
You want to read bytes_file_obj, not response.content.
Re: Please help!! Trouble downloading SharePoint file into Python environment!
L'In20Cible wrote:You want to read bytes_file_obj, not response.content.
Sorry, I actually copied in my code incorrectly -- that's what I'm doing. I fixed the code above.
Return to “General Discussion”
Who is online
Users browsing this forum: No registered users and 46 guests