Document Migration from Aconex to SharePoint online

Document Migration from Aconex to SharePoint online

Two parts to accomplish the migration without using Aconex APIs

Fist part is to download documents and their associated meta data. Documents and their meta data cannot be download together hence meta data is not associated with documents.

Follow the steps below to achieve download and then migration.

Download Content from Aconex

Step 1: Manually Download Documents

Document can be downloaded from Aconnex by selecting the documents and clicking the Tools button then Zip Download 

Step 2 : Manually Download document's meta data from Aconex

Click on Add/Remove Column button to be able to add meta data columns you want to include with documents.

Select the columns and hit OK button to add columns to the view.

Now click on Select All results from search as shown in the screenshot below.

Now click on Export to Excel button and it will download all the meta data information in Excel

Now when we have both documents in Zip and their meta data in excel, we can start uploading it to SharePoint.

Upload Content to SharePoint online

Step 3 : Manually upload documents to SharePoint

Open the document library in explorer where you want to upload documents.
IE allows to open document library in explorer view.

Once explorer view is opened, you may copy the documents directly through windows explorer.
Now when the documents are uploaded, Just create columns for the meta data you want to import for the documents. I created the following Columns in SharePoint online.

Document No  Title  Revision    Status Author (Organization)
Date Modified     Type Discipline Colour Print Additional Notes
Date on Document     File Name Package No Print Size
Transmittal In    Transmitted   Version

Step 4 : Automate the meta data association with uploaded documents using Client object model

This is the most interesting part of the migration where you can attach the documents' meta data to documents.

I am using visual studio and client object model to achieve this along with Excel services Library.

Following is the C# code to achieve this.



  //Instantiate Excel Application and open the workbook by providing the path in line 2
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"Path to Excel file");
        Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
        Excel.Range xlRange = xlWorksheet.UsedRange;

//Get the Row and column count as we want to iterate through both rows and columns in the //workbook
        int rowCount = xlRange.Rows.Count;
        int colCount = xlRange.Columns.Count;

//Iterate over the rows and columns and print to the console as it appears in the file
        //excel is not zero based!!
        for (int i = 2; i <= rowCount; i++)
        {
            string filename = "";
            try
            {

//Getting just two columns from excel but you may add as many as you want.                           
filename = (xlRange.Cells[i, 12].Value2 == null) ? string.Empty : xlRange.Cells[i, 12].Value2.ToString();

string documentNo = (xlRange.Cells[i, 1].Value2==null)?string.Empty: xlRange.Cells[i, 1].Value2.ToString();

 //Provide the SharePoint URL and get Client context

              string uri = "URL of SharePoint online site";

                using (ClientContext context = new ClientContext(uri))
                {
                    var passWord = new SecureString();
                    foreach (var c in "Your password here") passWord.AppendChar(c);
                    context.Credentials = new SharePointOnlineCredentials("Email address for SharePoint user", passWord);

//Provide name of the document library

                     List docLib = context.Web.Lists.GetByTitle("Documents");

                    context.Load(docLib);

// In excel we have file name which is also present in sharepoint document library

                    CamlQuery caml = new CamlQuery();
                    caml.ViewXml = "<View><Query><Where><Eq><FieldRef Name='FileLeafRef'/><Value Type='File'>" + filename + "</Value></Eq></Where></Query></View>";
                    ListItemCollection documentFile = docLib.GetItems(caml);



                    context.Load(documentFile);

                    context.ExecuteQuery();
                   
                    ListItem item = documentFile[0];

 // Now update the properties, you may provide as many as you want here
                    item["DocumentNo"] = documentNo;
                    item["DocumentTitle"] = title;

                   item.Update();

                   context.ExecuteQuery();

           }
            catch (Exception ex)
// Catch the exception here in case of any issue, we are logging in Log4Net (Open source logging library)
            {

                logger.Error("File Name : "+ filename+"  Error: "+ ex.Message);
            }
        }

        //cleanup
        GC.Collect();
        GC.WaitForPendingFinalizers();

        //rule of thumb for releasing com objects:
        //  never use two dots, all COM objects must be referenced and released individually
        //  ex: [somthing].[something].[something] is bad

        //release com objects to fully kill excel process from running in the background
        Marshal.ReleaseComObject(xlRange);
        Marshal.ReleaseComObject(xlWorksheet);

        //close and release
        xlWorkbook.Close();
        Marshal.ReleaseComObject(xlWorkbook);

        //quit and release
        xlApp.Quit();
        Marshal.ReleaseComObject(xlApp);

Congrats we have fully migrated documents and their meta data from Aconex to SharePoint online. If you have any question dont hesitate to contact me at shoaib_0077@hotmail.com