Data Migration to the cloud!

Data Migration from a third party application to Sharepoint is often a requirement and I had this challenge on the Sharepoint online environment, where we do not have access to the Central Admin or the server itself.

Where there is a will there is a way! We had a challenging situation and we figured out a workaround to do the needful...

Since it was Lotus application data- we needed a means to get the data in a Microsoft format so that we could read it ( any third party connections are not supported in the cloud yet), Lotus connectors are not supported.

Solution : get all the data ported to an excel format which can be read from Sharepoint environment!
The data includes attachments as well - so design your excel in such a manner that the last 2 columns have the below information:

1. Number of attachments
2. Path and Name of attachments in the format ( c://attachments/recordId/test1.docx)

This uses the sharepoint client object model to upload the data and since the attachments behaves a little sneaky I used the web services to upload the attachments once the record was created!
There are enough comments in the code to explain the logic but in case you have any questions please let me know!

This console application needs to run in the same domain for picking up Admin credentials of the logged ion user for the upload process and takes a config file which details the parameters like, site, list name etc. Add a web Reference to the http://localhost/_vti_bin/Lists.asmx and call it "SharepointListWS"

Here is the sample excel format : The second hidden row of the excel is the sharepoint column names from the List where the data is uploaded to.

Here is the code snippet that I created to import data from Lotus apps ( in a Excel format) to SPO world.

   
  
  
  private static void importFromExcel(StreamWriter streamwriterW)
  {
  
  try
  
 {
  
 Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
  
 string workbookPath = _ExcelPath;
  
 Microsoft.Office.Interop.Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5,
  
 "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true);
  
 Microsoft.Office.Interop.Excel.Sheets excelSheets = excelWorkbook.Worksheets;
  
 Microsoft.Office.Interop.Excel.Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelSheets.get_Item("Sheet1");
  
 object[,] values = excelSheet.get_Range(_ExcelRange).Value as object[,];
  
 ClientContext context = new ClientContext(_SharePointSiteUrl);
  
 context.AuthenticationMode = ClientAuthenticationMode.Default;
  
 Web web = context.Web;
  
 List list = web.Lists.GetByTitle(_ListName);
  
 for (int row = 3; row <= Convert.ToInt32(_ExcelRowNo); row++) // no. of rows of data in excel
  
 {
  
  ListItemCreationInformation listItemCreation = new ListItemCreationInformation();
  
 ListItem listItem = list.AddItem(listItemCreation);
  
 iRecord++;
  
 for (int column = 1; column <= Convert.ToInt32(_iColumnsToImport) + 1; column++) // no of fields/columns in excel to be imported
  
 {
  
 string columnName = values[2, column].ToString();
  
 strcolumnName = columnName;
  
 if (values[row, column] != null)
  
 {
  
  if (columnName == "AttachmentNo")
  
 {
  
 _strNoOfAttachments = values[row, column].ToString();
  
 strcolumnValue = _strNoOfAttachments;
  
 }
  
  else
  
 {
  
 listItem[columnName] = values[row, column];
  
 strcolumnValue = listItem[columnName].ToString(); 
  
 }
  
 }
  
 }
  
 listItem.Update();
  
 context.ExecuteQuery();
  
 string strListItemID = listItem.Id.ToString();
  
 _NoOfRowsImported++;
  
 
 logToFile("Record imported from Excel Row No :" + (iRecord + 2) + " to SP Record Id : " + strListItemID, streamwriterW);
  
 logToFile("No. of Attachments : " + _strNoOfAttachments, streamwriterW);
  
   if (_strNoOfAttachments != "0" && _strNoOfAttachments != null)
  
 {
  
 addAttachment(strListItemID, values[row, Convert.ToInt32(_iColumnsToImport) + 2].ToString(), streamwriterW);// if no. of attachment >0 
  
 }
  
 }
  
 excelWorkbook.Close();
  
 }
  
  catch (Exception e)
  
 {
  
 // log error here 
  
 }
  
 }
  
  private static void addAttachment(string strNewListItemID, string strAttachmentPathUnparsed, StreamWriter streamwriterW)
  
 {
  
  string attachmentUrl = string.Empty;
  
 try
  
 {
  
 //Parse path if delimiter exists 
  
 string[] splitAttachmentPath = strAttachmentPathUnparsed.Split(new Char[] { '|' });
  
 foreach (string strAttachmentPathParsed in splitAttachmentPath)
  
 {
  
  if (strAttachmentPathParsed.Trim() != "")
  
 {
  
 strAttachmentPath = strAttachmentPathParsed;
  
  if (!System.IO.File.Exists(strAttachmentPathParsed))
  
 {
  
 // log error

   return;
  
 }
  
  string fileName = Path.GetFileName(strAttachmentPathParsed);
  
 // Get file contents
  
 Byte[] contents = getFileStream(strAttachmentPathParsed);
  
 // Add the attachment to the new item in the SharePoint list & Instantiate a SharePoint
  
 // List Web Service object
  
 listWs = new SharePointListWS.Lists();
  
 // set the Web Service Credentials
  
 listWs.Credentials = System.Net.CredentialCache.DefaultCredentials;
  
 listWs.Url = _SharePointSiteUrl + "/_vti_bin/lists.asmx";
  
 attachmentUrl = listWs.AddAttachment(_ListName, strNewListItemID, fileName, contents);
  
 }
  
 }
  
    
 }
  
  catch (Exception ex3)
  
 {
  
 //log error  
 }
  
  if (!String.IsNullOrEmpty(attachmentUrl))
  
 {
  
     // log error

 }
  
 }
  
 }
  
 }