none
Import CSV file to SP List via Powershell

    Question

  • Hi there,

    I have a trouble writing a correct function for the script to achieve what I want.

    I export and import a CSV file on a regular basis and that way I keep 2 sharepoint lists (external and native) in sync.

    When I import the values, I want Powershell to:

    1. See if the item exists, if not – add a new item - THAT CURRENTLY WORKS
    2. If item exists, compare its columns and if there is a new value in CSV, update SP List with updated values just for the items that have changed – DO NOT KNOW HOW TO DO IT

    Editing just the items that have changed would help me keeping version history under control.

    See my script so far. How can I modify it accomplish point 2 above?

    $csvVariable= Import-CSV -path "\\fileshare\folder\export.csv"
    
    # Destination site collection
    $WebURL = "https://intranet.contoso.com" 
    
    # Destination list name
    $listName = "SP Native List" 
    
    #Get the SPWeb object and save it to a variable
    $webDestination = Get-SPWeb -identity $WebURL 
    
    #Get the SPList object to retrieve the list
    $list = $webDestination.Lists[$listName] 
    
    #Get all items in this list and save them to a variable 
    $items = $list.items 
    
          #loop through csv file
    foreach($row in $csvVariable)
    {
        $updated = 0
        #loop through SharePoint list
        foreach($item in $items)
        {
            if($item["EquipmentID"] -eq $row."EquipmentID") 
                { 
    	      $updated++
                } 
        }
    #add new item if an update wasn't made
            if($updated -eq 0)
                {
                  $newItem = $list.items.Add()
    
    $newItem["UniqueRef"] = $row."UniqueRef"
    $newItem["Safety"] = $row."Safety"
    $newItem["Comment"] = $row."Comment"
    $newItem["Serial"] = $row."Serial"
    $newItem["Vendor"] = $row."Vendor"
    $newItem["Active"] = $row."Active"
    $newItem["Model"] = $row."Model" 
    $newItem["Description"] = $row."Description"
    $newItem["EquipmentID"] = $row."EquipmentID"
    $newItem["Code"] = $row."Code"      
    $newItem.Update()
                } 
    }
    $webDestination.Dispose() 

    Thursday, April 23, 2015 1:06 PM

Answers

  • Hi Romeo na d thanks for your reply.

    Your solution would work if we do not want to have efficient versioning of the items in place.

    Also, it would easily clog up the Recycle Bins and someone would have to monitor that. 

    I have come up with my own solution and modified the script to:

    1. Add new items if the do not exist

    2. Modify just the items that have changed

    I haven't really taken care of deleted items but this shouldn't be a problem.

    Please have a look at me script here

    I don't like to propose my own stuff as answers but in this case this is what it is.

    • Marked as answer by Lukas_UK Wednesday, May 6, 2015 11:35 AM
    Wednesday, May 6, 2015 11:35 AM

All replies

  • Hi

    another approach

    if the item exist,

    delete it from the list

    and update the list using item's value from csv file


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.

    Wednesday, May 6, 2015 11:20 AM
  • Hi Romeo na d thanks for your reply.

    Your solution would work if we do not want to have efficient versioning of the items in place.

    Also, it would easily clog up the Recycle Bins and someone would have to monitor that. 

    I have come up with my own solution and modified the script to:

    1. Add new items if the do not exist

    2. Modify just the items that have changed

    I haven't really taken care of deleted items but this shouldn't be a problem.

    Please have a look at me script here

    I don't like to propose my own stuff as answers but in this case this is what it is.

    • Marked as answer by Lukas_UK Wednesday, May 6, 2015 11:35 AM
    Wednesday, May 6, 2015 11:35 AM