Posted on 2014-08-12
Last Modified: 2014-09-03

I have following script:
#Retrieving the Web Site with List
$MyWeb = Get-SPWeb "http://MySite"

#Get the List name
$MyList = $MyWeb.Lists["MyList"]

#Get The View
#Change the name of the view
$MyView = $MyList.Views["My View"]

#Get the Items from the view
$MyItems = $MyList.GetItems($MyView)

#Get Specific field items and export it to a csv file
$MyItems | %{ select-object -input $_ -prop @{Name='ID';expression={$_.ID;}}, @{Name='Content Type';expression={$_["x. Type"];}}, 
@{Name='Item Name';expression={$_["Item Name"];}}, @{Name='Categories';expression={$_["Categories"];}}, 
@{Name='Item Activity';expression={$_["Item Activity"];}}, 
@{Name='attending';expression={$_["attending"];}}; } | Export-Csv -Path C:\MyExports\export.csv

Open in new window

Now, the categories field is a lookup field how do use substring after function for the look up field so that I get the text after "#" character?
Question by:Techsavy
    LVL 38

    Accepted Solution

    I'm not sure if I understand exactly what you're looking for, however try this and see if it fits.  BTW, it's simpler to just pipe to Select-Object, rather than using it within a ForEach so I modified the code to do that.
    #Get Specific field items and export it to a csv file
    $MyItems | select-object -prop @{Name='ID';expression={$_.ID}},
                                    @{Name='Content Type';expression={$_["x. Type"]}},
                                    @{Name='Item Name';expression={$_["Item Name"]}},
                                    @{Name='Categories';expression={ ($_["Categories"]).substring(($_["Categories"]).IndexOf("#")+1) }},
                                    @{Name='Item Activity';expression={$_["Item Activity"]}},
                                    @{Name='attending';expression={$_["attending"]}} |
     Export-Csv -Path C:\MyExports\export.csv

    Open in new window


    Author Comment

    Hi footech,

    Thank you for the solution. What function I use to get anything before ';#' to get the ID of the LookupList Item? SharePoint Stores lookup field values as 1;#LookupValue so, I need to extract the string before ;# it could be any number.
    LVL 38

    Expert Comment

    I don't understand what you're asking.  I don't have a environment where I can test this out so you'd need to show me exactly what is being returned now.  Let me know if the code I provided is working for you.  Is this a separate question than your first one, or do you just need a modification of what I provided?

    Author Comment

    Hi footech.

    The solution that you gave me worked for the case where I wanted to return everything after '#'. But now I need to find a way to extract everything that appears before ';#' in a string that is always formatted 12;#somestring. Please note that the number before ;# could be anything from 1 to 20000
    LVL 38

    Expert Comment

    So the first expression was
    To return what's before ";#", you could have

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
    I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now