0

actually I'm a Java EE developer and I want to access a SharePoint Webserver by using the SharePoint REST service. Thereby, my goal is to read the data from the lists and save them into a database (yes, logically, it would be better to use a database instead of SharePoint lists, but unfortunately, I don't decide this). My problem is that I am thinking too much the JPA way with POJOs and Services. Therefore, I am stuck and kindly ask for help. I've managed to successfully receive data as a JSON object, but unlike when receiving a JSON object from the front end, this object is full of additional data, e.g. RoleAssignments, FieldValuesAsText, etc. Furthermore, both the German umlauts and special characters are escaped, which makes things even more difficult than I thought. Last but not least, the object seems to also contain hidden fields like "modified" or "created" which shouldn't be stored into the database.

My questions:

-Is it possible to use POJOs or do I manually have to extract the data from the received JSON object?

-Is it possible to tell SharePoint to export the data to Excel by using REST, so that this file will be received, stored on the Computer and afterwards be imported (by using e.g. the Java Excel API), such that I can avoid dealing with the JSON object?

Any help is really much appreciated!

Improve this question
1
  • Instead of using Excel, see what MS-Access can do for you. More powerful than Excel. Commented Sep 28, 2017 at 12:25

2 Answers 2

Reset to default
0

You can query the list with a simple post call to only return the items in the list you which matches and only display fields you have specified (And perhaps a few default as well)

So if you only want cars and only return the fields that contain information about the Manufacturer and the model then caml query would be like this:

<View><Query><Where><Eq><FieldRef Name='TypeOfVehicle' /><Value Type='Choice'>Car</Value></Eq></Where></Query><ViewFields><FieldRef Name='Manufacturer' /><FieldRef Name='Model' /></ViewFields><QueryOptions /></View>

You might want to download U2U CAML Query builder in order to save time when building the query string.

This however does not remove the need to handle the JSON/XML returned from the webservice, but it does make it possible for you to limit the information you receive.

Improve this answer
0

I can't speak to the Java aspects, but yes, you're going to have to extract data. For example, SharePoint lists can have different data types, and some of the types will store data differently. So person fields will have a different data structure than choice fields, which are different than multi-value choice fields, which are different from managed metadata fields, etc. (For example, a person field will have sub-fields for both the person id and the user's display name. Your code will have to determine which of these values it needs (or both), or if the code needs to do an additional query to get the user's login name)

Yes, you could use excel, which has the capability of pulling data from SharePoint lists. Though then you have the added difficulty of automating it.

You can also use SSIS, though the OOB OData connector doesn't support all data types, so that's not a great solution.

edit: I was negative on the SSIS solution since I just ran into its limitation that it doesn't support multi-value choice fields. If your lists have data types that SSIS supports, then this is a really simple way to automate an extract of data from SP to a database.

edit2: you can certainly specify the fields you need via REST:

_api/web/lists/getbytitle('listname')/items?$Select=field1,field2,fieldetc

And, you can certainly get the data returned as json. An example is here

Though again, depending on the data types, there will probably be further work left to do.

(if you're unfamiliar with the OData options, such as selecting fields, setting the sort order, etc., see here for the docs.

Improve this answer
9
  • It would be perfect, if I could get a JSON object containing dataset objects like {{"quantity" : 13, "category" : "car"}, {"quantity" : 20, "category" : "motorcycle"}, ...}. Is this generally possible with queries? Concerning the export to Excel: When I'm accessing a list on the SP server within the browser and select "List tools", there is a button "Export to Excel". Do you know if the REST API offers the possibility to get the list as an Excel file? Concerning SSIS: I'll keep that in mind in case I can definitely rule out all other options, since SSIS is completely new to me. Commented Sep 27, 2017 at 13:54
  • No, rest won't generate an excel file. See my edits for an expanded answer on OData/JSON Commented Sep 27, 2017 at 14:29
  • OK, thanks a lot, so I'll probably have to specify the fields via REST. Right now, there are four lists and each list will have to be stored into a database table. It would be perfect if this could be dynamic, i.e. reading all non-hidden column names to use them for the field specification, afterwards getting the list data and use again the non-hidden column names to store the list data into the corresponding database table. Is it possible to get all non-hidden column names? Commented Sep 27, 2017 at 14:41
  • You could first connect to _api/web/lists/getbytitle('list1')/Fields, as "hidden" is an attribute of a field. However, by hidden, do you mean system fields or just fields that aren't selected to be displayed? if the latter, then you could first query against _api/web/lists/getbytitle('list1')/views('viewguid') to get the list of fields in the view. That's starting to become a larger project, however. Commented Sep 27, 2017 at 14:52
  • By hidden I actually meant only those fields that are visible when I look into the list by using the web console. But I've performed _api/web/lists/getbytitle('list1')/Fields and it seems to me that there are also non-system columns that are hidden. At a first step, I'll try to not make it "100 percent" dynamic, i.e. I'll pass the column names for example as an array. If it will be clear which columns will have to be read from SP and written into database, then I'll can invest more time into that. Commented Sep 27, 2017 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.