Add new Column to List in SharePoint Online using Power Shell script

cls
#Specify tenant admin and site URL
$User = "sravankumar1107@vayugundla.onmicrosoft.com"
$SiteURL = "https://vayugundla.sharepoint.com/"

#Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
Add-Type -Path "D:\SharePointDLL\Microsoft.SharePoint.Client.dll"
Add-Type -Path "D:\SharePointDLL\Microsoft.SharePoint.Client.Runtime.dll"
$Password = Read-Host -Prompt "Please enter your password" -AsSecureString
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)

#Bind to site collection
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
$Context.Credentials = $Creds
#Retrieve lists
$Lists = $Context.Web.Lists
$Context.Load($Lists)
$Context.ExecuteQuery()
write-host "Completed the loading of lists"
foreach ($list in $Lists) {
if ($list.BaseType -eq "DocumentLibrary") {
write-host "Currently adding the column to" $list.Title
#Adding new column to the Library
$List.Fields.AddFieldAsXml("<Field Type='Text' Required='false' Name='Quotes' MaxLength='255' DisplayName='Quotes'/>",$true,[Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldToDefaultView)
$List.Update()
$Context.ExecuteQuery()
#update an item to the list
$listitems = $List.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
$context.load($listitems)
$Context.ExecuteQuery()
foreach($item in $listitems)
{
$item["Quotes"] = "Test"
$item.Update()
$Context.ExecuteQuery()
write-host "Updated the Quotes Column"
}
}
}
write-host "Added a new column and updated the coulumn to the all libraries"

Disable Search for the Document Library using Powershell

Using below script we can disable the search for the Document library/List.

cls
Add-PsSnapin Microsoft.SharePoint.PowerShell

$site = Get-SPSite "<<Site URL>>"
$web= $site.openweb()  
        foreach ($list in $web.lists['Projectinfolist'])
        {                
                    $list.NoCrawl = $true
                    $list.Update()
        }  
        $web.Dispose()
    $site.Dispose()

Move Documents from one library to another library using PowerShell

cls
Add-PsSnapin Microsoft.SharePoint.PowerShell
Function global:Get-SPWeb($url)
{
  $site= New-Object Microsoft.SharePoint.SPSite($url)
        if($site -ne $null)
            {
               $web=$site.OpenWeb();    
            }
    return $web
}

#Get the web and List
$Web = Get-SPWeb "<<Site URL>>"
$SourceList = $web.Lists["Site Collection Documents"]
$TargetList = $Web.Lists["SiteCollectionDocuments-Copy"]

#Get all Files Created in 2009
 $Query = '<Where><And><Geq><FieldRef Name="Created" /><Value IncludeTimeValue="TRUE" Type="DateTime">2015-01-01T00:00:00Z</Value></Geq><Leq><FieldRef Name="Created" /><Value IncludeTimeValue="TRUE" Type="DateTime">2015-12-31T23:59:59Z</Value></Leq></And></Where>'
 $SPQuery = new-object Microsoft.SharePoint.SPQuery
 #$SPQuery.ViewAttributes = "Scope='Recursive'" #To include Sub-folders in the library
 $SPQuery.Query = $Query
 $SourceFilesCollection =$SourceList.GetItems($SPQuery)

Write-host "Total number of files found: "$SourceFilesCollection.count

#Move each file to the destination folder
foreach($item in $SourceFilesCollection)
{
  #Get the Source File
  $file = $Web.GetFile($item.File.URL)

  #Get the Month value from the File crated date
  $MonthValue = $item.File.TimeCreated.ToString('MMMM')
 
  # Try to Get the Sub-Folder in the Library!
  $TargetFolder = $TargetList.ParentWeb.GetFolder($TargetList.RootFolder.Url + "/" +$MonthValue);
 
  #If the folder doesn't exists, Create!
  if ($TargetFolder.Exists -eq $false)
   {
     $TargetFolder = $TargetList.Folders.Add("", [Microsoft.SharePoint.SPFileSystemObjectType]::Folder, $MonthValue)
     $TargetFolder.Update()
   }

   #Move the File
   $file.MoveTo($TargetFolder.Url + "/" + $File.name)
 
}

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

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

So the idea is: Lets 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);
          }
       }
   }

Upload the Excel Data into the SharePoint List

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 =newOleDbConnection(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 rowin dt.Rows)

                {

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

                    i++;

                }

            }

            return excelSheets;

        }

        private void InsertIntoList(DataTable listTable,stringcontactListName)

        {

            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;

                }

            }

        }

    }

}

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