Friday, March 23, 2018

Update List Item with Excel Data Using Powershell



function UpdateListItem(){

    $inputfile = Get-FileName "c:\"
    if($inputfile -ne "")
    {
        $objExcel=New-Object -ComObject Excel.Application
        $objExcel.Visible=$false
        $WorkBook=$objExcel.Workbooks.Open($inputfile)
        $worksheet = $WorkBook.sheets.Item(1)
    
        ##########################
        $listName = "PnPList"

        $intRowMax = ($worksheet.UsedRange.Rows).count
        for($intRow = 2 ; $intRow -le $intRowMax ; $intRow++)
        {
            $ID = $worksheet.cells.item($intRow,1).value2
            $Title = $worksheet.cells.item($intRow,2).value2
            $Description = $worksheet.cells.item($intRow,3).value2

            $itemValue = @{"Title"=$Title;"Description"=$Description} #item value
            Set-PnPListItem -List $listName -Identity $ID -Values $itemValue            
        }  
        $WorkBook.close()
        $objexcel.quit()

        ##########################
    }
}

Function Get-FileName($initialDirectory)
{
    [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms") | Out-Null
    
    $OpenFileDialog = New-Object System.Windows.Forms.OpenFileDialog
    $OpenFileDialog.initialDirectory = $initialDirectory
    $OpenFileDialog.filter = "xlsx (*.xlsx)| *.xlsx"
    $OpenFileDialog.ShowDialog() | Out-Null
    $OpenFileDialog.filename
}

UpdateListItem # Call Function

No comments:

Post a Comment

Your feedback is always appreciated. I will try to reply to your queries as soon as time allows.Please don't spam,spam comments will be deleted upon reviews.