Read From Excel & Import to SharePoint List – Using Web Services

Another code snippet using SharePoint-Web Services. The requirement is: End-User wants to read data from Microsoft Excel (it will be placed at c:\contracts.xls) and append it to SharePoint List (List Name: Contracts) on-demand from his desktop.

So the idea is: let’s build a console application, add a web service reference to SharePoint List web service http://<Site>/_vti_bin/Lists.asmx, and give it to the end-user. Let him run the console application from his desktop on-demand.

using System;
using System.Collections;
using System.Data;
using System.Xml;
using System.IO;
using System.Data.OleDb;
using System.Data.Common;

namespace ReadFromExcelImportToSharePoint
{
    class Program
    {
        static void Main(string[] args)
        {
            DateTime dt;
            string dt1;
   //initialize Web Service Reference
   Site1.Lists.Lists list = new ListWebservice.Site1.Lists.Lists();
   list.Credentials = System.Net.CredentialCache.DefaultCredentials;

            XmlNode contacts = list.GetList("Contracts");  

   XmlDocument doc = new XmlDocument();

   string listName = contacts.Attributes["ID"].Value;

            string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\contracts.xls;Extended Properties=""Excel 8.0;HDR=YES;""";
            DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
            using (DbConnection connection = factory.CreateConnection())
            {
                connection.ConnectionString = connectionString;
                using (DbCommand command = connection.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM [Sheet1$]";
                    connection.Open();
                    using (DbDataReader dr = command.ExecuteReader())
                    {
                        int i = 0;
                        while (dr.Read())
                        {
                            lblRecordNo.Text = i.ToString();

                            XmlElement elBatch = doc.CreateElement("Batch");
                            elBatch.SetAttribute("OnError", "Continue");
                            elBatch.SetAttribute("ListVersion", "1");

                            XmlElement el1 = doc.CreateElement("Method");
                            el1.SetAttribute("ID", "1");
                            el1.SetAttribute("Cmd", "New");

                            XmlElement field1 = doc.CreateElement("Field");
                            field1.SetAttribute("Name", "ID");
                            field1.InnerText = "New";

                            //From Here List Fields starts.
                            XmlElement field2 = doc.CreateElement("Field");
                            field2.SetAttribute("Name", "Title");
                            field2.InnerText = dr["i-Many number"].ToString();

                            XmlElement field3 = doc.CreateElement("Field");
                            field3.SetAttribute("Name", "Executing_x0020_Site");
                            field3.InnerText = dr["Executing Site"].ToString();

                            XmlElement field4 = doc.CreateElement("Field");
                            field4.SetAttribute("Name", "SAP_x002f_Legacy_x0020_Pricing_x");
                            field4.InnerText = dr["SAP/Legacy Pricing System"].ToString();

                            XmlElement field5 = doc.CreateElement("Field");
                            field5.SetAttribute("Name", "Customer_x0020_Name");
                            field5.InnerText = dr["Customer Name"].ToString();

                            XmlElement field6 = doc.CreateElement("Field");
                            field6.SetAttribute("Name", "Type_x0020_of_x0020_Contract");
                            field6.InnerText = dr["Type of Contract"].ToString();

                            XmlElement field7 = doc.CreateElement("Field");
                            field7.SetAttribute("Name", "Contract_x0020_Description");
                            field7.InnerText = dr["Contract Description"].ToString();

                            XmlElement field8 = doc.CreateElement("Field");
                            field8.SetAttribute("Name", "Effective_x0020_Date");
                            dt = Convert.ToDateTime(dr["Effective Date"].ToString()) ;
                            dt1=dt.ToString("u");
                            field8.InnerText = dt1;
                        
                            XmlElement field9 = doc.CreateElement("Field");
                            field9.SetAttribute("Name", "SBU");
                            field9.InnerText = dr["SBU"].ToString();

                            XmlElement field10 = doc.CreateElement("Field");
                            field10.SetAttribute("Name", "Customer_x0020_Type");
                            field10.InnerText = dr["Customer Type"].ToString();

                            elBatch.AppendChild(el1);

                            el1.AppendChild(field1);
                            el1.AppendChild(field2);
                            el1.AppendChild(field3);
                            el1.AppendChild(field4);
                            el1.AppendChild(field5);
                            el1.AppendChild(field6);
                            el1.AppendChild(field7);
                            el1.AppendChild(field8);
                            el1.AppendChild(field9);
                            el1.AppendChild(field10);
                            XmlNode rNode = list.UpdateListItems(listName, elBatch);

                            // 0x00000000 returned means that the list item was inserted correctly...
                           //   Console.WriteLine(rNode.InnerText);

                            i = i + 1;
                        }
                    }
                }
            }
       Console.WriteLine("No.of Records Imported:" +i);
          }
       } 
   }

List web service reference: http://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems%28v=office.12%29.aspx

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

5 thoughts on “Read From Excel & Import to SharePoint List – Using Web Services

  • October 11, 2017 at 6:55 PM

    I am using Visual studio 2015, I was not able to add Web reference. Did some research on it on some forums and tried several approaches but couldn’t possibly find a solution. Let me know if there is an alternate option to replace web reference and code something inline to connect to the sharepoint url.

    Reply
  • December 16, 2013 at 11:50 AM

    How to update the lookup fields and people and group fields.

    Reply
    • March 13, 2014 at 12:18 PM

      Lookup fields need to use the format of – number;#LookupValue

      Where the number is the index of the lookup item. So, You got to get the index using GetListItems or some other method first!

      Reply
  • April 9, 2013 at 11:14 AM

    Site1.Lists.Lists list = new ListWebservice.Site1.Lists.Lists();

    can you please explain what this statement is for??

    Reply
    • April 9, 2013 at 12:04 PM

      That is to Initiate SharePoint List Web Service! Just right click the Project >> Add Web Reference >> Specify SharePoint List Web service URL >> Give name as: Site1.Lists

      Reply

Leave a Reply