I had a requirements from client to develop a migration tool which will migrate the excel sheet data and documents from local folder to SharePoint 2013 document library.
The excel sheet contains the location of documents from local folder plus the metadata in its columns.
I have developed a console application which:
1) Checks the access of user on SharePoint site and document library
2) Gives the row number in which there is some wrong data which cannot be added in library
3) Pick the document from location specified in excelsheet
4) Adds the data from other excel columns as the meta data of the file.
Below is the sample excel file
1) The column DOCUMENTLOCATION shown below is mandatory
2) The columns (except DocumentLocation) from excel file should be present in document library
3) The case of column names in excel and in document library should be same.
------------------------------------------------------------------------------------------------------------------------------------------------------------MAIN PROGRAM----------(refer at bottom for config file)----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------APP.CONFIG FILE-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The excel sheet contains the location of documents from local folder plus the metadata in its columns.
I have developed a console application which:
1) Checks the access of user on SharePoint site and document library
2) Gives the row number in which there is some wrong data which cannot be added in library
3) Pick the document from location specified in excelsheet
4) Adds the data from other excel columns as the meta data of the file.
Below is the sample excel file
1) The column DOCUMENTLOCATION shown below is mandatory
2) The columns (except DocumentLocation) from excel file should be present in document library
3) The case of column names in excel and in document library should be same.
------------------------------------------------------------------------------------------------------------------------------------------------------------MAIN PROGRAM----------(refer at bottom for config file)----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Microsoft.SharePoint.Client; using System.IO; using System.Web; using System.Security; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Net; using System.Diagnostics; #endregion namespace MigrateExcel { class Program { #region Constants //All the data is read from config file //Full path of excel sheet from local folder in which the metadata is saved private const string EXCELFILEPATHWITHFILENAME = "ExcelFilePathWithFileName"; //Sheet name usually it is sheet1 private const string EXCELSHEETNAME = "ExcelSheetName"; //Column name where the location of document is specified in excel sheet private const string COLNAMEDOCUMENTLOCATION = "ColNameDocumentLocation"; //Library name private const string SPLIBRARYNAME = "SPLibraryName"; //site URL private const string SPSITEURL = "SPSiteURL"; //Message to be shown when user do not have access on library or library do not exist private const string MSG_NOLIBRARY = "Msg_NoLibrary"; //Message to be shown if columns from excel sheet do not exist in library (case of columns will also //be checked private const string MSG_UNMATCHEDCOLUMNS = "Msg_UnmatchedColumns"; private const string MSG_CORRECTURLLIBRARY = "Msg_CorrectURLLibrary"; //Path of notepad file to log exceptions private const string ERRORFILEPATH = "ErrorFilePath"; static string _excelName; #endregion static void Main(string[] args) { try { _excelName = Path.GetFileName(ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME]).ToString(); ClientContext context = getContext(); //to check the access of user context.Load(context.Web, w => w.Title); context.ExecuteQuery(); if (context != null) { Web currentWeb = context.Web; context.Load(currentWeb); context.ExecuteQuery(); List docs = getLibrary(context); if (docs != null) { if (getConfirmation(docs.Title, context.Web.Title)) { if (!excelFileOpen()) { context.Load(docs.Fields); context.ExecuteQuery(); if (checkColumns(getColumnNames(docs), getExcelColumns())) { AddDocument(context, docs); } else { Console.WriteLine(ConfigurationManager.AppSettings[MSG_UNMATCHEDCOLUMNS]); } } else { //do nothing } } else { //do nothing } } } Console.ReadKey(); } catch (Exception ex) { WriteError(ex); } } #region Methods private static bool excelFileOpen() { string filePath = ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME]; bool open = false; try { using (System.IO.File.Open(filePath, FileMode.Open)) { } } catch { Console.WriteLine("The file " + _excelName + " is already open or is unavailable."); open = true; } return open; } private static bool getConfirmation(string LibTitle, string SiteTitle) { bool proceed = false; try { Console.WriteLine(ConfigurationManager.AppSettings[MSG_CORRECTURLLIBRARY] + "\n"); Console.WriteLine("To proceed with migration of excel file " + _excelName + " to document libray: " + LibTitle + " in site: " + SiteTitle + "\n Press Y or y then press Enter key. \n If you do not" + " wish to proceed \n Press N or n then press Enter key."); string answer = Console.ReadLine(); if (answer.ToLower().Equals("y")) { proceed = true; } else { //do nothing } } catch (Exception) { throw; } return proceed; } private static void AddDocument(ClientContext context, List docs) { try { DataTable dt = ConvertExcelInToDataTable(ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME], ConfigurationManager.AppSettings[EXCELSHEETNAME], false, false); List<string> excelColumn = getExcelColumns(); int i = 1; foreach (DataRow row in dt.Rows) { FileCreationInformation newFile = new FileCreationInformation(); i += 1; try { newFile.Content = System.IO.File.ReadAllBytes(Convert.ToString(row[ConfigurationManager.AppSettings[COLNAMEDOCUMENTLOCATION]])); newFile.Url = Path.GetFileName(Convert.ToString(row[ConfigurationManager.AppSettings[COLNAMEDOCUMENTLOCATION]])); newFile.Overwrite = true; context.Load(docs.RootFolder.Files); context.ExecuteQuery(); Microsoft.SharePoint.Client.File uploadFile = docs.RootFolder.Files.Add(newFile); context.Load(uploadFile); context.ExecuteQuery(); foreach (string col in excelColumn) { uploadFile.ListItemAllFields[col] = Convert.ToString(row[col]); } uploadFile.ListItemAllFields.Update(); context.ExecuteQuery(); } catch (Exception e) { WriteError(e); Console.WriteLine("There was a problem in copying the row :" + i); deleteUploadedDoc(docs, newFile.Url, context); } } } catch (Exception) { throw; } } private static DataTable ConvertExcelInToDataTable(string excelFilePath, string SheetName, bool addHeader, bool oneRow) { DataTable excelData = new DataTable(); try { string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;"; using (OleDbConnection connection = new OleDbConnection(con)) { connection.Open(); if (!oneRow) { OleDbCommand command = new OleDbCommand("select * from [" + SheetName + "$]", connection); using (OleDbDataReader dr = command.ExecuteReader()) { excelData.Load(dr); } } else { OleDbCommand command = new OleDbCommand("select top 1 * from [" + SheetName + "$]", connection); using (OleDbDataReader dr = command.ExecuteReader()) { excelData.Load(dr); } } if (addHeader) { // in some excel header column is missing, to creater header with the first record of excel sheet, so that the row is considered in Foreach loop DataRow row = excelData.NewRow(); for (int i = 0; i < excelData.Columns.Count; i++) { row[i] = excelData.Columns[i].ColumnName; } excelData.Rows.InsertAt(row, 0); } else { //do nothing } } } catch (Exception ex) { throw; } return excelData; } private static void deleteUploadedDoc(List docs, string url, ClientContext context) { try { Web web = context.Web; Microsoft.SharePoint.Client.File f = docs.RootFolder.Files.GetByUrl(url); context.Load(f); f.DeleteObject(); context.ExecuteQuery(); // Delete file here but throw Exception //Console.Write("deleted"); } catch { throw; } } private static ClientContext getContext() { ClientContext context = null; try { context = new ClientContext(ConfigurationManager.AppSettings[SPSITEURL]); } catch (System.Exception) { throw; } return context; } private static List getLibrary(ClientContext context) { List docLib = null; try { docLib = context.Web.Lists.GetByTitle(ConfigurationManager.AppSettings[SPLIBRARYNAME]); context.Load(docLib); context.ExecuteQuery(); } catch (System.Exception) { Console.WriteLine(ConfigurationManager.AppSettings[MSG_NOLIBRARY]); docLib = null; } return docLib; } private static List<string> getColumnNames(List docs) { List<string> list = new System.Collections.Generic.List<string>(); try { foreach (Field field in docs.Fields) { list.Add(Convert.ToString(field.Title)); } } catch (System.Exception) { throw; } return list; } private static List<string> getExcelColumns() { List<string> list = new System.Collections.Generic.List<string>(); try { DataTable dt = ConvertExcelInToDataTable(ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME], ConfigurationManager.AppSettings[EXCELSHEETNAME], false, true); foreach (DataColumn col in dt.Columns) { if (Convert.ToString(col.ColumnName) != ConfigurationManager.AppSettings[COLNAMEDOCUMENTLOCATION]) { list.Add(Convert.ToString(col.ColumnName)); } } } catch (System.Exception) { throw; } return list; } private static bool checkColumns(List<string> getLibColNames, List<string> getExcelColNames) { bool equal = true; try { foreach (string Excelitem in getExcelColNames) { if (!getLibColNames.Contains(Excelitem)) { equal = false; Console.WriteLine("The column name " + Excelitem + " do not exist in Document Library or the case do not match"); } else { //do nothing } } } catch (System.Exception) { throw; } return equal; } #endregion #region Exception private static string GetExceptionMessage(Exception e) { StringBuilder message = new StringBuilder(); bool flag = false; string strInnerExeption; while (e != null) { if (flag) { strInnerExeption = "Inner Exception"; } else { strInnerExeption = string.Empty; } // Get stack trace for the exception with source file information var st = new StackTrace(e, true); // Get the top stack frame var frame = st.GetFrame(st.FrameCount - 1); // Get the line number from the stack frame var line = frame.GetFileLineNumber(); string method = frame.GetMethod().ToString(); int line1 = frame.GetFileLineNumber(); message.Append(Environment.NewLine); message.Append("----------" + strInnerExeption + " Exception----------"); message.Append(Environment.NewLine); message.Append("\t\r\nType : " + e.GetType().FullName); message.Append(Environment.NewLine); message.Append("\t\r\nMessage : " + e.Message); message.Append(Environment.NewLine); message.Append("\t\r\nSource : " + e.Source); message.Append(Environment.NewLine); message.Append("\t\r\nTargetSite : " + e.TargetSite); message.Append(Environment.NewLine); message.Append("\t\r\nLine Number : " + line + " : " + method); message.Append(Environment.NewLine); message.Append("\t\r\nStack Trace : \n" + e.StackTrace); message.Append(Environment.NewLine); message.Append("\t\r\n----------" + strInnerExeption + " Exception----------"); message.Append(Environment.NewLine); flag = true; e = e.InnerException; } return message.ToString(); } protected static void WriteError(Exception e) { try { string errorMessage = GetExceptionMessage(e); addToFile(errorMessage); } catch (Exception) { } } protected static void addToFile(string errorMessage) { try { string path = ConfigurationManager.AppSettings[ERRORFILEPATH] + DateTime.Today.ToString("yyyy_MM_dd") + ".txt"; string subPath = ConfigurationManager.AppSettings[ERRORFILEPATH]; bool isExists = System.IO.Directory.Exists(subPath); if (!isExists) System.IO.Directory.CreateDirectory(subPath); if (!System.IO.File.Exists(path)) { System.IO.File.Create(path).Close(); } using (StreamWriter w = System.IO.File.AppendText(path)) { //w.WriteLine("\r\nLog Entry : "); //w.WriteLine("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture)); w.WriteLine("\r\nLog Entry : {0}", DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss")); string err = "Error in: " + "" + ". Error Message:" + errorMessage; w.WriteLine(err); w.WriteLine("__________________________"); w.Flush(); w.Close(); } } catch (Exception) { //WriteError(ex.Message); } } #endregion } // } }
<?xml version="1.0" encoding="utf-8" ?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" /> </startup> <appSettings> <add key="ErrorFilePath" value="D:\\Projects\\Tamu\\Codes\\Errors\\MigrateExcel"/> <add key="ExcelFilePathWithFileName" value="C:\\Users\\Downloads\\MigrationToolSampleData.xlsx" /> <add key="ExcelSheetName" value="Sheet1" /> <add key="ColNameDocumentLocation" value="DocumentLocation" /> <add key="SPLibraryName" value="Documents" /> <add key="SPSiteURL" value="http://tamouh_srv01:8086/" /> <add key="Msg_NoLibrary" value="Library does not exist or you do not have permissions on the library." /> <add key="Msg_UnmatchedColumns" value="Documents" /> <add key="Msg_CorrectURLLibrary" value="The site URL and document library provided are correct" /> </appSettings> </configuration>
No comments:
Post a Comment