Sometime ago I wrote an article about using C# console applications to access and perform tasks in SharePoint. One example I use often is a dump of all lists in a site to CSV.

Between native and 3rd party options there are probably 86 ways to backup SharePoint. However sometimes you want something simple and guarenteed. The Export to Spreadsheet option is an example – its basic but works – kind of cosey.

So I wrote a console app that lets me quickly dump the contents of a Site to a series of CSV files and zips them. It literally only takes seconds and is a good “just-in-case” prior to performing any changes.

You call the application by

BackupListsToCSV.exe "SiteName" "WebName"

or

BackupListsToCSV.exe "SiteName" "WebName/SubWebName"

for further down the tree.

The app starts with Main confirming the arguments and if ok running the BackupWebToCSV method;

static void Main(string[] args)
{
    try
    {
        //confirm arguments
        if (!String.IsNullOrEmpty(args[0].ToString())
          && !String.IsNullOrEmpty(args[1].ToString()))
             BackupWebToCSV(args[0].ToString(), args[1].ToString());
    }
    catch
    {
        //else generate error
        Console.WriteLine("ERROR: SITE or WEB not specified.");
        Console.WriteLine("try: BackupSite.exe \"http://yoursitename/\""
              + " \"yourwebname\"");
    }
}

The BackupWebToCSV goes through each list in the Web and backs it up to CSV (BackupWebToCSV) and once they are all done compresses the CSV files into a Zip;

private static void BackupWebToCSV(string site, string web)
{
    try
    {
        //bind to site and web
        using(SPSite oSite = new SPSite(String.Format("{0}/", site.Trim('/'))))
        {
            using(SPWeb oWeb = oSite.OpenWeb(web))
            {
                foreach(SPList oList in oWeb.Lists)
                {
                    //backup each list
                    BackupListToCSV(oList);
                }
            }
        }

        //compress the files into a zip
        CompressFiles();
    }
    catch (Exception Ex)
    {
        Console.WriteLine(String.Format("Error: {0}", Ex));
    }
}

To backup the list to CSV I use the following method;

private static void BackupListToCSV(SPList oList)
{
    Console.WriteLine(String.Format("Backing up {0}", oList.Title));

    //get the list of headings for the list
    List <string> Headings = Get_Headings(oList);

    //create a content object
    StringBuilder CSVFileContent = new StringBuilder();

    //add the field names as the CSV headings
    string line = String.Empty;
    foreach(string Heading in Headings)
        line += String.Format("\"{0}\",", Heading);
    CSVFileContent.AppendLine(line.Trim(','));

    //bind to the list items
    SPListItemCollection oCollection = oList.Items;

    foreach(SPListItem oItem in oCollection)
    {
        //for each item add the values to the content
        line = String.Empty;

        foreach(string Heading in Headings)
        line += oItem[Heading] == null
            ? String.Format("\"{0}\",", String.Empty)
            : String.Format("\"{0}\",", oItem[Heading].ToString());

        CSVFileContent.AppendLine(line.Trim(','));

        //display some progress on the screen
        Console.Write(".");
    }

    //save the csv file
    SaveCSVFile(oList.Title, CSVFileContent);

    //null the collection
    oCollection = null;
}

Its a bit long, but it is relatively simple. First it creates a list of all the field names in the SPList;

List <string> Headings = Get_Headings(oList);
...
...
private static List < string > Get_Headings(SPList oList)
{
    List < string > Headings = new List < string > ();
    foreach(SPField field in oList.Fields)
       Headings.Add(field.InternalName);

    return (Headings);
}

It then uses this list of field names as the column names of the CSV, ie its first line;

foreach(string Heading in Headings)
    line += String.Format("\"{0}\",", Heading);

CSVFileContent.AppendLine(line.Trim(','));

Using this same list, I can then get the value for that field name for each item, build a CSV string, then add it to the content;

foreach(SPListItem oItem in oCollection)
{
    //for each item add the values to the content
    line = String.Empty;

    foreach(string Heading in Headings)
        line += oItem[Heading] == null
            ? String.Format("\"{0}\",", String.Empty)
            : String.Format("\"{0}\",", oItem[Heading].ToString());

    CSVFileContent.AppendLine(line.Trim(','));

    //display some progress on the screen
    Console.Write(".");
}

Notice to handle null records I set the value as String.Empty;

line += oItem[Heading] == null
    ? String.Format("\"{0}\",", String.Empty)
    : String.Format("\"{0}\",", oItem[Heading].ToString());

Once all the items have been added to the content, I can write it to disk;

SaveCSVFile(oList.Title, CSVFileContent);
...
...
private static void SaveCSVFile(String Title, StringBuilder Content)
{
	string FileName = String.Format("{0}.csv", Title);
	FileNames.Add(FileName);

	TextWriter writer = new StreamWriter(FileName);
	writer.Write(Content.ToString());
	writer.Close();
	writer.Dispose();
}

This process goes through all the lists pretty quickly, but depending on the Web you can end up with some pretty big CSV files, which are great candidates for compression.

I had a look around and settled on the ICSharpCode.SharpZipLib.dll library as it is fairly simple, is fast, and has worked in every scenario Ive needed.

private static void CompressFiles()
{
    Console.WriteLine(String.Format("Compressing {0} files", FileNames.Count));

    //create the zip file name
    string ZipFileName = String.Format("{0}-{1}-{2}.zip",
         DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day);

    //set up the zip file
    ZipOutputStream ZipFile = new ZipOutputStream(File.Create(ZipFileName));
    ZipFile.SetLevel(9); //highest compression
    byte[] buffer = new byte[4096];

    //for each csv file
    foreach(string FileName in FileNames)
    {
        ZipEntry ZipFileEntry = new ZipEntry(FileName);
        ZipFileEntry.DateTime = DateTime.Now;
        ZipFile.PutNextEntry(ZipFileEntry);

        //stream the file into the zip
        using(FileStream fs = File.OpenRead(FileName))
        {
            int SourceBytes;

            do
            {
                SourceBytes = fs.Read(buffer, 0, buffer.Length);
                ZipFile.Write(buffer, 0, SourceBytes);
                Console.Write(".");
            } while (SourceBytes > 0);
        }
    }

    //finalise the zip
    ZipFile.Finish();
    ZipFile.Close();

    //delete the source files
    DeleteFiles();
}

The entire application is only 180 lines long, and in my main example extracts some 500,000 records from about 30 lists and compresses them down to a 19Mb file in about a minute – not bad.

Click here and go to see the full code on the next tab else you can download the solution below!