Using CSOM to Import CSV Files Into Lists in Office 365/SharePoint Online

While working on a recent project, I needed to import a large Excel spreadsheet into a custom list in SharePoint Online. Microsoft provides the Import Spreadsheet add-in for this purpose—this will allow you to create a new list with each item corresponding to an Excel record. However, the spreadsheet I needed to import included columns that needed to be mapped to Managed Metadata terms and Users, which is (understandably) not supported by the add-in. A few Google searches led me to try copying and pasting some of the records. This showed more promise, but I still had issues with the aforementioned field types—the entered users (by email address) were only resolved sporadically, and the managed metadata terms never seemed to work correctly.

Since the out-of-the-box solutions weren’t working for this spreadsheet, I decided to leverage the Client-Side Object Model (CSOM) and create a console app that could import the spreadsheet. This post will walk through the implementation of the app, highlighting in particular the code for setting managed metadata fields, people fields, and lookup fields.

Get full source code here.

Note: the app does not fully account for incomplete or invalid data. Based on the quality of your data or business requirements, it is likely that more robust error handling would need to be added. In addition, this does not handle the implementation for setting publishing-specific fields (though it may work for some of them).   Setting up the Environment

In order to read the data from the spreadsheet (which I converted to a CSV file), I added the CsvHelper library (available on NuGet—just search for CsvHelper)

CsvHelper makes it easy to map records in a CSV file to C# (or VB) objects. The only dependencies are:

1. The column names in the spreadsheet must match the column names in your C# object.

2. The types specified in both locations are compatible.

   1. a. If you have a Currency field in the spreadsheet, make sure this is set to String in C#—the value is imported with the currency symbol. SharePoint correctly handles this when setting the field, so no additional work is needed.

CSV file (truncated due to length)

CsvRecord.cs (entity representing a record in the spreadsheet)

public class CsvRecord { public string Title { get; set; } public string Choice_x0020_Field { get; set; } public float Number_x0020_Field { get; set; } public string Currency_x0020_Field { get; set; } public DateTime Date_x0020_Field { get; set; } public string Lookup_x0020_Field { get; set; } public bool Yes_x002F_No_x0020_Field { get; set; } public string Person_x0020_Field { get; set; } public string Hyperlink_x0020_Field { get; set; } public string MM_x0020_Field { get; set; }}

GetRecordsFromCsv (Method to map the spreadsheet data to a list in C#)

private static List GetRecordsFromCsv(){ List records = new List(); using (var sr = new StreamReader(csvPath)) { var reader = new CsvReader(sr); records = reader.GetRecords().ToList(); }   return records;}

You’ll also need to add the following assemblies to your project in order to write CSOM code:

• SharePoint.Client

• SharePoint.Client.Runtime

• SharePoint.Client.Taxonomy (for managed metadata)

Connecting to SharePoint and Checking for Existing Records

The following code snippet represents the main method in the console app. The main focus of this post is to detail the logic within the AddNewListItem method, but I wanted to briefly outline the sequence in the code below:

1. Instantiate the SharePoint context.

2. Read data from the target CSV file and convert to a list of a custom C# entity (using the previously mentioned GetRecordsFromCsv method).

3. Instantiate the SharePoint list.

4. For each imported record, determine if a matching record already exists in the SharePoint list by using a CAML query.

1. a. If so, move on to the next record.

2. b. If not, call

AddNewListItem

Main

const string csvPath = "C:\filePath.csv";static void Main(){ try { //Get site URL and credentials values from config Uri siteUri = new Uri(ConfigurationManager.AppSettings["SiteUrl"].ToString()); var accountName = ConfigurationManager.AppSettings["AccountName"]; char[] pwdChars = ConfigurationManager.AppSettings["AccountPwd"].ToCharArray();   //Convert password to secure string System.Security.SecureString accountPwd = new System.Security.SecureString(); for (int i = 0; i < pwdChars.Length; i++) { accountPwd.AppendChar(pwdChars[i]); }   //Connect to SharePoint Online using (var clientContext = new ClientContext(siteUri.ToString()) { Credentials = new SharePointOnlineCredentials(accountName, accountPwd) }) { if (clientContext != null) { //Map records from CSV file to C# list List records = GetRecordsFromCsv(); //Get config-specified list List spList = clientContext.Web.Lists.GetByTitle(ConfigurationManager.AppSettings["ListName"]);   foreach (CsvRecord record in records) { //Check for existing record based on title (assumes Title should be unique per record) CamlQuery query = new CamlQuery(); query.ViewXml = String.Format("@" +"" + "{0}" +"", record.Title); var existingMappings = spList.GetItems(query); clientContext.Load(existingMappings); clientContext.ExecuteQuery();   switch (existingMappings.Count) { case 0: //No records found, needs to be added AddNewListItem(record, spList, clientContext); break; default: //An existing record was found - continue with next item continue; } } } } } catch (Exception ex) { Trace.TraceError("Failed: " + ex.Message); Trace.TraceError("Stack Trace: " + ex.StackTrace); }}

A few additional notes:

1. This code explicitly uses my credentials in order to connect to SharePoint Online. If you’re looking at creating this as a custom add-in, you can either use the same implementation with a service account’s credentials or use app-only permissions.

2. At the beginning of the main foreach loop, the code checks to see if an item already exists with the same title as the to-be-imported spreadsheet record. If so, it moves on to the next record. This allows you to run the app multiple times without duplicating the same records (assuming the record title should be unique for all items). An enhanced implementation would likely update the matching list item with the current values in the spreadsheet as opposed to simply continuing to the next item. I’ll leave that to you as a user exercise.

Adding the List Item

The code below shows the simple implementation for adding the list item—support for complex types will begin in the next section.

The logic here works as follows:

1. Instantiate a Dictionary variable to store all the field names and values locally. We will loop through this after processing all of the properties to set the field values for the new item. The reason I’m not just creating the list item at the beginning and updating each field directly is because a query needs to be run for each property, and this caused the item to be added with incomplete data.

2. Use reflection to iterate through the properties of the record.

3. For each non-empty property, load its matching SharePoint site column. My code assumes the property names of your custom entity equal the internal names of the corresponding site columns.

4. Depending on the field’s FieldTypeKind property, set the appropriate variable for the field value, and add it plus the field name to the Dictionary. The code below shows only the default implementation, which simply uses the inherent property value from the imported record. This works for the following field types:

a. Text b. Choice c. Number d. Currency e. Date and Time f. Boolean g. Hyperlink (the description field will be set to the URL as well) h. Task OutcomeAnd possibly more. However, it does not work for Users, Lookup Fields, or Managed Metadata.

5. Create the new list item, set its field values based on the items in the Dictionary, and persist the changes.

a. Dictionary, and persist the changes.

AddNewListItem

private static void AddNewListItem(CsvRecord record, List spList, ClientContext clientContext){ //Instantiate dictionary to temporarily store field values Dictionary<string, object> itemFieldValues = new Dictionary<string, object>(); //Use reflection to iterate through the record's properties PropertyInfo[] properties = typeof(CsvRecord).GetProperties(); foreach (PropertyInfo property in properties) { //Get property value object propValue = property.GetValue(record, null); //Only set field if the property has a value if (!String.IsNullOrEmpty(propValue.ToString())) { //Get site column that matches the property name //ASSUMPTION: Your property names match the internal names of the corresponding site columns Field matchingField = spList.Fields.GetByInternalNameOrTitle(property.Name); clientContext.Load(matchingField); clientContext.ExecuteQuery();   //Switch on the field type switch (matchingField.FieldTypeKind) { //TODO: implement cases for complex types default: itemFieldValues.Add(matchingField.InternalName, propValue); break; } } }   //Add new item to list ListItemCreationInformation creationInfo = new ListItemCreationInformation(); ListItem oListItem = spList.AddItem(creationInfo);   foreach (KeyValuePair<string, object> itemFieldValue in itemFieldValues) { //Set each field value oListItem[itemFieldValue.Key] = itemFieldValue.Value; } //Persist changes oListItem.Update(); clientContext.ExecuteQuery();}

Setting a User Field

The Microsoft.SharePoint.Client assembly includes a FieldUserValue entity, which specifically represents the value for a user field. In order to properly set the user field for a new list item, we’ll need to create a FieldUserValue instance and set its LookupId to the relevant User ID.

The GetUserFieldValue method below shows how to find the User ID based on a userName variable, which can be an email address (which I used in my spreadsheet), display name, or login name.

If you already have the login name, you can skip the call to Utility.ResolvePrincipal and call EnsureUser directly to get the User ID.

GetUserFieldValue

private static FieldUserValue GetUserFieldValue(string userName, ClientContext clientContext) { //Returns first principal match based on user identifier (display name, email, etc.) ClientResult principalInfo = Utility.ResolvePrincipal( clientContext, //context clientContext.Web, //web userName, //input PrincipalType.User, //scopes PrincipalSource.All, //sources null, //usersContainer false); //inputIsEmailOnly clientContext.ExecuteQuery(); PrincipalInfo person = principalInfo.Value;   if (person != null) { //Get User field from login name User validatedUser = clientContext.Web.EnsureUser(person.LoginName); clientContext.Load(validatedUser); clientContext.ExecuteQuery();   if (validatedUser != null && validatedUser.Id > 0) { //Sets lookup ID for user field to the appropriate user ID FieldUserValue userFieldValue = new FieldUserValue(); userFieldValue.LookupId = validatedUser.Id; return userFieldValue; } } return null;}

Inside the switch statement in the AddNewListItem method, we’ll add a case statement to call GetUserFieldValue and add the result to the Dictionary.

User case statement

case FieldType.User: FieldUserValue userFieldValue = GetUserFieldValue(propValue.ToString(), clientContext); if (userFieldValue != null) itemFieldValues.Add(matchingField.InternalName, userFieldValue); else throw new Exception("User field value could not be added: " + propValue.ToString()); break;

Setting a Lookup Field

We’ll implement a similar process to get the lookup value for a field, this time returning a FieldLookupValue.

In a nutshell, we need to retrieve the ID of the lookup list item that has the same value as the record to be imported, and set the LookupId of the FieldLookupValue to that ID.

We’ll first need to specify the title of the lookup list, the name of the lookup field, and the type of the lookup field in the configuration:

app.config

We’ll then use that information to execute a CAML query against the items in that list in order to find the correct ID.

GetLookupFieldValue

(Most of this code is sourced from Karine Bosch’s blog post)

public static FieldLookupValue GetLookupFieldValue(string lookupName, string lookupListName, ClientContext clientContext){ //Ref: Karine Bosch - https://karinebosch.wordpress.com/2015/05/11/setting-the-value-of-a-lookup-field-using-csom/ var lookupList = clientContext.Web.Lists.GetByTitle(lookupListName); CamlQuery query = new CamlQuery(); string lookupFieldName = ConfigurationManager.AppSettings["LookupFieldName"].ToString(); string lookupFieldType = ConfigurationManager.AppSettings["LookupFieldType"].ToString();   query.ViewXml = string.Format(@"{2}" + "", lookupFieldName, lookupFieldType, lookupName);   ListItemCollection listItems = lookupList.GetItems(query); clientContext.Load(listItems, items => items.Include (listItem => listItem["ID"], listItem => listItem[lookupFieldName])); clientContext.ExecuteQuery();   if (listItems != null) { ListItem item = listItems[0]; FieldLookupValue lookupValue = new FieldLookupValue(); lookupValue.LookupId = int.Parse(item["ID"].ToString()); return lookupValue; }   return null;}

We’ll then create a new case statement that calls this method for user fields:

Lookup case statement

case FieldType.Lookup: FieldLookupValue lookupFieldValue = GetLookupFieldValue(propValue.ToString(), ConfigurationManager.AppSettings["LookupListName"].ToString(), clientContext); if (lookupFieldValue != null) itemFieldValues.Add(matchingField.InternalName, lookupFieldValue); else throw new Exception("Lookup field value could not be added: " + propValue.ToString()); break;

Setting a Managed Metadata Field

Handling managed metadata fields follows the same pattern: we’ll set up a method that will create a new instance of the designated field for that type (TaxonomyFieldValue, in this case) and set its TermId. The app implementation assumes the record value will match the label of a managed metadata term.

GetTaxonomyFieldValue

(Most of this code is sourced from Steve Curran’s blog post)

public static TaxonomyFieldValue GetTaxonomyFieldValue(string termName, Field mmField, ClientContext clientContext){ //Ref: Steve Curran - http://sharepointfieldnotes.blogspot.com/2013_06_01_archive.html //Cast field to TaxonomyField to get its TermSetId TaxonomyField taxField = clientContext.CastTo(mmField); //Get term ID from name and term set ID string termId = GetTermIdForTerm(termName, taxField.TermSetId, clientContext); if (!string.IsNullOrEmpty(termId)) { //Set TaxonomyFieldValue TaxonomyFieldValue termValue = new TaxonomyFieldValue(); termValue.Label = termName; termValue.TermGuid = termId; termValue.WssId = -1; return termValue; } return null;}

The logic to retrieve the Term ID based on the term name and term set ID is somewhat verbose, so it’s been extracted to a utility method:

GetTermIdForTerm

public static string GetTermIdForTerm(string term, Guid termSetId, ClientContext clientContext){ //Ref: Steve Curran - http://sharepointfieldnotes.blogspot.com/2013_06_01_archive.html string termId = string.Empty;   //Get term set from ID TaxonomySession tSession = TaxonomySession.GetTaxonomySession(clientContext); TermStore ts = tSession.GetDefaultSiteCollectionTermStore(); TermSet tset = ts.GetTermSet(termSetId);   LabelMatchInformation lmi = new LabelMatchInformation(clientContext);   lmi.Lcid = 1033; lmi.TrimUnavailable = true; lmi.TermLabel = term;   //Search for matching terms in the term set based on label TermCollection termMatches = tset.GetTerms(lmi); clientContext.Load(tSession); clientContext.Load(ts); clientContext.Load(tset); clientContext.Load(termMatches);   clientContext.ExecuteQuery();   //Set term ID to first match if (termMatches != null && termMatches.Count() > 0) termId = termMatches.First().Id.ToString();   return termId;}

Like the previous two complex types, we’ll create a case statement to call this method when processing a managed metadata field. However, this type is a little different, because it has a FieldType of “Invalid”, along with several other column types (like HTML and many of the publishing-specific types). So we’ll also have to add an additional check to make sure the field is managed metadata:

Managed metadata case statement:

case FieldType.Invalid:switch (matchingField.TypeAsString){ case "TaxonomyFieldType": TaxonomyFieldValue taxFieldValue = GetTaxonomyFieldValue(propValue.ToString(), matchingField, clientContext); if (taxFieldValue != null) itemFieldValues.Add(matchingField.InternalName, taxFieldValue); else throw new Exception("Taxonomy field value could not be added: " + propValue.ToString()); break; default: //Code for publishing site columns not implemented continue;}break;

Conclusion

Although this code doesn’t cover every possible scenario when importing spreadsheet records to a list, I hope it gives you a good starting point for your own development.

If you have questions or comments about this blog post, or other aspects of SharePoint Online development, please leave a comment below, send us a tweet at @CrederaMSFT, or contact us online.

• SharePoint
• Office 365
• CSOM
• CSV Files