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.

 

using SP = Microsoft.SharePoint.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SharePoint.Client;
using System.Data;
Step 5
Add the below piece of code to your.cs file under the class
/// <summary>
/// Start for the Program
/// </summary>
/// <param name="args"></param>
static void Main(string[] args) {
    //Source Site Collection CSV File Path
    String filePath = @ "C:\SiteCollectionList.csv";
    GetSourceList(filePath);
}
/// <summary>
/// Read the data from CSV File
/// </summary>
/// <param name="filePath"></param>
public static void GetSourceList(string filePath) {
    var reader = new StreamReader(System.IO.File.OpenRead(filePath));
    Hashtable htSiteCollectionInfo = new Hashtable();
    //skip First Row in CSV File
    reader.ReadLine();
    while (!reader.EndOfStream) {
        var line = reader.ReadLine();
        var values = line.Split(',');
        //Add the Site Collection Name and Url to Hash Table
        htSiteCollectionInfo.Add(values[0].Trim(), values[1].Trim());
    }
    DataTable SourceData = new DataTable();
    SourceData.Columns.Add("Site Collection Title");
    SourceData.Columns.Add("List Title");
    SourceData.Columns.Add("Items Count");
    SourceData.Columns.Add("Site Collection Url");
    // For retrieving elements in the HashTag
    foreach(DictionaryEntry e in htSiteCollectionInfo) {
        GetListProperties(e.Key.ToString(), e.Value.ToString(), SourceData);
    }
    StringBuilder sb = new StringBuilder();
    DataTable dt = SourceData;
    foreach(DataRow dr in dt.Rows) {
        foreach(DataColumn dc in dt.Columns)
        sb.Append(FormatCSV(dr[dc.ColumnName].ToString()) + ",");
        sb.Remove(sb.Length - 1, 1);
        sb.AppendLine();
    }
    System.IO.File.WriteAllText("D:\\Sample\\SourceList.csv", sb.ToString());
}
/// <summary>
/// Get the List Properties like List Title, ItemCount etc...
/// </summary>
/// <param name="siteTitle"></param>
/// <param name="siteUrl"></param>
/// <param name="SourceData"></param>
public static void GetListProperties(String siteTitle, String siteUrl, DataTable SourceData) {
    ClientContext clientContext = new ClientContext(siteUrl);
    Web oWebsite = clientContext.Web;
    ListCollection collList = oWebsite.Lists;
    clientContext.Load(collList);
    clientContext.ExecuteQuery();
    foreach(SP.List oList in collList) {
        //Console.WriteLine("Title: {0} | Items Count: {1} | Site Title:{2}", oList.Title, oList.ItemCount, siteTitle);
        SourceData.Rows.Add(siteTitle, oList.Title, oList.ItemCount, siteUrl);
    }
}
/// <summary>
/// Function to format the datatable to CSV for exporting it to CSV File
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static string FormatCSV(string input) {
    try {
        if (input == null) return string.Empty;
        bool containsQuote = false;
        bool containsComma = false;
        int len = input.Length;
        for (int i = 0; i < len && (containsComma == false || containsQuote == false); i++) {
            char ch = input[i];
            if (ch == '"') containsQuote = true;
            else if (ch == ',') containsComma = true;
        }
        if (containsQuote && containsComma) input = input.Replace("\"", "\"\"");
        if (containsComma) return "\"" + input + "\"";
        else return input;
    } catch {
        throw;
    }
}

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!!