Forum Discussion

Kaddrik's avatar
Kaddrik
Copper Contributor
Feb 24, 2023

Import non existing CSV rows to SharePoint online list

Hello,
Hope you're doing good.
 
I'm very new to Powershell and try to import CSV rows into a SharePoint online list. I found several things on the net and combined them all together so what i'm trying to achieve almost works.
 

Problem is I'm stuck at checking if an item already exists in the list. At the moment it recreates all the items even if they already exist. It doesn't check if the items exists to only create the new ones 😞

So if someone could help me a little bit ...that would be great !

Here is my script :

#Parameters
$SiteUrl = <my site>
$ListName = "Apps"
$CSVPath = "MyFile.csv"

#Get content
$CSVData = Import-CsV -Path $CSVPath -Delimiter ";"

#Connection to site
Connect-PnPOnline $SiteUrl -UseWebLogin

# Iteration through CSV lines
ForEach ($Row in $CSVData)
{
    $Item = $ListName.Items | where {$_['Title'] -eq $Row.Title}

    if($Item -eq $Null){
    Write-Host "Adding Application $($Row.Title)"

    #Adds items
    Add-PnPListItem -List $ListName -Values @{"Title" = $($Row.Title);
                                              "Department"=$($Row.Department);
                                              "Project" = $($Row.Project);
                            
    };
   }
}

Thanks in advance for your time.

SharePoint
  • AndySvints's avatar
    AndySvints
    Mar 01, 2023

    Hello Kaddrik,

    One of the possible options would be something like this:

    1. Get SharePoint List Items
    2. Get CSV file
    3. Compare two collections and identify the diff, items that are present in CSV but are not present in SharePoint list
    4. Foreach diff item add it to the SharePoint

     

    #Parameters
$SiteUrl = "https://m365x45097644.sharepoint.com/sites/Contoso"
$ListName = "ProjectList"
$CSVPath = "MyFile.csv"

#Get content
$CSVData = Import-CsV -Path $CSVPath #-Delimiter ";"

#Connection to site
Connect-PnPOnline $SiteUrl -UseWebLogin

$ListItems=(Get-PnPListItem -List $ListName ).FieldValues | Select-Object @{l="Title";e={$_."Title"}} , @{l="Department";e={$_."Department"}}, @{l="Project";e={$_."Project"}}

$Diff=Compare-Object -ReferenceObject $ListItems -DifferenceObject $CSVData | Where-Object {$_.SideIndicator -eq "=>"} | Select-Object -ExpandProperty InputObject

ForEach ($Row in $Diff)
{
    Write-Host "Adding Application $($Row.Title)"
    #Adds items
    Add-PnPListItem -List $ListName -Values @{"Title" = $($Row.Title);
                                              "Department"=$($Row.Department);
                                              "Project" = $($Row.Project);
   }
}

    Hope that helps.

  • AndySvints's avatar
    AndySvints
    Steel Contributor
    Mar 01, 2023

    Hello Kaddrik,

    One of the possible options would be something like this:

    1. Get SharePoint List Items
    2. Get CSV file
    3. Compare two collections and identify the diff, items that are present in CSV but are not present in SharePoint list
    4. Foreach diff item add it to the SharePoint

     

    #Parameters
$SiteUrl = "https://m365x45097644.sharepoint.com/sites/Contoso"
$ListName = "ProjectList"
$CSVPath = "MyFile.csv"

#Get content
$CSVData = Import-CsV -Path $CSVPath #-Delimiter ";"

#Connection to site
Connect-PnPOnline $SiteUrl -UseWebLogin

$ListItems=(Get-PnPListItem -List $ListName ).FieldValues | Select-Object @{l="Title";e={$_."Title"}} , @{l="Department";e={$_."Department"}}, @{l="Project";e={$_."Project"}}

$Diff=Compare-Object -ReferenceObject $ListItems -DifferenceObject $CSVData | Where-Object {$_.SideIndicator -eq "=>"} | Select-Object -ExpandProperty InputObject

ForEach ($Row in $Diff)
{
    Write-Host "Adding Application $($Row.Title)"
    #Adds items
    Add-PnPListItem -List $ListName -Values @{"Title" = $($Row.Title);
                                              "Department"=$($Row.Department);
                                              "Project" = $($Row.Project);
   }
}

    Hope that helps.

    • Kaddrik's avatar
      Kaddrik
      Copper Contributor
      Mar 15, 2023
      Hi AndySvints,

      Thanks a lot for your reply. I was like desperate for someone to be able to help me on this! 😄

      I just did a quick test on a test list, cause in the meantime i went on with my project so i'll come back to this later, but it seems to be working !!! 🙂

      So thanks a lot for your time and ...i hope i can come back to you if i see there is something wrong when i 'll do it on my original list 😛

      Thanks a lot !!!

Resources