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