0

I have accessed a list in SharePoint Online with Python and want to save the list data to a file (csv or json) to transform it and sort some metadata for a migration

I have full access to the Sharepoint site I am connecting(client ID, secret..).

from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.runtime.client_request import ClientRequest
from office365.sharepoint.client_context import ClientContext

I have set my settings:

app_settings = {
     'url': 'https://company.sharepoint.com/sites/abc',
     'client_id': 'id',
     'client_secret': 'secret'
}

Connecting to the site:

context_auth = AuthenticationContext(url=app_settings['url'])
context_auth.acquire_token_for_app(client_id=app_settings['client_id'],
client_secret=app_settings['client_secret'])
ctx = ClientContext(app_settings['url'], context_auth)

Getting the lists and checking the titles:

lists = ctx.web.lists
ctx.load(lists)
ctx.execute_query()
for lista in lists:
    print(lista.properties["Title"])  # this gives me the titles of each list and it works.

lists is a ListCollection Object

From the previous code, I see that I want to get the list titled: "Analysis A":

a1 = lists.get_by_title("Analysis A")
ctx.load(a1)
ctx.execute_query()  # a1 is a List item - non-iterable

Then I get the data in that list:

a1w = a1.get_items()
ctx.load(a1w)
ctx.execute_query() # a1w is a ListItemCollection - iterable

idea 1: df to json/csv

df1 = pd.DataFrame(a1w) #doens't work)

idea 2:

follow this link: How to save a Sharepoint list as a file?

I get an error while executing the json.loads command:

JSONDecodeError: Extra data: line 1 column 5 (char 4)

Alternatives:

I tried Shareplum, but can't connect with it, like I did with office365-python-rest. My guess is that it doesn't have an authorisation option with client id and client secret (as far as I can see)

How would you do it? Or am I missing something?

martineau
  • 119,623
  • 25
  • 170
  • 301
Vyron B.
  • 35
  • 3
  • 7

2 Answers2

2

Sample test demo for your reference.

context_auth = AuthenticationContext(url=app_settings['url'])
context_auth.acquire_token_for_app(client_id=app_settings['client_id'],
client_secret=app_settings['client_secret'])
ctx = ClientContext(app_settings['url'], context_auth)

list = ctx.web.lists.get_by_title("ListA")
items = list.get_items()
ctx.load(items)
ctx.execute_query()

dataList = []
for item in items:
     dataList.append({"Title":item.properties["Title"],"Created":item.properties["Created"]})
     print("Item title: {0}".format(item.properties["Title"]))
pandas.read_json(json.dumps(dataList)).to_csv("output.csv", index = None,header=True)
Lee
  • 5,305
  • 1
  • 6
  • 12
  • Thanks for your solution, I tried but csv file which has generated is empty. Do you think this has something to do my read permission? Also it does not print anything with print("Item EmployeeNumber: {0}".format(item.properties["EmployeeNumber"])) – Idleguys Aug 11 '20 at 20:13
0

Idea 1

It's hard to tell what can go wrong without the error trace. But I suspect it's likely to do with malformed data that you are passing as the argument. See here from the documentation to know exactly what's expected.

Do also consider updating your question with relevant stack error traces.

Idea 2

JSONDecodeError: Extra data: line 1 column 5 (char 4)

This error simply means that the Json string is not a valid format. You can validate JSON strings by using this service. This often tells you the point of error which you can then use it to manually fix the problem.

This error could also be caused if the object that is being parsed is a python object. You can avoid this by jsonifying each line as you go

data_list= []
for line in open('file_name.json', 'r'):
    data_list.append(json.loads(line))

This avoids storing intermediate python objects. Also see this related issue if nothing works.

AzyCrw4282
  • 7,222
  • 5
  • 19
  • 35