Friday, 22 October 2010

Create SharePoint search keywords and best bets from a CSV file using PowerShell

If your SharePoint implementation comprises of different Search Centers across a number of site collections, managing a consistent set of keywords and best bets can be difficult through the browser UI. Each keyword can be time consuming to create, especially if it contains multiple best bets.

The script in this article allows you to use a CSV file to specify a set of keywords and best bets for a specified site collection. You can either append to any existing keywords already created in the site collection, or wipe out the current keywords and create new ones.

First, the CSV file should have the following header:


I have specified three best bets in the example above, but you are free to add as many BestBet, Description, and Url columns as you like. Although hard to read on this page, here is an example of a CSV file with some example keywords included:

PowerShell,Here are some links for the keyword 'PowerShell',Windows PowerShell;Scripting;PS;Scripts,Get-SPScripts,PowerShell scripts for SharePoint,,Hey Scripting Guy! Blog,Microsoft TechNet blog on scripting,,Effective Windows PowerShell,Free e-Book on learning PowerShell,!5A8D2641E0963A97!6930.entry
SharePoint,Here are some links for the keyword 'SharePoint',SharePoint Server;SP2010;Foundation;Collaboration,,SharePoint blog offering hints tips and solutions,,Microsoft SharePoint Team Blog,The official blog of the Microsoft SharePoint Product Group,,,,

This CSV file will add two keywords – “PowerShell” and “SharePoint” – along with synonyms and best bets for each one. Note that where there is more than one synonym for each keyword, add them as semi-colon delimited values within the CSV column.

Now the script. Before you can add the keywords, you must run the script below in PowerShell, which will set up the Import-SearchKeywords function. You will need to replace “Search Service Application” with the name of your search service application proxy from the Manage Service Applications page in Central Administration:

function Import-SearchKeywords ($SiteUrl, $CSVFilePath, [switch]$RemoveOldKeywords)
    $ssap = Get-SPEnterpriseSearchServiceApplicationProxy -Identity "Search Service Application"
    $keywords = New-Object Microsoft.Office.Server.Search.Administration.Keywords($ssap, $SiteUrl)
    $allKeywords = $keywords.AllKeywords
    $date = Get-Date
    #Remove all previous keywords from the site collection if chosen
    if($RemoveOldKeywords) {
        $keywordsArray = @()
        $allKeywords | ForEach-Object {
            $keywordsArray = $keywordsArray + $_.Term
        $keywordsArray | ForEach-Object {
            write-host "Deleting keyword:"$_
    #Import CSV file
    $csvData = Import-Csv $CSVFilePath | ForEach-Object {
        #Create keyword
        write-host "Importing keyword:"$_.Keyword
        $keyword = $allKeywords.Create($_.Keyword, $date.AddHours(-1))
        $keyword.Definition = $_.Definition
        #Create synonyms
        $synonymArray = @()
        $synonymArray = $_.Synonyms.Split(";")
        $synonymArray | ForEach-Object {
            write-host "Creating synonym"$_ "for keyword" $keyword.Term
            if (($_ -ne $null) -and ($_ -ne "")) { $keyword.Synonyms.Create($_) }
        #Create best bets
        $bestBetColumnNumber = 1
        $bestBetColumn = "BestBet" + $bestBetColumnNumber
        $descriptionColumn = "Description" + $bestBetColumnNumber
        $urlColumn = "Url" + $bestBetColumnNumber
        while ($_.$bestBetColumn) {
            write-host "Creating best bet"$_.$bestBetColumn "for keyword" $keyword.Term
            $keyword.BestBets.Create($_.$bestBetColumn, $_.$descriptionColumn, $_.$urlColumn)
            $bestBetColumnNumber = $bestBetColumnNumber + 1
            $bestBetColumn = "BestBet" + $bestBetColumnNumber
            $descriptionColumn = "Description" + $bestBetColumnNumber
            $urlColumn = "Url" + $bestBetColumnNumber
        #Update keyword with best bets

Once you have your CSV file ready and the script above has been run, you have one of two options for importing the keywords and best bets.

To append the keywords in your CSV with those already present in the site collection, type the following command:

Import-SearchKeywords –SiteUrl <SiteCollectionURL> –CSVFilePath <CSVFilePathAndFileName>

To delete all existing keywords from the site collection and create a new set from scratch with the contents of your CSV file, type the same command but with the RemoveOldKeywords switch:

Import-SearchKeywords –SiteUrl <SiteCollectionURL> –CSVFilePath <CSVFilePathAndFileName> -RemoveOldKeywords

For example, to create an entirely new set of keywords in the “http://portal” site collection using my example “SearchKeywords.csv” file above, you would type:

Import-SearchKeywords -SiteUrl http://portal -CSVFilePath C:\Install\SearchKeywords.csv –RemoveOldKeywords

The “Manage Keywords” site collection administration page will now look as follows:


And the following results will appear when I type one of my keywords in the search center:


If you are looking to import FAST Search keywords and user contexts to SharePoint 2010 from a CSV file, take a look at this script posted on the Microsoft TechNet Script Center at


  1. Nice post. Well, this gave me a Heads up on what i was looking for. But what i we have multiple BestBets, i was loooking for BestBets seperated by ";" , not as "BestBet1, Description1, Url1"

  2. Is there a way to retrieve all existing Keywords, BestBets , Synonyms and Definitions using PowerShell script from Sharepoint Enterprise Search?

  3. You may want to read the post again - "I have specified three best bets in the example above, but you are free to add as many BestBet, Description, and Url columns as you like". Using a semi-colon delimeter wouldn't be very intuitive and adds no real benefit as far as I can tell...unless I'm missing the point.

  4. "Is there a way to retrieve all existing Keywords, BestBets , Synonyms and Definitions using PowerShell script from Sharepoint Enterprise Search?" > You could enumerate through the site collections and send them to an XML or CSV file...but that is a whole different script than the one above...

  5. The semicolon delimiter doesn't work. It will allow you to do it, but they won't actually work until you go into through the front end, edit the keyword and click "okay".

    For the Best Bets to work without manual intervention they need to be added one at a time (as in the example above).

  6. Nice Post, When i was trying run the script, its asking like below
    cmdlet ForEach-Object at command pipeline position 2
    Supply values for the following parameters:

    Could you please correct me where i am doing wrong.


  7. Can you post a sample example of a CSV file for download?

  8. This is great! You just saved me so much time. Thanks!

  9. Thanks, this is terrific!

  10. Thank you! #codehelp

  11. Does this also work with SharePoint 2013 or are there changes that need
    to be make to the code?

  12. Thanks for this! Our Search service crashed and I had to recreate the search application. This has happened before and with 100s of best bets it was a real chore to restore them through the UI. This saved my sanity! Nice work.

  13. Game online a lot of people who love to help them relieve stress of the moment, such as TinyTanksandTinyPlanesorTiny Planes Beta is the fighting game with a pen or pencil, you have to destroy opponents before they kill you. Or Run 3 Unblocked and Hacked unblocked Gamesare skillful game controller for bears to dance to the goal without falling off a cliff. I wish you happy gaming!

  14. If you have a history of illness that is difficult to recover, maybe our next article will help you to recover

    Obat untuk kencing nanah
    Obat Kudis
    Obat denam tipes
    Obat nyeri panggul
    Obat tetanus

  15. También puede visitar una tienda de ladrillo y mortero de Michael Kors o su sitio web y comprar directamente un bolso Michael Kors desde allí. Usar un bolso de Michael Kors les permite a los demás reconocer que el habitante urbano educado toma la moda realmente con seriedad. Los bolsos de hombro son particularmente refinados y elegantes.

    {Bolsas Michael Kors Precios | Bolsos Michael Kors Outlet | Michael Kors Rebajas}

    En vacker konstnärlig skapelse av vävt läder, som ger ett skalskaligt utseende - liknar en snakeskin eller fiskhud, linjer utsidan av påsen. Läderens bältros är små läderringar. Det finns också gyllene accenter på väskan. Slutresultatet är svagt liknar kedjepost.

    {Michael Kors Rea | Michael Kors Väska Rea | Michael Kors Plånbok}