Sharepoint excel data take into Pandas data frame without downloading

Question

I need to take my SharePoint excel file in to pandas data frame because I need to do analysis using python for that excel file. to access the SharePoint I use bellow code and it works. From bellow code I can access my excel file which located in SharePoint. Now I want take my excel file in to pandas data frame.so how I can modify bellow code?

from office365.sharepoint.client_context import ClientContext

SP_SITE_URL ='https://asdfgh.sharepoint.com/sites/ABC/'
SP_DOC_LIBRARY ='Publications'
USERNAME ='[email protected]'
PASSWORD ='******' 

# 1. Create a ClientContext object and use the user’s credentials for authentication 
ctx =ClientContext(SP_SITE_URL).with_user_credentials(USERNAME, PASSWORD) 

# 2. Read file entities from the SharePoint document library 
files = ctx.web.lists.get_by_title(SP_DOC_LIBRARY).root_folder.files 
ctx.load(files)
ctx.execute_query()

# 3. loop through file entities
for filein files: 
  # 4. Access the file object properties 
  print(file.properties['Name'], file.properties['UniqueId'])
  # 5. Access list item object through the file object 
  item = file.listItemAllFields
  ctx.load(item) 
  ctx.execute_query() 
  print('Access metadata - Category: {0}, Status: {1}'.format(item.properties['Category'], item.properties['Status']))

# 4. The Output: 
# File Handling in SharePoint Document Library Using Python.docx 77819f08-5fbe-450f-9f9b-d3ae2862cbb5
# Access metadata - Category: Python, Status: Submitted

Satya V · Accepted Answer · 2021-06-01 13:54:01Z

1

For it operate through, the file will be needed to be present in the memory of the system.

Find the path of the file - It should be in of the Meta-Data of the file which you are already.

With the below library :

from office365.sharepoint.files.file import File

You could use the below code to go ahead and store it in the memory and read from the Panda data frame.

response = File.open_binary(ctx, url)

bytes_file_obj = io.BytesIO()
bytes_file_obj.write(response.content)
bytes_file_obj.seek(0) #set file object to start

df = pd.read_excel(bytes_file_obj, sheetname = <Sheetname>)

answered Jun 1, 2021 at 13:54

Satya V

4,2241 gold badge8 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Satya V Over a year ago

Following up to see whether the above solution helped ?

Erik Johnsson Over a year ago

May I ask how to save data into excel in sharepoint?

Collectives™ on Stack Overflow

Sharepoint excel data take into Pandas data frame without downloading

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related