import excel sheet data with column header and update the sharepoint list based on any primary key in O365

Question

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

Answer 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();
        }
    }