none
Shell Script for loading data into a single value lookup column SharePoint 2010 RRS feed

  • Question

  • Hello,

    I'm loading data from a .csv column into a single value lookup column in a SharePoint list.  I'm doing this through a shell script and am having a small issue with getting the data loaded.  Here is what the shell script looks like:

    Add-PsSnapin "Microsoft.SharePoint.PowerShell" 
    
    start-SPAssignment -global
    
    $web = Get-SPWeb http://dev9/sites/CMBlankSiteFour/
    #Loading data into CM Image Association list
    $cnt = 0
    $documentURL = 'C:\Users\shaun.cline\Downloads\CMListData\CMImageAssociationData.csv'
    $cmCategories = $web.lists["CM Image Association"]
    "'nLoading data in the CM Image Association list.  One Moment Please."
    
    foreach($i in Import-CSV $documentURL )
    {
    	$new = $cmCategories.Items.Add()
    	$new["Title"] = $i.Title
    	$imageLookup = new-object Microsoft.Sharepoint.SPFieldLookupValue("Image")
    	$container = new-object Microsoft.Sharepoint.SPFieldChoiceValue($null)
    	$container.Add($imageLookup)
    	$new["Image"] = $container
    	$new.Update()
    	$cnt++
    }
    
    "Added " + $cnt.ToString() + " records to CM Image Association list."
    start-sleep -seconds 2
    
    stop-SPAssignment -global

    I sort of took a guess at what the code would look like since I was following an example of loading multiple values into a multi value lookup column.    Could someone lend some assistance. Thanks in advance, Shaun
    Monday, July 2, 2012 7:08 PM

Answers

  • There are a couple of things that complicate this scenario:

    1) using an image library for a lookup can be tricky because the file name is not an option when setting up the lookup field.  Title and ID are the two most obvious options to pick but they are not great either (ID doesn't provide any context and Title is not required and blank by default meaning there won't be any options in your dropdown).

    2) Once you decide what field will be selected in your lookup, the value that needs to be set will be in the format [id];#[fieldvalue]

    Because your csv has the file name in it, I would use that to open the images as SPFile objects and then use their Item property to get the item's ID.  The script required would look something like this:

    $web = Get-SPWeb http://localhost
    $list = $web.Lists["Picture Lookup Test"]
    
    # Load the file (assumes picture library is called "Pictures")
    $pic = $web.GetFile("pictures/koala.jpg")
    
    $newItem = $list.Items.Add()
    $newItem["Title"] = "Test 2"
    $newItem["Image"] = ($pic.Item["ID"].ToString() + ";#" + $pic.Item["Title"])
    
    $newItem.Update()

    You'll need to update the values to match your environment but hope this helps!

    References:

    Tuesday, July 3, 2012 3:24 PM

All replies

  • Can you tell me more about the structure of your lists?  It sounds like your CSV has a collection of "Categories" and for each Category you have a field that is a lookup to an image list perhaps?

    Monday, July 2, 2012 7:32 PM
  • Hi

    I may be tired, but as far as I can see, the Microsoft.SharePoint assembly doesn't have a class named SPFieldChoiceValue .


    Kind Regards Bjoern
    Blog

    Monday, July 2, 2012 9:10 PM
    Moderator
  • Hi Raymond,

    Sure thing.  The list "CM Categories" contains a single value lookup column called "Image" and a "Title" column.  You are correct, this field lookups up and image in an image list.  The value being loaded into the "Image" lookup column is in a .csv file where there is a column named "Image".  The .csv would look like this:

    It's in Excel format.  So in cell A1 would be Title and cell B1 would be Image

    Title              Image

    Notes            notepa.png

    Documents    paperclip32.png

     Hope this helps, thanks for the reply Raymond.  Shaun

    Tuesday, July 3, 2012 2:17 PM
  • There are a couple of things that complicate this scenario:

    1) using an image library for a lookup can be tricky because the file name is not an option when setting up the lookup field.  Title and ID are the two most obvious options to pick but they are not great either (ID doesn't provide any context and Title is not required and blank by default meaning there won't be any options in your dropdown).

    2) Once you decide what field will be selected in your lookup, the value that needs to be set will be in the format [id];#[fieldvalue]

    Because your csv has the file name in it, I would use that to open the images as SPFile objects and then use their Item property to get the item's ID.  The script required would look something like this:

    $web = Get-SPWeb http://localhost
    $list = $web.Lists["Picture Lookup Test"]
    
    # Load the file (assumes picture library is called "Pictures")
    $pic = $web.GetFile("pictures/koala.jpg")
    
    $newItem = $list.Items.Add()
    $newItem["Title"] = "Test 2"
    $newItem["Image"] = ($pic.Item["ID"].ToString() + ";#" + $pic.Item["Title"])
    
    $newItem.Update()

    You'll need to update the values to match your environment but hope this helps!

    References:

    Tuesday, July 3, 2012 3:24 PM
  • Sorry for the delay in the response.  Thanks for this code snippet.  I'll be giving this a try and I'll get back to you on how I do.  It'll be several days since I was just given a few higher priority tasks.  Thanks for the reply.  Shaun
    Thursday, July 5, 2012 3:29 PM