Import items from a CSV file to a Sharepoint Online list

Copper Contributor

Hello,
I made a PowerShell script to import items from a CSV file to a Sharepoint Online list. Except that my code does not work because I have this error:  "Get-SPOUser : Le terme «Get-SPOUser» n'est pas reconnu comme nom d'applet de
commande, fonction, fichier de script ou programme exécutable. Vérifiez
l'orthographe du nom, ou si un chemin d'accès existe, vérifiez que le chemin
d'accès est correct et réessayez."

 

My "Manager" item is a People Picker.

Can you help me please :( ?

 

My code :

 

 

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"


##Variables for Processing
$SiteUrl = "https://abc/abc"
$ListName="Membres"
$ImportFile ="C:\Scripts\Equipe Romain.csv"
$UserName="abcd"
$Password ="1234"
 
#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
 
#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials
  
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)
 
#Get the Data from CSV and Add to SharePoint List
$data = Import-Csv $ImportFile
Foreach ($row in $data) {
    #add item to List
    $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
    $Item = $List.AddItem($ListItemInfo)

   $item["Title"] = $row."Nom Prenom" 
   #Set the People Picker Field value
   $item["Manager"] = Get-SPOUser -Identity $row.Manager -web "https://aaaaaaaaaaaaaaaaaaaaaaaaaaaaa/"

   $item["Lundi"] = $row.Lundi
   $item["Mardi"] = $row.Mardi
   $item["Mercredi"] = $row.Mercredi
   $item["Jeudi"] = $row.Jeudi
   $item["Vendredi"] = $row.Vendredi


    $Item.Update()
    $Context.ExecuteQuery() 
    
}

 

20 Replies

Looking at one of the screenshots you have provided I would suggest to tackle multiple potential issues as follows:

1) ensure proper encoding of you input
(not seen anywhere in this thread, but just to be on the save side keep it for later in mind).
What I definitely could not see in your screenshot is the property for the delimiter of your CSV data

 

#Get Import , convert to UTF
    $NewFile = $ImportFile.Replace(".csv","UTF.csv")
    Type $ImportFile -Encoding:String | Out-File $NewFile -Encoding UTF8
    Start-Sleep -Seconds 2

#Import the new created file
    $data = Import-Csv -path $NewFile -Delimiter "`t" # ";"
    start-sleep -Seconds 5
#delete the new created file again
    remove-item -path $NewFile

 


2) Within the Foreach put some additional brackets around the CSV column names:
  $item['Title'] = $($row.ColumnHeader)
Whereby if the header consists of multiple lines or white spaces you might want to define the column header beforehand:

variation 1:
(spacing)

 

$TitleHeading = "Nom Prenom"
[STRING]$MyTitle = $($row.$TitleHeading).trim()
$item['Title'] = $MyTitle

 

variation 2:
(whoever thought having multiple lines within the header is a good idea ;) )


$TitleHeading = @"
Nom Prenom
(obligatoire)

"@
[STRING]$MyTitle = $($row.$TitleHeading).trim()
$item['Title'] = $MyTitle


<edit 1>:
:cryingwithlaughter:, had a look at your screenshot to peek on your code just to notice after posting my reply that you have included it now within your initial posting.
</edit>