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).
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 :
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 !!
Comments
Anonymous
September 27, 2010
Can you provide any suggestions for implementing this code? Maybe through a custom action. What is you recommendation?Anonymous
January 25, 2011
What action did you take to implement this code? Referring to Brad's question.Anonymous
May 16, 2011
I am looking for a similar solution but not very familiar with the coding. Could you please mention where to insert this code like in webpart etc. or using Visual studio?? Would appreciate itAnonymous
July 13, 2011
Is this also an issue with SharePoint 2010?Anonymous
November 03, 2011
Manuj, Can you please update on how to implement this code in SP ?Anonymous
September 09, 2013
Very useful. Thanks for posting.Anonymous
November 23, 2015
I am currently having this issue but you are not tell us where to place this code. I am not a code person so can you post step by step instructions. I really need to complete this project. Thank you