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 https://<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: https://msdn.microsoft.com/en-us/library/lists.lists.updatelistitems%28v=office.12%29.aspx

Salaudeen Rajack

Salaudeen Rajack - Information Technology Expert with Two-decades of hands-on experience, specializing in SharePoint, PowerShell, Microsoft 365, and related products. He has held various positions including SharePoint Architect, Administrator, Developer and consultant, has helped many organizations to implement and optimize SharePoint solutions. Known for his deep technical expertise, He's passionate about sharing the knowledge and insights to help others, through the real-world articles!

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

  • 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
  • How to update the lookup fields and people and group fields.

    Reply
    • 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
  • Site1.Lists.Lists list = new ListWebservice.Site1.Lists.Lists();

    can you please explain what this statement is for??

    Reply
    • 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

Your email address will not be published. Required fields are marked *