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:

Keyword,Definition,Synonyms,BestBet1,Description1,Url1,BestBet2,Description2,Url2,BestBet3,Description3,Url3

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:

Keyword,Definition,Synonyms,BestBet1,Description1,Url1,BestBet2,Description2,Url2,BestBet3,Description3,Url3
PowerShell,Here are some links for the keyword 'PowerShell',Windows PowerShell;Scripting;PS;Scripts,Get-SPScripts,PowerShell scripts for SharePoint,http://get-spscripts.com,Hey Scripting Guy! Blog,Microsoft TechNet blog on scripting,http://blogs.technet.com/b/heyscriptingguy/default.aspx,Effective Windows PowerShell,Free e-Book on learning PowerShell,http://keithhill.spaces.live.com/blog/cns!5A8D2641E0963A97!6930.entry
SharePoint,Here are some links for the keyword 'SharePoint',SharePoint Server;SP2010;Foundation;Collaboration,PACSharePoint.com,SharePoint blog offering hints tips and solutions,http://pacsharepoint.com,Microsoft SharePoint Team Blog,The official blog of the Microsoft SharePoint Product Group,http://blogs.msdn.com/b/sharepoint/,,,

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:"$_
            $allKeywords[$_].Delete()
        }
    }
   
    #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
        $keyword.Update()
    }
}

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 http://gallery.technet.microsoft.com/ScriptCenter/en-us/9ad93d90-9ca7-4515-b81e-441eda0390c3.