Upload Excel data into SharePoint Custom List

4.33 / 5 5
1 / 5
2 / 5
3 / 5
4 / 5
5 / 5

9 votes, 4.33 avg. rating (87% score)

Introduction

In this post we will see how to upload excel data(using oledb provider) into SharePoint 2007 / SharePoint 2010 Custom List from object model(c#)
What are the points that are covered

Get the sheet name from excel file rather than providing static name
Builds connection string for .xls and .xlsx files
Reads excel data and inserts into SharePoint custom list

Upload excel data

The following code reads data from contacts.xls and inserts into SharePoint Custom List ContactList which I have created.
According to the schema of the ContactList, the method InsertIntoList() in the following code has relevant code.
You can modify according to the schema of your Custom List and Excel file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Data;
using System.Data.OleDb;
using Microsoft.SharePoint;

namespace TestSharepointProject
{
    public class UploadExcelData
    {
        public void LoadExcelData()
        {
            string fileName = "@"C:\AdisGroup\Contacts\contacts.xls";
            //if you are using file upload control in sharepoint get the full path as follows assuming fileUpload1 is control instance
            //string fileName = fileUpload1.PostedFile.FileName
 
            string fileExtension = Path.GetExtension(fileName).ToUpper();
            string connectionString = "";

            if (fileExtension == ".XLS")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + fileName + "'; Extended Properties='Excel 8.0;HDR=YES;'";
            }
            else if (fileExtension == ".XLSX")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + fileName + "';Extended Properties='Excel 12.0 Xml;HDR=YES;'";
            }
            if (!(string.IsNullOrEmpty(connectionString)))
            {
                string[] sheetNames = GetExcelSheetNames(connectionString);
                if ((sheetNames != null) && (sheetNames.Length > 0))
                {
                    DataTable dt = null;
                    OleDbConnection con = new OleDbConnection(connectionString);
                    OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + sheetNames[0] + "]", con);
                    dt = new DataTable();
                    da.Fill(dt);
                    InsertIntoList(dt,"ContactList");
                }
            }
        }

        private string[] GetExcelSheetNames(string strConnection)
        {
            var connectionString = strConnection;
            String[] excelSheets;
            using (var connection = new OleDbConnection(connectionString))
            {
                connection.Open();
                var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (dt == null)
                {
                    return null;
                }
                excelSheets = new String[dt.Rows.Count];
                int i = 0;

                // Add the sheet name to the string array.
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets[i] = row["TABLE_NAME"].ToString();
                    i++;
                }
            }
            return excelSheets;
        }

        private void InsertIntoList(DataTable listTable, string contactListName)
        {

            SPWeb mySite = null;
            try
            {

                mySite = SPContext.Current.Web; //create web object if context is null
                mySite.AllowUnsafeUpdates = true;
                SPList contactList = mySite.Lists[contactListName];
                for (int iRow = 0; iRow < listTable.Rows.Count; iRow++)
                {
                    SPListItem newContact = contactList.Items.Add();
                    newContact["FirstName"] = Convert.ToString(listTable.Rows[iRow][0]);
                    newContact["LastName"] = Convert.ToString(listTable.Rows[iRow][1]);
                    newContact["FullName"] = Convert.ToString(listTable.Rows[iRow][2]);
                    newContact["LoginID"] = Convert.ToString(listTable.Rows[iRow][3]);
                    newContact["EmailAddress"] = Convert.ToString(listTable.Rows[iRow][4]);
                    newContact["PhoneNumber"] = Convert.ToString(listTable.Rows[iRow][5]);
                    newContact["Company"] = Convert.ToString(listTable.Rows[iRow][6]);
                    newContact.Update();
                }
                mySite.AllowUnsafeUpdates = false;
            }
            catch (Exception ex)
            {
                //log exception
            }
            finally
            {
                if (mySite != null) //don't dispose if the site is from SPContext
                {
                    mySite.AllowUnsafeUpdates = false;
                }
            }
                  
        }

    }
}

Remarks

In the above code
LoadExcelData method takes data from first sheet name i.e sheetNames[0]. Change this if you want to load from another sheetName
InsertIntoList method uses SPContext to get the current web object. If you are using the above code where SPContext is not available then you have to create SPWeb object and dispose it in finally block

Conclusion

Hope you understand how to load excel data into Custom List in SharePoint using object model.

Email this page

June 14, 2012 · Adi · 49 Comments
Tags: Export excel, Export excel data into SharePoint, Upload Excel data into SharePoint Custom List · Posted in: Sharepoint 2007, Sharepoint 2010

49 Responses

Anuj Govil - August 7, 2012

Great Post…!! It saves lot of time for me.. :).
Thanks alot.
Billy Williams - October 8, 2012

I don’t understand how am I supposed to utilize this? as a console application? event receiver? visual web part?
Adi - October 9, 2012

Hi Billy Williams,
Its an utility functions which can be used any where
ex: If you want to use it in webpart, provide file path or use any file upload control. Once you get the path from the users, use the code as in the post
D. Chaney - December 7, 2012

Is it possible to use this code in a workflow which will run daily?
Adi - December 7, 2012

Hi Chaney,
It is possible to use, there shouldn’t be a problem.
D. Chaney - December 14, 2012

excelSheets[i] = row[“TABLE_NAME”].ToString();
i++;

Where does this TABLE_NAME come from?
Adi - December 14, 2012

Hi Chaney,
In the method GetExcelSheetNames, the datatable ‘dt’ will be filled with connection.GetOleDBSchemaTable method

var dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

Oledb connection object return the table in a specific format
dt.Columns[0].ColumnName will be “TABLE_CATALOG”
dt.Columns[1].ColumnName will be “TABLE_SCHEMA”
dt.Columns[2].ColumnName will be “TABLE_NAME”

For our purpose we have to use the third column i.e. “TABLE_NAME”

Hope your query is resolved now
Raghavendra - December 24, 2012

Hi Adi,

I implemente this as part of a webpart under a button clieck event. But the application is taking excel sheet from the server rather than the client machine. I am having the same path in both server and client machine. When i run the application, it is reading data from the shett existing in server side. Please reply me soon incase u see my mail, as i am in very urget need of this solution.
Adi - December 24, 2012

Hi Raghavendra,
If you are using it in the webpart, try taking the path from any fileupload control. See line 17 in the code how to take the file path from client machine. If you are using direct string with path, it will always check the path in the server not the client side.
Let me know if you are stuck.

Regards,
Adi
Raghavendra - December 24, 2012

Hi Adi, Thanks a lot for immediate help. Actally i did the same, i am taking the path from file upload control, just like you told in 17 line. But the result is same, it is again looking for the selected path from control in the server.

I added file upload control, just to take the path when user browse and select the file. am not uploading any where. In the button (submit) clieck event, i have taken your code. But application is searching for the control returned path in server.

Will it be solved, if i use your other approach with interop method. Thanks in advance.

Adi - December 24, 2012

oops..I got the problem.
Method 1: Save the file to any default location in server. After all your process is completed try to delete the uploaded excel file if required

string filename = Path.GetFileName(fileUpload1.FileName);
fileUpload1.SaveAs(Server.MapPath("~/") + filename);

Method 2:
Without saving the file you can use file stream to build the datatable and create list out of it, for example

 List<string> rows = new List<string>();
            using (StreamReader reader = new StreamReader(fileUpload1.PostedFile.InputStream))
            {
                string record = reader.ReadLine();
                while (record != null)
                {
                    rows.Add(record);
                    record = reader.ReadLine();
                }

            
                foreach (string s in rows)
                {
                    //write code to prepate datatable out of the rows
                }
            }

If you are still stuck, may be I can post some sample code later

Regards,
Adi

Deep - July 3, 2013

HI Adi,

I am using above code but not able to update excel item in list. I am using file upload control and saving it to temp location.
data from excel file is getting easily and able to bind in Grid.
fields are also coming in insertlist method but unable to upload in LISt. update into list method is working wihtout error but not inserting values of exel

please suggest me what to do
Adi - July 3, 2013

Hi Deepak,
The above code “InsertIntoList” method inserts new items to SharePoint list but will not update.
SPListItem newContact = contactList.Items.Add(); — this creates new item in the list

I did not get your point that “update into list method is working without error but not inserting values of excel”
Can you give somemore info so that I can help?

To make it simple for inserting item into list, following is the code (assuming FirstName column exists in your list)
SPListItem newContact = contactList.Items.Add();
newContact[“FirstName”] = “Adi”;
newContact.Update()

Is the above code inserting item in your list? Let me know if you are stuck at any point.

Regards,
Adi
Deep - July 3, 2013

Thanks Adi,
For quick reply..

I found the error.. Silly mistake i have done.

I was using oList.Update();//list object
resolved by using item object : oListItem.Update();
Adi - July 3, 2013

cool, your issue got resolved
Deep - July 5, 2013

HI Adi,

for above example, How we can avoid duplicate entries in LIST.
If some one uploading excel sheet again with updated records.

Thanks in advance.
Balaji - July 25, 2013

Hi Adi,

My requirement is when page load happens,I should fetch the latest data from excel to sharepoint list and subsequently we use Visio to display the data to end-users. Is it possible to use the above code to bring Excel data to Sharepoint list?

Thanks in advance for your help!

Thanks,
Balaji
Adi - July 25, 2013

Hi Balaji,

Yes you can use the code. Make sure you modify the InsertIntoList method with your respective list schema and also change excel file location.

Cheers,
Adi
Balaji - July 26, 2013

Thanks Adi for your quick reply. I need to upload the excel data into list on page load without using any controls. I tried with Content Editor web part but it’s not working. I think am using wrong web part for this. It would be great and helpful if i get assistance from you on the usage of web part for this code.

Thanks,
Balaji
Adi - July 26, 2013

Hi Balaji,

There is no outofbox webpart which can execute custom code on the fly. In your case you have to create a custom webpart with the above code and deploy it. Then in your page you add the custom webpart same like adding content editor webpart to the page.
After that whenever pageloads the code will get executed. Hope you understood the procedure. Let me know if you are still stuck up.

Thanks,
Adi
Usha - July 26, 2013

Hi Adi,

I have created a webpart with fileupload control which takes excel sheet and exports the excel data into the custom sharepoint list.I’m facing issue with the person lookup field while adding userid’s to it. I’m getting invalid lookup value error.Could you please provide some solution on how to add data to the person or group lookup field?I know one way of adding the data to it i.e., ’87#,Rama krishna Mekela’ , but it won’t be possible to mention id’s for all the users in excel column.

Please assist on this issue.Thanks in advance.

Usha.
Adi - July 26, 2013

Hi Usha,

Person or group value should not be given as text unless you try tweaking with userid which is not suggestible. You should provide SPFieldUserValue to the Person or group column.
Consider Line 84 in the above code
This adds listTable.Rows[iRow][0] value to FirstName column, which is straight forward way.
newContact["FirstName"] = Convert.ToString(listTable.Rows[iRow][0]);

Considering your list has “UserID” column, and your excel sheet column has value coming from listTable.Rows[iRow][7] (assuming value is Rama Krishna Mekela)
You introduce one more method for that.
newContact["UserID"] = GetUserValue(Convert.ToString(listTable.Rows[iRow][7]),mySite);
public SPFieldUserValue GetUserValue(string userid, SPWeb site) { SPUser requireduser = site.EnsureUser(userid); SPFieldUserValue uservalue = new SPFieldUserValue(site, requireduser.ID, requireduser.LoginName); return uservalue; }

This will convert your string ‘Rama Krishna Mekela’ to respective user id. You update the code with this method and it should resolve your requirement.

One more point to re check:
I am assuming your excel sheet cell have only one user id. If you have multiple user ids in the cell i.e. listTable.Rows[iRow][7] gets user1;user2;user3; then you have to modify code to split and add SPFieldUserValue into SPFieldUserValueCollection

Hope this reply help you.

Cheers,
Adi
Usha - July 27, 2013

Hi Adi,

Thanks for your help.My issue was resolved.Many thanks.

Usha
Jeswin - August 21, 2013

Dear Adi,

I am getting dt.Rows.Count=0.Bcz of that it is not going inside the for loop. Please help me on this. Or can you send me the excel file for reference.

Regards,
Jeswin
Adi - August 23, 2013

Hi Jeswin,

There is no special in my excel. It is a normal one.
What I understood from your comment is that, you are unable to get the sheet names from the method used in Line 33.
string[] sheetNames = GetExcelSheetNames(connectionString);
Can you try giving the Sheet names in static like below? Comment Line 33 and use below one. Replace “Sheet1”,.. with your actual workbook names.
String[] excelSheets = new string[]{“Sheet1″,”Shee2″,”Sheet3”};

If it is working then we can narrow down your problem to getting sheet names from excel. Let me know the outcome.

Regards,
Adi
Jeswin - August 24, 2013

Dear Adi,

Thanks for the Quick Response. I figure out the issue. The issue is with the File Upload Control. So Using Jquery I passed the URL of File Upload control into One text box and passing that textbox value as file Name. So my issue resolved. Thanks. And I have one more doubt. There is any issue in Security of a file.Bcz i tried to access the file from Downloads folder, I am getting error in GetExcelSheetNames(connectionString); method.

Regards,
Jeswin
Adi - August 24, 2013

Hi Jeswin,

The issue might be due to the folder permission. I suppose there isn’t modification that can be done to the method to resolve folder level security.
Anyhow what is the error you are getting?

Regards,
Adi
Usha - October 2, 2013

Hi Adi,

The fileupload control in my webpart will store the excel documents in the C drive folder located on the server (like “@”C:\tracker”)and while inserting data into sharepoint list the excel file will be picked up from this location. The users are getting access denied error while uploading files using fileupload control. I think i’m not getting this error since i have access to the server. Could you please help me in resolving this issue?

Thanks in advance.

Usha.
Adi - October 2, 2013

Hi Usha,

You better run the code with runwithelevated previlages. This will run the code on Service Account credentials. Give permission to the folder for Service Account.
This should resolve access denied error as every user will uploading impersonated with Service Account
(Service Account is the user credentials which is set in your IIS application pool of the SharePoint Web Application)

Hope this helps.

Regards,
Adi
Nakul - November 3, 2013

I am using the upload control in webpart. The code is running fine for the first time for file A. However when I try to upload aany other file B. The code is still referencing file A. I am using same code which is mentioned at the top of this article I have commented the hardcoded path uncommented this line mentioned below,

string fileName = fileUpload1.PostedFile.FileName

Can you please help me on this. Thanks in advance!
Adi - November 4, 2013

Hi Nakul,
Can you please try this one
String fileName = FileUpload1.FileName;

Let me know if you are still facing issues.
Regards,
Adi
Shandeep - April 22, 2014

@Adi, im currently using SP2013. And I have lookup values.
The list must also gets the lookup values when the excel is uploaded. I have another code and I’m just looking for alternation to make the lookup values saved. Do u have a suggestion for this ?
Adi - April 27, 2014

Hi Sandeep,
If it is lookup value, then you need to get the id of the value from lookup list. Then insert that value.

Ex:Suppose, in excel you have Country value “India” which is lookup value from the list ‘CountryList’. Before inserting the value “India” as new item, write a method that will give you the ID of the value “India” from the “CountryList”. Then insert that id. This will suit to your requirement.

Thanks and Regards,
Adi
Vale - May 16, 2014

Hello! I’m interested in the method 2 to use file stream, but I don’t know where to put the code to build the datatable and insert into the list.

It must be into foreach?

foreach (string s in rows)
{
string[] sheetNames = GetSheetNames(connectionString);
if((sheetNames != null) && (sheetNames.Length > 0))
{
DataTable dt = null;
…

dt = new DataTable();
da.Fill(dt);
InsertIntoList(dt, “ContactList”);
}
}

Please help me and thanks in advance!
Dharmendra Yadav - June 16, 2014

My requirement is to upload a .xlsx file into document library of sharepoint 2010 and have to update the list item in another list.
I am uploading excel sheet(.xlsx) but when i am trying to fill the data table with excel value it is giving me error “External table is not in expected format”.
Kindly tell me how to resolve this issue.
Oscar Acevedo - June 25, 2015

Great Work bro…
Kiran Kumar Talkkoti - June 30, 2015

Hi Adi,

Can you pls guide me how to add lookup values to list from excel column:

Example :
newContact[“Manager”] = new SPFieldLookupValue (id,Value).
I tried to do using above syntax but didn’t succeed.
Gettting Error ” The field your Adding is Readlonly Exception”

Your Help would be much appreciated,

Thanks,
Kiran
sai vara prasad - January 25, 2016

Hi Adi,
My query is that i will insert excel data to sahrepoint list through the code given by you but if i modified the few rows in same excel sheet and again if i try to insert or upload the rows , the rows should not get inserted as duplicate … they should be updated at their respective columns or rows.

This wil happen every day at certain time period.
i want to develop work flow for this .
Can you please explain how to do it.
Adi - January 29, 2016

I don’t think you require workflow here, you just require job that will do data CRUD’s. You might look into my other comments for the solution.

Regards,
Adi
sai vara prasad - January 25, 2016

I have a folder into which a new Excel Sheet is periodically copied.

Once every day I want to import the sheet data into a Sharepoint list. I know how to do this by hand, convert the data into list form and publish or merge to a sharepoint list. But what I need to do is do all this programatically.

I have a sharepoint list and want to merge new data from a normal Excel List into it. I want to be able to schedule this task to run as
soon as a new sheet is available in the target folder.

Can this be done using the Exel Object model through .Net/C#?
Adi - January 29, 2016

Hi Sai,

Yes, you can do it with object model through C#. But the code should be deployed as farm solution. Two ways to set
1>You can implement the code in custom SharePoint timer job and set the timer job to run frequently.
2>You can also do a console c# application with same implementation code. Make it as exe. Call this exe through windows job scheduler.
In both the cases you have to create Web object. Do not use SPContext as it is not relevant to timer jobs (referring line 78 in the above code)

Let me know if you want further help. Happy coding

Regards
Adi
Giri - January 28, 2016

Hi Adi, My requirement is to read data from excel sheet and update into the share point custom list. we need to create a mapping between excel sheet fields and sharepoint fields. Also developer machine does not have access to the sharepoint server. All we have is sharepoint subsite URL. Can i use this code or do we need to develop separate CSOM component. Could you please advise?

Regards,
Giri.
Adi - January 29, 2016

Hi Giri,
This code is with object model which needs to be deployed in the server. I am afraid, in your case this code will not fit.
Yes, you can go for client applications which will access/manage SharePoint sites using CSOM. Let me know if you want any other information, happy coding:)

Regards,
Adi
Sridhar - February 9, 2016

I am getting following error while upload excel file to sharepoint using c#. Can anyone help please. The type or namespace name ‘SPWeb’ could not be found (are you missing a using directive or an assembly reference?).
Thanks in advance
Adi - February 9, 2016

Hi Sridhar,
If you are getting error during compile time, then you just need to reference the path to Microsoft.SharePoint dll at “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.dll”
(14 if 2010, 15 if 2013)
If you are getting error during run time, then the user with which you are running the code is not having permission. You might need to run the code with elevated privileges.
Let me know if you want any further help

Regards,
Adi
Sridhar - February 9, 2016

Hi Adi,

I am not find out Web Server Extension folder following path in my system. So i’m not able to find out “Microsoft.SharePoint.dll”.
Adi - February 9, 2016

Hi Sridhar,
This is custom code(farm solution). You should be working with in your dev server where sharepoint is installed. If not, you can copy that dll from your server as any way you will be deploying in SharePoint server where dll will be always available. Let me know if this information helps

Regards,
Adi
Sridhar - February 10, 2016

Hi Adi,

I will check it and update to you.
sai vara prasad - February 10, 2016

Adi ,
My query is i have a rows in my view in sharepoint 2010 , i want to export only few rows(Selected Rows) to excelsheet . please let me know how to do it without caml query code and server side code in sharepoint.
Thanks,
Sai