2

How to import excel sheet data with column header and update or insert in the SharePoint list based on any primary key in O365 by CSOM

1

There is no straightforward way of doing this unless you are creating a new list using an Excel file. If you want to do that, you can go to:

Site Contents -> Add an App -> Import Spreadsheet

and upload your excel file to create a sort of replica of the excel in the form of a list.

If you have an existing SharePoint list and want to insert/update using excel file using CSOM you need to create a Console or a Windows application in which you will need to write code for:

1. Reading the data from Excel file - I suggest you use EPPLUS library to do that so that you don't have dependency on Office files. Here is a sample C# code to Read an Excel spreadsheet into DataTable object picked up from here:

public static DataTable GetDataTableFromExcel(string path, bool hasHeader = true)
{
    using (var pck = new OfficeOpenXml.ExcelPackage())
    {
        using (var stream = File.OpenRead(path))
        {
            pck.Load(stream);
        }
        var ws = pck.Workbook.Worksheets.First();  
        DataTable tbl = new DataTable();
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        {
            tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
        }
        var startRow = hasHeader ? 2 : 1;
        for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            DataRow row = tbl.Rows.Add();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Text;
            }
        }
        return tbl;
    }
}

2. Adding/Updating the ListItem - Then you can use a variation of following code to add/update the list item into the List where dtMetadata will be the DataTable object fetched from the Excel file:

            foreach (DataRow row in dtMetadata.Rows)
            {
                properties = new Dictionary<string, KeyValuePair<string, string>>();
                foreach (KeyValuePair<string, KeyValuePair<string, string>> columnMapping in sourceTargetMapping)
                {
                    foreach (char invalidChar in invalidChars)
                    {
                        row[columnMapping.Key] = row[columnMapping.Key].ToString().Replace(invalidChar, ' ');
                    }

                    properties.Add(columnMapping.Value.Key, new KeyValuePair<string, string>(row[columnMapping.Key].ToString(), columnMapping.Value.Value));
                }
                AddUpdateListItem(targetClientContext, targetListName, targetPrimaryKey, properties);
            }

You will need to implement AddUpdateListItem function somewhere on the lines of:

        ListItemCreationInformation newItemInfo = new ListItemCreationInformation();
        ListItem newListItem = list.AddItem(newItemInfo);
        newListItem["Title"] = newItemName;
        string tempTerms = "";
        int oddCounter = 0;
        foreach (KeyValuePair<string, KeyValuePair<string, string>> kvPair in properties)
        {
            switch (kvPair.Value.Value)
            {
                case "Metadata":
                    oddCounter = 0;
                    tempTerms = "";
                    foreach (var item in kvPair.Value.Key.Split('|'))
                    {
                        if (!string.IsNullOrWhiteSpace(item) && oddCounter % 2 == 0)
                        {
                            tempTerms += item + "|";
                        }
                        oddCounter++;
                    }
                    if (!string.IsNullOrWhiteSpace(tempTerms))
                    {
                        tempTerms = tempTerms.Remove(tempTerms.Length - 1, 1);
                        SetManagedMetaDataField(clientContext, newListItem, kvPair.Key, tempTerms);
                    }
                    break;
                case "People":
                    SetPeopleField(newListItem, kvPair.Key, kvPair.Value.Key);
                    break;

                default:
                    try
                    {
                        newListItem[kvPair.Key] = kvPair.Value.Key;
                    }
                    catch (Exception ex)
                    {
                        WriteToLog("Exception: " + ex.Message);
                        newListItem[kvPair.Key] = kvPair.Value.Key;
                    }
                    break;
            }


        }
        newListItem.Update();

        clientContext.Load(list);
        clientContext.ExecuteQuery();

3. Additional handling of Metadata and People columns - You will need additional special handling of Metadata and People columns as you can't just set values to these columns as you can with Number or Single line of Text columns. These functions will come handy for those cases:

public static void SetManagedMetaDataField(ClientContext clientContext, ListItem listItem, string managedMetadataColumnName, string terms) { string termSetId, WssId = "-1", termGuid = "", termValue = "";

        foreach (var term in terms.Split('|'))
        {
            if (!string.IsNullOrWhiteSpace(term))
            {
                string searchTerm = term.Replace("&", "\uFF06").Trim();
                if (columnTermSetTermsMapping.ContainsKey(managedMetadataColumnName) && columnTermSetTermsMapping[managedMetadataColumnName].ContainsKey(term))
                {
                    termValue += WssId + ";#" + searchTerm + "|" + columnTermSetTermsMapping[managedMetadataColumnName][term] + ";#";
                }
                else
                {
                    GetTermSetInfo(clientContext, targetDocumentLibName, managedMetadataColumnName, out termSetId);

                    ClientContext xclientContext = new ClientContext(targetSiteUrl);
                    xclientContext.Credentials = new SharePointOnlineCredentials(targetSiteUsername, targetSecurePassword);
                    var taxonomySession = TaxonomySession.GetTaxonomySession(xclientContext);
                    var termStore = taxonomySession.GetDefaultSiteCollectionTermStore();
                    var termSet = termStore.GetTermSet(new Guid(termSetId));

                    var labelMatchInfo = new LabelMatchInformation(xclientContext)
                    {
                        TermLabel = searchTerm,
                        DefaultLabelOnly = true,
                        StringMatchOption = StringMatchOption.ExactMatch,
                        ResultCollectionSize = 1,
                        TrimUnavailable = true
                    };

                    var termCollection = termSet.GetTerms(labelMatchInfo);
                    try
                    {
                        xclientContext.Load(termCollection, tcol => tcol.Include(t => t.Id, t => t.Name));
                        xclientContext.ExecuteQuery();
                    }
                    catch (Exception ex)
                    {
                        WriteToLog("Exception in Fetching Term Id: " + ex.Message);
                    }
                    foreach (var item in termCollection)
                    {
                        if (item.Name.ToLower() == searchTerm.ToLower())
                        {
                            termGuid = item.Id.ToString();
                            break;
                        }
                    }

                    if (termGuid == "")
                    {
                        try
                        {
                            WriteToLog("Term: " + searchTerm + " not found for MetaData ColumnName: " + managedMetadataColumnName);
                            WriteToLog("MetaData ColumnName: " + managedMetadataColumnName
                                    + "\nTermValue: " + termValue
                                    + "\ntermSetId: " + termSetId
                                  );
                            Guid newGuid = Guid.NewGuid();
                            Term oTerm = termSet.CreateTerm(searchTerm, 1033, newGuid);
                            xclientContext.Load(oTerm);
                            xclientContext.ExecuteQuery();
                            termGuid = oTerm.Id.ToString();
                        }
                        catch (Exception ex)
                        {
                            WriteToLog("Exception Found: " + ex.Message);
                        }
                    }

                    if (termGuid == "")
                    {
                        WriteToLog("MetaData ColumnName: " + managedMetadataColumnName
                                    + "\nTermValue: " + termValue
                                    + "\ntermSetId: " + termSetId
                                  );
                        break;
                    }
                    else
                    {
                        termValue += WssId + ";#" + searchTerm + "|" + termGuid + ";#";
                        if (columnTermSetTermsMapping.ContainsKey(managedMetadataColumnName))
                        {
                            columnTermSetTermsMapping[managedMetadataColumnName].Add(term, termGuid);
                        }
                        else
                        {
                            columnTermSetTermsMapping.Add(managedMetadataColumnName, new Dictionary<string, string>() { { term, termGuid } });
                        }

                    }
                }
            }
        }
        if (termValue != "")
        {
            termValue = termValue.Remove(termValue.Length - 2, 2);
            WriteToLog("MetaData ColumnName: " + managedMetadataColumnName + "\tTermValue: " + termValue);
            listItem[managedMetadataColumnName] = termValue;
            listItem.Update();
        }
    }

    private static void SetPeopleField(ListItem newListItem, string fieldName, string fieldValue)
    {
        ClientContext clientContext = new ClientContext(targetSiteUrl);
        clientContext.Credentials = new SharePointOnlineCredentials(targetSiteUsername, targetSecurePassword);

        List<FieldUserValue> users = new List<FieldUserValue>();

        int i = 0;
        if (fieldValue.Contains('|'))
        {
            foreach (var item in fieldValue.Split('|'))
            {
                if (!string.IsNullOrWhiteSpace(item))
                {
                    try
                    {
                        User user = clientContext.Web.EnsureUser(item);
                        clientContext.Load(user);
                        clientContext.ExecuteQuery();
                        FieldUserValue fieldUserValue = new FieldUserValue();
                        fieldUserValue.LookupId = user.Id;
                        users.Add(fieldUserValue);
                    }
                    catch (Exception ex)
                    {
                        WriteToLog("Exception in finding: " + item + " | Exceptions Message: " + ex.Message);
                    }
                }
            }
        }
        else
        {
            //User user = clientContext.Web.EnsureUser(fieldValue.Split('#')[1]);
            try
            {
                User user = clientContext.Web.EnsureUser(fieldValue);
                clientContext.Load(user);
                clientContext.ExecuteQuery();
                FieldUserValue fieldUserValue = new FieldUserValue();
                fieldUserValue.LookupId = user.Id;
                users.Add(fieldUserValue);
            }
            catch (Exception ex)
            {
                WriteToLog("Exception in finding: " + fieldValue + " | Exceptions Message: " + ex.Message);
            }
        }
        if (users.Count > 0)
        {
            newListItem[fieldName] = users;
            newListItem.Update();
        }
    }
  • the above piece did not work for CSOM, issues with new OfficeOpenXml.ExcelPackage())any other option? – MJaiswal Feb 19 '16 at 9:53
  • OfficeOpenXml.ExcelPackage()) gives namespace error – MJaiswal Feb 19 '16 at 10:09
  • Have you downloaded the EPPlus DLL, referenced it in your project and added Using OfficeOpenXml to your cs code file? – SlackerAmit Feb 19 '16 at 11:40
  • am facing an issue related to the above sample code, i am having a hyperlink cewll in my excel and i want to insert these hyperlink cell values into a hyperlink field into a splist , such that, when on click of an button in my visual web part/Ui, i need to import the excel contents including the hyperlink cell values with clickable functionality., – prasadwt Sep 28 '17 at 11:52
  • am not able to get/extract the hyperlink cell from excel. how to extract the hyperlink url and display name , and its inserted as a hyperlink field( i dont have issues if its a single line of text, ) and end user clicks, it shud navigate to the link's targeted location. like url is "google.com" and display name is "google" – prasadwt Sep 28 '17 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.