Friday, March 23, 2018

Create List Item with Excel Data Using Powershell


function AddListItem(){

    $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++)
        {
            $Title = $worksheet.cells.item($intRow,1).value2
            $Description = $worksheet.cells.item($intRow,2).value2

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

        ##########################
    }
}
# Below function for Dialog Box to select file.
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
}

AddListItem # 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.