There are many ways to upload data from excel in SharePoint. We can divide them in two categories

  1. SharePoint on premise: There is a good article which describes what are the ways available without any programming you can refer to it here: https://sharepointmaven.com/3-ways-import-excel-sharepoint/ . There are challenges in these methods, the biggest is that it does not allow all Column Types such as Metadata , Choice etc. Out of three explained there Import from excel is best but it will not allow Metadata column to be created
  2. SharePoint Online: Recently in SharePoint Online native support has been provided by Microsoft on uploading data from excel to SharePoint, there are few limitations but it works pretty well, you can refer to this article which has great details https://sharepointmaven.com/how-to-import-an-excel-spreadsheet-to-a-sharepoint-custom-list/

My blog talks about how you can use C# to upload data from excel to SharePoint.

Step 1: Read data from excel in C#: To do this the code is very simple but you need to have excel installed on the machine you are running the code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel; 

//Instantiate excel app and if not installed give a message
Application excelApp = new Application();
            if (excelApp == null)
            {
                Console.WriteLine("Excel is not installed!!");
                return;
            }

//load the workbook from where data needs to be read           
 Workbook excelBook = excelApp.Workbooks.Open(@"path of excel file including file name");
            _Worksheet excelSheet = excelBook.Sheets[1];
            Range excelRange = excelSheet.UsedRange;

            //get the number of rows and columns
            int rows = excelRange.Rows.Count;
            int cols = excelRange.Columns.Count;


//loop through them and print
            for (int i = 1; i <= rows; i++)
            { 
               for (int j = 1; j <= cols; j++)
               {

                //write the console
                if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null)
                {
                    Console.Write(excelRange.Cells[i, j].Value2.ToString() + "\t");

               }
}



Step 2: Add data to SharePoint list: Note that there are different column types supported by SharePoint but my main focus area is to how to insert data in column type Choice, Number, Metadata, Multi line text

 string siteUrl = "site url where data needs to be uploaded"
//create a context 
 ClientContext clientContext = new ClientContext(siteUrl);
//get the list in which data needs to be inserted
 Microsoft.SharePoint.Client.List list = clientContext.Web.Lists.GetByTitle("name of the list");
 //preparing list item which will be added                                                                                                
ListItemCreationInformation itemInfo = new ListItemCreationInformation();
ListItem listItemCreation = list.AddItem(itemInfo);

//this is needed for taxonomy / metadata field
 var taxonomySession = TaxonomySession.GetTaxonomySession(clientContext);
 var termStore = taxonomySession.GetDefaultSiteCollectionTermStore();

Once above is done we have to combine both so rest of the insert data code will be under 2nd for loop in read excel data part. Here we will assume that 1st column is a number, 2nd column a multi line text, 3rd a Taxonomy / Managed Metadata column, fourth a number column and fifth a choice column, please check the complete code below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Taxonomy;
using System.Net.NetworkInformation;
using System.IO;
using System.Net;

namespace UploadDatatoSPfromExcel
{
    class Program
    {



        static void Main(string[] args)
        {

string siteUrl = "site url where data needs to be uploaded"
//create a context 
 ClientContext clientContext = new ClientContext(siteUrl);
//get the list in which data needs to be inserted
 Microsoft.SharePoint.Client.List list = clientContext.Web.Lists.GetByTitle("name of the list");
 //preparing list item which will be added                                                                                                
ListItemCreationInformation itemInfo = new ListItemCreationInformation();
ListItem listItemCreation = list.AddItem(itemInfo);

//this is needed for taxonomy / metadata field
 var taxonomySession = TaxonomySession.GetTaxonomySession(clientContext);
 var termStore = taxonomySession.GetDefaultSiteCollectionTermStore();
//Instantiate excel app and if not installed give a message
Application excelApp = new Application();
            if (excelApp == null)
            {
                Console.WriteLine("Excel is not installed!!");
                return;
            }

//load the workbook from where data needs to be read           
 Workbook excelBook = excelApp.Workbooks.Open(@"path of excel file including file name");
            _Worksheet excelSheet = excelBook.Sheets[1];
            Range excelRange = excelSheet.UsedRange;

            //get the number of rows and columns
            int rows = excelRange.Rows.Count;
            int cols = excelRange.Columns.Count;


//loop through them and print
            for (int i = 1; i <= rows; i++)
            { 

//we have to define field / column name for each column in which data will //be inserted, note that each name should be internal name of SharePoint //column

string fieldNameNumber = "FieldNumber";
string fieldNameMultiLineText = "FieldMultilineText";
string fieldNameTaxonomy = "FieldTaxonmy";
string fieldNameNumber = "FieldNumber";
string fieldNameChoice = "FieldChoice"



               for (int j = 1; j <= cols; j++)
               {

                //write the console
                if (excelRange.Cells[i, j] != null && excelRange.Cells[i, j].Value2 != null)
                {
                    Console.Write(excelRange.Cells[i, j].Value2.ToString() + "\t");

//I am skipping first row as it contains column name
if (j == 1 && i!=1)
                    {

//setting number column                  
      listItemCreation [fieldNameNumber ] = excelRange.Cells[i, j].Value2.ToString();

                    }

if (j == 2 && i!=1)
                    {

//setting multi line text column                  
      listItemCreation [fieldNameMultiLineText] = excelRange.Cells[i, j].Value2.ToString();

                    }

if (j == 3 && i!=1)
                    {

//setting fieldNameTaxonomy  column                  
      string fieldTaxonomyValue = excelRange.Cells[i, j].Value2.ToString();
//create variable to store GUID value of metadata      
Guid CustomertermIdGuid = Guid.Empty;
//Important to set correct term set name here
var allTerms = termStore.GetTermSetsByName("<<name of term set>>", 1033);
var termSet = allTerms.GetByName("<<name of term set>>");
                        
var terms = termSet.GetAllTerms();
                        clientContext.Load(terms, includes => includes.Include(k => k.Id, k => k.Name, k => k.Parent, k => k.Parent.Id, k => k.Parent.Name, k => k.IsRoot, k => k.LocalCustomProperties));
                        clientContext.ExecuteQuery();
                        foreach (var term in terms)
                        {
                            if (term.Name == Customer)
                            {
                                CustomertermIdGuid = term.Id;
                            }


                        }


                        fieldTaxonomyValue = Customer + '|' + CustomertermIdGuid.ToString();
                        

                    }

if (j == 4 && i!=1)
                    {

//setting number column                  
      listItemCreation [fieldNameNumber] = excelRange.Cells[i, j].Value2.ToString();

                    }

if (j == 5 && i!=1)
                    {

//setting chpice column                  
      listItemCreation [fieldNameChoice] = excelRange.Cells[i, j].Value2.ToString();

//since this is the last column we should insert value in SharePoint as well

            listItemCreation.Update();
            clientContext.Load(listItemCreation);
            clientContext.ExecuteQuery();

                    }

               }
}









        } 
   } 

}

Important point to note is how I have set the Managed Metadata column, first we we need to get all terms from term set and then find correct GUID else it will not work.

Code is not structured very well but you can refer main parts to write your own code.

Subscribe to my YouTube Channel https://www.youtube.com/channel/UC6vLzWN-3aFG8dgTgEOlx5g

Connect with me on Twitter or Linked In and follow YouTube Channel or my blog https://twitter.com/SinhaKislay

https://www.linkedin.com/in/kislaysinha

/https://synkventures.com

/https://www.youtube.com/channel/UC6vL…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s