PowerShell - Create/Update SharePoint 2010 data from excel file

PowerShell - Create/Update SharePoint 2010 data from excel file

#*=============================================================================

#* Purpose: To Create/Update list items with data extracted from excel file.

#*

#* Note: The Upload is done through OLEDB object, so there is no necessary for MS Office to be installed in the server.

#*=============================================================================

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$strFileName = Read-Host "Enter Excel Filename ::"

$strSheetName = Read-Host "Enter sheet name ::"

if ($strFileName -eq "") {

    throw "Please provide path to the Excel file"

    Exit

}

if (-not (Test-Path $strFileName)) {

    throw "File/Path does not exists - '$strFileName'"

    exit

}

if ($strSheetName -eq "") {

    throw "Sheet name cannot be null."

    Exit

}

##This fucntion is used to create new item..

Function fnCreateNewItem

{

param ([string] $webURL,[string] $listName,[string] $Title,[string] $Name,[string] $Address)

           

      #Get the SPWeb object and save it to a variable

      $web = Get-SPWeb $webURL

      #Get the SPList object to retrieve the "List"

      $list = $web.Lists[$listName]

     

      #Create a new item

      $newItem = $list.Items.Add()

      #Add properties to this list item

      $newItem["Title"] = $Title

      $newItem["Name"] =$Name

      $newItem["Address"] =$Address

      #Update the object so it gets saved to the list

      $newItem.Update()

}

##This fucntion is used to update existing item..

Function fnUpdateItem

{

param ([string] $webURL,[string] $listName,[string] $Title,[string] $Name,[string] $Address)

           

      #Get the SPWeb object and save it to a variable

      $web = Get-SPWeb $webURL

      #Get the SPList object to retrieve the "List"

      $list = $web.Lists[$listName]

     

      $spQuery = New-Object Microsoft.SharePoint.SPQuery

     

      #Query to filter data based on condition..

      $camlQuery = "<Where>

                              <Eq>

                                    <FieldRef Name='Title' />

                                    <Value Type='Text'>$Title</Value>

                              </Eq>

                        </Where>"

                       

      $spQuery.Query = $camlQuery  

      $spQuery.RowLimit =100 #Query Paging row limit..

      #Apply query and get all corresponding Item's...

      $spListItemCollection = $list.GetItems($spQuery)

      #Loop through every filtered items for data update...

      $spListItemCollection | ForEach-Object{

            $_["Name"] = $Name;

            $_["Address"] = $Address;

            $_.Update()

      }

}

$strSheetName = $strSheetName +'$'

$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"

$strDataSource = "Data Source = $strFileName"

$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")

$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn

$objConn.open()

$DataReader = $sqlCommand.ExecuteReader()

Write-Host "Processing...."

While($DataReader.read())

{    

      # Variables that we are going to use for list editing

      $webURL = http://<Site Url>      

      $listName = "<List Name>"

     

      #fnCreateNewItem $webURL $listName $DataReader[0].Tostring() $DataReader[1].Tostring() $DataReader[2].Tostring()

      fnUpdateItem $webURL $listName $DataReader[0].Tostring() $DataReader[1].Tostring() $DataReader[2].Tostring()

} 

Write-Host "Process Completed....!!!"

$dataReader.close()

$objConn.close()

Script Execution Steps:-
1) Pass PowerShell Script file name with entire path.
2) Pass Excel file name with entire path
3) Pass Excel Sheet name where the data exists.

Note: Web Url and List name has been hardcoded in above script, modify that to full fill your needs.