Upload Excel data into SharePoint Custom List

Upload Excel data into SharePoint Custom List
6 votes, 4.67 avg. rating (93% 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.

AdiCodesTags
Export an Excel table to a SharePoint list
Export Excel Data into SharePoint List
Import Excel spread sheet to SharePoint list
Upload excel data into SharePoint list
Upload Spreadsheets into SharePoint Lists

June 14, 2012 В· Adi В· 35 Comments
Tags: , ,  В· Posted in: Sharepoint 2007, Sharepoint 2010

35 Responses

  1. Anuj Govil - August 7, 2012

    Great Post…!! It saves lot of time for me.. :) .
    Thanks alot.

  2. 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?

  3. 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

  4. D. Chaney - December 7, 2012

    Is it possible to use this code in a workflow which will run daily?

  5. Adi - December 7, 2012

    Hi Chaney,
    It is possible to use, there shouldn’t be a problem.

  6. D. Chaney - December 14, 2012

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

    Where does this TABLE_NAME come from?

  7. 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 :)

  8. 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.

  9. 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

  10. 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.

  11. 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

  12. 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

  13. 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

  14. 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();

  15. Adi - July 3, 2013

    cool, your issue got resolved :)

  16. 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.

  17. 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

  18. 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

  19. 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

  20. 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.

  21. 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

  22. 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

  23. Usha - July 27, 2013

    Hi Adi,

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

    Usha

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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.

  29. 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

  30. 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!

  31. 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

  32. 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 ?

  33. 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

  34. 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!

  35. 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.

Leave a Reply

What is 9 + 13 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)