As Sharepoint consultants we have often been faced with this opportunity to port the data across environments. I came across such a scenario in one of my recent engagements where I had to push all the data from Excel to Sharepoint Lists.
Below I used data migration from excel sheet, but if any migration tool that you have used does not support for the migration of created by and created date fields - you can fine tune the below code to your benefit.
Below I used data migration from excel sheet, but if any migration tool that you have used does not support for the migration of created by and created date fields - you can fine tune the below code to your benefit.
Couple of issues I would like to address which I faced and the solutions to those to help my SharePoint community members.
1. Import n excel sheet to create a List via the Sharepoint User Interface - This has worked fine except that the look ups were not carried through in the List. I had to update the column types after the create.
The look up column for Country needed to be changed
Note : If the Excel column for Country did not have values from the data validation list, then you will need to add those values in the choice column above. By default it takes all the values form the Sharepoint column and converts it to a choice.
2. "Created by" fields were mirrored with admin account. I needed to now convert these to the users who had created them since there was functionality to display only your own items to contributors.
Here is a Visual web part I came up with to update the "Created by" and "Modified by" field to any other user based on an additional column value - "CopyCreatedBy" to update the user name.
For entering data - I could not view the List in data sheet view, Some research on the net showed I needed to install this below.
If you do not see the data sheet view - Install the update for X64, to access Data sheet view. 2007
Office System Driver: Data Connectivity Components
OK, That sorted that issue out. Now I entered some data for the user names.
UX:
The below code is not permormance savvy since it uses the crude way to access the list & data.
Although the code works fine and performs great in a local environment, issue is with the way the Items property is accessed. The Items property queries ALL items from the Content Database for the current SPList and “unfortunately” does that every time we access the Items property. The retrieved items ARE NEVER CACHED. In the loop the Items property is accessed twice for every loop iteration – once to retrieve the Count, and once to access the actual Item identified by its index.
Code:
Although the code works fine and performs great in a local environment, issue is with the way the Items property is accessed. The Items property queries ALL items from the Content Database for the current SPList and “unfortunately” does that every time we access the Items property. The retrieved items ARE NEVER CACHED. In the loop the Items property is accessed twice for every loop iteration – once to retrieve the Count, and once to access the actual Item identified by its index.
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
namespace ChangeUserName.ChangeCreatedBy
{
public partial class ChangeCreatedByUserControl : UserControl
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
lblerror.Visible = false;
txtListName.Text = "";
txtwebSite.Text = "";
txtCopyCreateByCol.Text = "";
}
}
protected bool validate()
{
if (txtListName.Text =="" ||txtwebSite.Text == "" || txtCopyCreateByCol.Text == "")
{
lblerror.Visible = true;
lblerror.Text += "Please enter the Required Fields with *" ;
return false;
}
return true ;
}
protected void Update_Click(object sender, EventArgs e)
{
if (validate())
{
lblerror.Text = "";
try
{
using (SPSite site = new SPSite(txtwebSite.Text))
{
using (SPWeb web = site.OpenWeb())
{
SPList list = web.Lists[txtListName.Text];
foreach (SPListItem item in list.Items)
{
web.AllowUnsafeUpdates = true;
SPUser user = web.EnsureUser(item[txtCopyCreateByCol.Text].ToString());
string value1 = user.ID + ";#" + user.Name;
item["Author"] = value1;
item["Editor"] = value1;
item.Update();
}
list.Update();
web.Update();
lblerror.Visible = true;
lblerror.Text = "Successfully updated the created by field for all records of the list : " + txtListName.Text;
}
}
}
catch (Exception ex)
{
lblerror.Visible = true;
lblerror.Text += "All Records could not be updated. " + ex.Message.ToString();
}
}
}
}
}
