locked
SharePoint Data Extraction Approaches RRS feed

  • Question

  • Hi All,

    I am working on SharePoint 2007. I have a task where i need to extract all data from sharepoint portal i.e., List data, Library data, Images, Videos, HTML content, needs to be exported to Excel. The portal has enormous amount of data.

    What would be the best approach for this. Any help on this is greatly appriciated.

    Thanks.

    Friday, January 2, 2015 9:42 AM

Answers

  • This is not server object model link. It is simply consuming webs service using VS.

    Earlier you said that you don't want to use SPSevice now you have decided to use SPService again. Now i am confused what you want to use :-).

    You MUST first decide whether you will use SPService (i.e. jquery) or custom code using VS to achieve your goal. I would personally prefer VS and ref links are already posted in my earlier posts.


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    • Marked as answer by letShare Thursday, January 8, 2015 5:49 AM
    Wednesday, January 7, 2015 11:17 AM
    Moderator

All replies

  • Do you need to export each record? That's not a good idea.

    Option 1 - what the best practice says is to write code to iterate through the farm and get each object (SPFarm, SPWeb Application, SPSite, SPWeb, SPList, SPListItem) information.

    Option 2 - which is not prescribed - write a select sql query, get how many sites, how many list in each site, how many items in eash list.


    Thanks, Ashish | Please mark a post helpful/answer if it is helpful or answer your query.

    Friday, January 2, 2015 12:03 PM
  • Hi Ashish,

    Thanks for your reply. I need to export all the data inside every List\Library and there are some static HTML content in few pages.

    How about REST API? Please suggest.

    Thanks

    Friday, January 2, 2015 12:13 PM
  • Honestly, REST in 2007 will be nightmare to dynamically iterate around different types of objects.


    Thanks, Ashish | Please mark a post helpful/answer if it is helpful or answer your query.

    Friday, January 2, 2015 12:29 PM
  • Hello,

    You can also consider powershell to iterate all lists and libraries. Here is few links for sample code:

    http://sharepoint.stackexchange.com/questions/4457/iterating-through-a-sharepoint-list-and-items-with-powershell

    http://stackoverflow.com/questions/9980124/sharepoint-2010-powershell-loop-through-all-document-libraries-create-view-a

    Here is blog to run powershell in MOSS:

    http://blogs.technet.com/b/praveenh/archive/2013/01/22/running-powershell-on-moss-2007.aspx


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Monday, January 5, 2015 6:33 AM
    Moderator
  • Hi Hemendra,

    Well it looks like i must go for web service. So can you please guide me on

    1) what are the prerequisites for creating an REST API in MOSS 2007? Can i di this on my machine?

    2) Sample code snippet to read list data?

    3) the output is XML can we push this to an excel?

    Thanks.

    Monday, January 5, 2015 6:51 AM
  • this  can be of some help.

    Thanks, Ashish | Please mark a post helpful/answer if it is helpful or answer your query.

    Monday, January 5, 2015 8:25 AM
  • Hi,

    thanks for your reply. I gone through the link you provided i could not understand how the data is being pulled.

    Could you please give a detailed link where they have explained from start to end.

    Thanks.

    Monday, January 5, 2015 10:27 AM
  • Hi All,

    Any thoughts on how to achieve the above task.

    Thanks.

    Tuesday, January 6, 2015 3:32 AM
  • Hello,

    Yes, you can also use web service. To answer your all questions, use lists.asmx web service where you can use GetListCollection() method to loop all the lists then use GetListItems() method to return the list items.

    http://msdn.microsoft.com/en-us/library/office/ms429658%28v=office.14%29.aspx

    http://www.c-sharpcorner.com/UploadFile/anavijai/get-all-lists-using-web-service-in-sharepoint-2010-visual-we/

    Later either you can directly read XML file and convert to excel OR create new datatable/dataset from XML then convert it to excel. One ref link for you:

    http://juhi2012.wordpress.com/2012/08/24/convert-xml-node-list-to-data-table/ 

    Hope it could help


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Tuesday, January 6, 2015 5:19 AM
    Moderator
  • Hi Hemendra,

    Really thanks for your reply. the information you had given is what i was looking for but there is one more constraint in the requirement.

    Lists are scattered around many sub sites. the level is upto 4 sub sites. How can I loop through all the sub sites and get all the lists?

    Thanks.

    Tuesday, January 6, 2015 6:39 AM
  • Use webs.asmx to loop all subsites.

    http://www.c-sharpcorner.com/UploadFile/anavijai/how-to-get-all-the-sub-web-sites-using-sharepoint-2010-web-s/


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Tuesday, January 6, 2015 6:51 AM
    Moderator
  • Hi,

    How about using SPServices to export Lists to excel? Lists from various sub sites.

    Also I never worked on MOSS 2007. do we have COM (Client Object Model) in MOSS2007?

    Please share your thoughts.

    Thanks.


    • Edited by letShare Tuesday, January 6, 2015 10:15 AM Needed to additional info
    Tuesday, January 6, 2015 10:14 AM
  • you can use spservices. there is no client object model for 2007

    Thanks, Ashish | Please mark a post helpful/answer if it is helpful or answer your query.

    Tuesday, January 6, 2015 12:44 PM
  • Hi,

    considering the following assumptions which one would you choose between Web Service and SPService  for data extraction?

    1: Huge number of Lists.

    2:Lists are spread across many number of sub sites.

    3:There are 3 lists under each sub site and the name is same accross all sub sites.

    4: Lists with same name have to be exported to excel at a time.

    Kindly suggest.

    Thanks.

    Tuesday, January 6, 2015 1:22 PM
  • Hello,

    Both are good and could be chosen based on requirement and skills. SPService is based on jquery and require SP machine to implement. If you are good in jquery then you can use but remember you won't be able to debug script easily to find error if there is anything wrong in code. If you look at SPService code then you will notice that SPService is also use same web services to interact with SP site.

    Hope it could help


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Wednesday, January 7, 2015 4:55 AM
    Moderator
  • Hi,

    I have block of code to get all lists in all sub sites using the title. I referred to the following site.

    http://www.bentedder.com/spservices-and-jquery-pull-items-from-multiple-lists-in-sharepoint-2010/

    Now the data returned is in XML. Could you please guide me on how to export this to an excel file?

    I really appreciate if you could give some references.

    Thanks.

    Wednesday, January 7, 2015 5:06 AM
  • I believe i already added in link in previous post for excel code. Anyway here is one more:

    http://techolyvia.wordpress.com/2011/02/11/write-data-from-sharepoint-list-to-excel/


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Wednesday, January 7, 2015 5:32 AM
    Moderator
  • Hi,

    Using SpServices I think I cannot use the code given in your link because that is related server server object model.

    please guide me on how to do this using client side. I searched through the net but could not get any info.

    Thanks.

    Wednesday, January 7, 2015 6:46 AM
  • See my earlier post. There is link to use web service using VS.

    http://www.c-sharpcorner.com/UploadFile/anavijai/get-all-lists-using-web-service-in-sharepoint-2010-visual-we/


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    Wednesday, January 7, 2015 7:46 AM
    Moderator
  • Hi,

    The link you provided is for server object model. I am thinking of using SPServices so i need to convert the XML output from this jQuery to an Excel file.

    Please help me on this. I am really stuck.

    Thanks.


    • Edited by letShare Wednesday, January 7, 2015 8:57 AM Correction
    Wednesday, January 7, 2015 8:54 AM
  • This is not server object model link. It is simply consuming webs service using VS.

    Earlier you said that you don't want to use SPSevice now you have decided to use SPService again. Now i am confused what you want to use :-).

    You MUST first decide whether you will use SPService (i.e. jquery) or custom code using VS to achieve your goal. I would personally prefer VS and ref links are already posted in my earlier posts.


    Hemendra:Yesterday is just a memory,Tomorrow we may never see<br/> Please remember to mark the replies as answers if they help and unmark them if they provide no help

    • Marked as answer by letShare Thursday, January 8, 2015 5:49 AM
    Wednesday, January 7, 2015 11:17 AM
    Moderator