How to Export Values for a MultiLine Column with - Append Changes to Existing Text

One of my customers recently came up with a requirement below :

-In a List there’s a column called COMMENTS (Multiple Lines of Text w/ Append Changes to Existing Text) where people put their comments for that list item whenever they edit it. It’s a column with Append Changes property set.

-When we use the option "Export to Spreadsheet" we only see the latest comment and not all the comments in the spreadsheet.

Since Out of the Box , "Export To SpreadSheet" doesn't export values of the previous versions , it only extracts the current Published Version. And therefore no values for Comments made in previous versions are exported.

I though of sharing here, the SharePoint OM code that I used in above scenario and which will do the job of extracting not just the current Version , but all previous versions.In other words -a Code Sample to export Version History of Items in a List :

I will lay out the steps that I followed to use the code. Below is the structure of the list that I used for testing. Default OOB Generic List, with few Columns I added : - Col1(single line of text), Col2(single line of text), Comments (Multiple lines of text, Append Changes to Existing Text).

 

abc

Below is the sample code that can used to extract Items with their Versions, and thus the Comments, who made the Comments (Modified By),and when (Modified Date), respective Version Number.

 

    1:  using System;
    2:  using System.Collections.Generic;
    3:  using System.Linq;
    4:  using System.Text;
    5:  using Microsoft.SharePoint;
    6:  using System.IO;
    7:  using System.Data;
    8:  namespace ExportVersionstoCSV
    9:  {
   10:      class Program2
   11:      {
   12:          static void Main(string[] args)
   13:          {
   14:              Console.WriteLine(@"Enter View URL of the List you want Exported. e.g http:\\site\Lists\List1\MyView.aspx");
   15:              Console.WriteLine(@"Where ""MyView"" signifies the View to use, which will determine number of columns to be exported");
   16:   
   17:              string strSite = Console.ReadLine();
   18:              Console.WriteLine("Enter filepath to store the Version content. e.g C:\\ListVReport.csv");
   19:              string strPath = Console.ReadLine();
   20:              SPSite site = new SPSite(strSite);
   21:              SPWeb webmain = site.OpenWeb();
   22:              SPList oList = webmain.GetListFromWebPartPageUrl(strSite);
   23:              SPView oViewObj = webmain.GetViewFromUrl(strSite);
   24:              SPView targetView = oViewObj;
   25:              
   26:              DataTable dtSource = oList.GetItems(targetView).GetDataTable();
   27:              System.Collections.Specialized.StringCollection ostrCollFinal = new System.Collections.Specialized.StringCollection();
   28:              foreach (DataColumn oColumnObj in dtSource.Columns)
   29:              {
   30:                  ostrCollFinal.Add(oList.Fields.GetFieldByInternalName(oColumnObj.ColumnName).Title);
   31:              }
   32:              CreateCSVFile(dtSource, strPath, ostrCollFinal, oList);
   33:          }
   34:          public static void CreateCSVFile(DataTable dt, string strFilePath, System.Collections.Specialized.StringCollection ostrCollparam, SPList oList)
   35:          {
   36:              // Create the CSV file to which grid data will be exported.
   37:              StreamWriter sw = new StreamWriter(strFilePath, false);
   38:              // First we will write the headers.
   39:              int iColCount = ostrCollparam.Count;
   40:              int ColPos = 0;
   41:              for (int i = 0; i < iColCount; i++)
   42:              {
   43:                  sw.Write(ostrCollparam[i]);
   44:                  if (ostrCollparam[i].Equals("Comments"))
   45:                  {
   46:                      ColPos = i;
   47:                  }
   48:                  if (i < iColCount - 1)
   49:                  {
   50:                      sw.Write(",");
   51:                  }
   52:              }
   53:              sw.Write(sw.NewLine);
   54:              // Now write all the rows.
   55:              foreach (DataRow dr in dt.Rows)
   56:              {
   57:              
   58:                  SPListItemVersionCollection oVColl = oList.GetItemById(int.Parse(dr["ID"].ToString())).Versions;
   59:                      foreach (SPListItemVersion oV in oVColl)
   60:                      {
   61:                          for (int i = 0; i < iColCount; i++)
   62:                          {
   63:                              if (oV[ostrCollparam[i]] != null)
   64:                              {
   65:                                  if (ostrCollparam[i].Equals("Modified By"))
   66:                                  {
   67:                                      sw.Write(oV[ostrCollparam[i].ToString()].ToString().Split('#')[1].ToString().Trim(new char[]{','}));
   68:                                  }
   69:                                  else
   70:                                  {
   71:                                      sw.Write(oV[ostrCollparam[i].ToString()].ToString());
   72:                                  }
   73:                              }
   74:                              if (i < iColCount - 1)
   75:                              {
   76:                                  sw.Write(",");
   77:                              }
   78:                          }
   79:                          sw.Write(sw.NewLine);        
   80:                      }
   81:               
   82:              }
   83:              sw.Close();
   84:          }
   85:      }
   86:  }
   87:   

Here's a screenshot of the sample OutPut :

abc2

If it was a document Library, then code will need to be modified to pick values from SPfile.Versions instead of SPListItem.Versions.

Hope this helps you save sometime !!