Console App To Get The Total Number Of Lists And Its Item Count For Multiple Site Collection In SharePoint 2013 Using C# CSOM

The Console application will read the Site Collections Url and Name from CSV file and get the list details like title , total item counts from each site collection and export the data to .CSV file using C# CSOM

This article talks about how to get the list of source lists/libraries with the item count for multiple site collections at one shot.

Consider a scenario where you have around 100 site collections in your farm which you are planning to migrate from SharePoint 2013 to SharePoint Online. Going to each site collection and getting the data is a tough job. So what I am going to do is to write a Console app using C# and perfom the activity at one go. It's a pretty simple method which should save a lot of time and we can focus on other important migration aspects.

Here are the steps involved in the development,

Step 1

Go to your SharePoint 2013 dev machine/server and open Visual Studio.


Step 2

Select File > New > Project > Console Application give a name. Lets say "GetSourceListData" and click on OK.


Step 3

Create a .CSV file with two columns, Site Collection Name and Site Collection URL 


Step 4

Come back to Visual Studio and add the below references and refer to those in the .cs file.

 
  1. using SP = Microsoft.SharePoint.Client;  
  2. using System;  
  3. using System.Collections;  
  4. using System.Collections.Generic;  
  5. using System.IO;  
  6. using System.Linq;  
  7. using System.Text;  
  8. using System.Threading.Tasks;  
  9. using Microsoft.SharePoint.Client;  
  10. using System.Data;  
  11. Step 5  
  12. Add the below piece of code to your.cs file under the class  
  13. /// <summary>  
  14. /// Start for the Program  
  15. /// </summary>  
  16. /// <param name="args"></param>  
  17. static void Main(string[] args) {  
  18.     //Source Site Collection CSV File Path  
  19.     String filePath = @ "C:\SiteCollectionList.csv";  
  20.     GetSourceList(filePath);  
  21. }  
  22. /// <summary>  
  23. /// Read the data from CSV File  
  24. /// </summary>  
  25. /// <param name="filePath"></param>  
  26. public static void GetSourceList(string filePath) {  
  27.     var reader = new StreamReader(System.IO.File.OpenRead(filePath));  
  28.     Hashtable htSiteCollectionInfo = new Hashtable();  
  29.     //skip First Row in CSV File  
  30.     reader.ReadLine();  
  31.     while (!reader.EndOfStream) {  
  32.         var line = reader.ReadLine();  
  33.         var values = line.Split(',');  
  34.         //Add the Site Collection Name and Url to Hash Table  
  35.         htSiteCollectionInfo.Add(values[0].Trim(), values[1].Trim());  
  36.     }  
  37.     DataTable SourceData = new DataTable();  
  38.     SourceData.Columns.Add("Site Collection Title");  
  39.     SourceData.Columns.Add("List Title");  
  40.     SourceData.Columns.Add("Items Count");  
  41.     SourceData.Columns.Add("Site Collection Url");  
  42.     // For retrieving elements in the HashTag  
  43.     foreach(DictionaryEntry e in htSiteCollectionInfo) {  
  44.         GetListProperties(e.Key.ToString(), e.Value.ToString(), SourceData);  
  45.     }  
  46.     StringBuilder sb = new StringBuilder();  
  47.     DataTable dt = SourceData;  
  48.     foreach(DataRow dr in dt.Rows) {  
  49.         foreach(DataColumn dc in dt.Columns)  
  50.         sb.Append(FormatCSV(dr[dc.ColumnName].ToString()) + ",");  
  51.         sb.Remove(sb.Length - 1, 1);  
  52.         sb.AppendLine();  
  53.     }  
  54.     System.IO.File.WriteAllText("D:\\Sample\\SourceList.csv", sb.ToString());  
  55. }  
  56. /// <summary>  
  57. /// Get the List Properties like List Title, ItemCount etc...  
  58. /// </summary>  
  59. /// <param name="siteTitle"></param>  
  60. /// <param name="siteUrl"></param>  
  61. /// <param name="SourceData"></param>  
  62. public static void GetListProperties(String siteTitle, String siteUrl, DataTable SourceData) {  
  63.     ClientContext clientContext = new ClientContext(siteUrl);  
  64.     Web oWebsite = clientContext.Web;  
  65.     ListCollection collList = oWebsite.Lists;  
  66.     clientContext.Load(collList);  
  67.     clientContext.ExecuteQuery();  
  68.     foreach(SP.List oList in collList) {  
  69.         //Console.WriteLine("Title: {0} | Items Count: {1} | Site Title:{2}", oList.Title, oList.ItemCount, siteTitle);  
  70.         SourceData.Rows.Add(siteTitle, oList.Title, oList.ItemCount, siteUrl);  
  71.     }  
  72. }  
  73. /// <summary>  
  74. /// Function to format the datatable to CSV for exporting it to CSV File  
  75. /// </summary>  
  76. /// <param name="input"></param>  
  77. /// <returns></returns>  
  78. public static string FormatCSV(string input) {  
  79.     try {  
  80.         if (input == nullreturn string.Empty;  
  81.         bool containsQuote = false;  
  82.         bool containsComma = false;  
  83.         int len = input.Length;  
  84.         for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++) {  
  85.             char ch = input[i];  
  86.             if (ch == '"') containsQuote = true;  
  87.             else if (ch == ',') containsComma = true;  
  88.         }  
  89.         if (containsQuote && containsComma) input = input.Replace("\"""\"\"");  
  90.         if (containsComma) return "\"" + input + "\"";  
  91.         else return input;  
  92.     } catch {  
  93.         throw;  
  94.     }  
  95. }  
Step 6

DONE!! Now its time for testing and debugging if requried.

Step 7

Basically what the code does is get the input data (Site Collection Name and Site Collection URL) from the CSV file, iterate all the site collections, and get the list data like title and item count, and export it the .CSV file.

Step 8

It's a pretty simple job considering if more number of site collections are present.

Output CSV File ScreenShot ,

 

Happy Coding!!