Convert Sharepoint List into Pandas Dataframe

Question

I have a table in Sharepoint that I'm wanting to convert into a Pandas Dataframe. I've largely used this question to try and frame a solution Get SharePoint List with Python. I'm having issues however.

Here is what I have so far...

import pandas as pd
from shareplum import Site
from requests_ntlm import HttpNtlmAuth

url = 'https://share.corporation.com/sites/group/subgroup/'

username = 'username'
password = 'password'

cred = HttpNtlmAuth(username, password)
site = Site(url, auth=cred, verify_ssl=False)

Up to this point, I can run the code without an error being thrown. However, when I run this bit:

sp_list = site.List('Q22020') # this creates SharePlum object

ShareplumRequestError: Shareplum HTTP Post Failed : 500 Server Error: Internal Server Error for url: https://share.corporation.com/sites/group/subgroup/_vti_bin/lists.asmx

I'm actually not entirely sure that my site.List('Q22020') is even correct.

However, following the instructions from this video: https://www.youtube.com/watch?v=dvFbVPDQYyk

When I manually enter the following url into my browser, it does generate an xml file, so I believe it's correct: https://share.corporation.com/sites/group/subgroup/_vti_bin/ListData.svc/Q22020

Answer 1

Try: https://share.corporation.com/sites/group/subgroup/Lists/Q22020/_vti_bin/lists.asmx

If not, go to the list on the web and have a look at the URL once you are looking at a view of the 'Q22020' list. Your "url" parameter may be incorrect.

Answer 2

I had the same problem and followed the same logic of getting the list name from URL. However, I found that the list name actually had a space in it, despite the URL not showing it. Adding the space solved the issue.

Using your example, if the URL is https://share.corporation.com/sites/group/subgroup/_vti_bin/ListData.svc/Q22020 but the list is actually 'Q2 2020' then you would change your code to:

sp_list = site.List('Q2 2020') 

Answer 3

A friend pass me this code early. ListaSP returns a Dataframe with your Sharepoint list contents

from office365.runtime.auth.client_credential import ClientCredential 
from office365.sharepoint.client_context import ClientContext

def dataframeSP(lista):
    sp_list = lista
    sp_lists = ctx.web.lists
    s_list = sp_lists.get_by_title(sp_list)
    l_items = s_list.get_items()
    ctx.load(l_items)
    ctx.execute_query()
    
    columnas=list(pd.DataFrame.from_dict(l_items[0].properties.items()).iloc[:,0])
    valores=list()
    for item in l_items:
        data=list(pd.DataFrame.from_dict(item.properties.items()).iloc[:,1])
        valores.append(data)
    resultado=pd.DataFrame(valores,columns=columnas)
    return resultado


client_id = "########" 
client_secret = "##############" 
site_url = "https://YOURSHAREPOINT.sharepoint.com/YOURLIST" 
ctx = ClientContext(site_url).with_credentials(ClientCredential(client_id, client_secret))

listaSP = ctx.web.lists.get_by_title("THE NAME OF YOUR SHAREPOINT LIST")