Import custom data from excel to sharepoint List in powershell

Question

I come from sharepoint 2019 and i try to import data from excel to a list It's working fine for date and text datas But i don't understand about unique choice, checkbox or People picker fields.

my loop to add rows is

Foreach ($Row in $ExcelData)
{
    Write-host -f Yellow "Adding Row $i of $($ExcelData.count)..." -NoNewline
    #Add to SharePoint Online List
    $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
    $ListItem = $List.AddItem($ListItemInfo)
     
    #Text
    #$ListItem["Number"] = $Row.'num'
     
    #date
    #$ListItem["Birthday"] = $Row.'Date de naissance '
    
    #Multiple Choice (checkbox)
    #$ListItem["Type"] = $Row.'Categorie'

    #Unique Choice
    #$ListItem["Ready"] = $Row.'Validation'

    #People Picker Field
    #$ListItem["Usr"] = $Row.'User'

    $ListItem.Update()
}

Someone could have a look on this please, and tell me what is wrong ? or maybe provide me another way ?

Regards,

Poule

Vlad Karasov · Accepted Answer · 2024-08-13 02:19:35Z

1

Personally, I prefer PnP.PowerShell. With Add-PnPListItem it is very simple:

$title = "Test Title" 
$numField = 12345 
$values = @{"Title" = $title; "Number" = $numField }
Add-PnPListItem -List $list -Values $values

answered Aug 13, 2024 at 2:19

Vlad Karasov

5502 silver badges13 bronze badges

Add a comment |

Karley Zhou-MSFT · Accepted Answer · 2024-08-13 01:27:20Z

To import data from Excel to a SharePoint list, you can use PowerShell. For unique choice, checkbox, or people picker fields, you need to use the internal name of the field in the PowerShell script. You can find the internal name of the field by going to the List Settings page and clicking on the field name. The internal name will be in the URL after "Field=".

Here is an example PowerShell script to import data from Excel to a SharePoint list:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

$siteUrl = "http://sitecollection/site"
$listName = "ListName"
$excelFilePath = "C:\ExcelFile.xlsx"

$spWeb = Get-SPWeb $siteUrl
$spList = $spWeb.Lists[$listName]

$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($excelFilePath)
$worksheet = $workbook.Sheets.Item(1)

for ($i=2; $i -le $worksheet.UsedRange.Rows.Count; $i++)
{
    $row = $worksheet.Rows.Item($i)

    $item = $spList.AddItem()

    $item["Title"] = $row.Range("A$i").Text
    $item["TextColumn"] = $row.Range("B$i").Text
    $item["DateColumn"] = $row.Range("C$i").Text
    $item["ChoiceColumn"] = $row.Range("D$i").Text
    $item["CheckboxColumn"] = $row.Range("E$i").Text
    $item["PersonColumn"] = $row.Range("F$i").Text

    $item.Update()
}

$workbook.Close()
$excel.Quit()

$spWeb.Dispose()

Replace the $siteUrl, $listName, and $excelFilePath variables with your own values. Replace the column names with the internal names of your columns.

Stack Exchange Network

Import custom data from excel to sharepoint List in powershell

2 Answers 2

Your Answer

Hot Network Questions

Import custom data from excel to sharepoint List in powershell

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions