PowerShell – Create Content Type by reading Site Columns from excel with values for Choice field

PowerShell – Create Content Type by reading Site Columns from excel with values for Choice field

$strFileName = Read-Host "Enter Excel Filename ::"

$strSheetName = Read-Host "Enter sheet name ::"

$url = Read-Host "Enter Site URL?"

if ($strFileName -eq "") {

    throw "Please provide path to the Excel file"

    Exit

}

if (-not (Test-Path $strFileName)) {

    throw "File/Path does not exists - '$strFileName'"

    exit

}

if ($strSheetName -eq "") {

    throw "Sheet name cannot be null."

    Exit

}

Write-Host "Processing.... " -ForegroundColor Yellow

#This fucntion is used to create Site Columns with any field type listed within it.

Function Set-CustomField

{

      param([string] $FieldName,[string] $FieldType,$web,$contentType,$choices)

     

      switch ($FieldType)

    {

        "Text" {$web.fields.add($FieldName, ([Type]“Microsoft.SharePoint.SPFieldType”)::Text, $false)}

        "Choice" {$web.fields.add($FieldName, ([Type]“Microsoft.SharePoint.SPFieldType”)::Choice, $false, $false, $choices)}

        default {$web.fields.add($FieldName, ([Type]“Microsoft.SharePoint.SPFieldType”)::Text, $false)}

    }

      $field = $web.fields.getfield($FieldName)

      $fieldLink = new-object Microsoft.SharePoint.SPFieldLink($field)

      #Add new fields/Site Columns to corresponding content type

      $contentType.fieldlinks.add($fieldLink)

      #Update content type with new fields..

      $contentType.Update()

}

$site = get-spsite $url

$web = $site.openweb()

$ctypeName = “NYLIM Records Management” #Content Type Name

$ctypeParent = $web.availablecontenttypes["Document"]

$contentType = new-object Microsoft.SharePoint.SPContentType($ctypeParent, $web.contenttypes, $ctypeName)

$contentType.Group = “NYLIM Content Type” # Content Type Group Name

$contentType.Description = “Used to capture metadata for NYLIM documents.”

#Add new content type to corresponding web.

$web.contenttypes.add($contentType)

$strSheetName = $strSheetName +'$'

$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"

$strDataSource = "Data Source = $strFileName"

$strExtend = "Extended Properties=Excel 8.0"

$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")

$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)

$sqlCommand.Connection = $objConn

$objConn.open()

$DataReader = $sqlCommand.ExecuteReader()

Write-Host "Processing...."

While($DataReader.read())

{    

      # Variables that we are going to use for list editing            

      #fnUpdateItem $webURL $listName $DataReader[0].Tostring() $DataReader[1].Tostring() $DataReader[2].Tostring()

      $ColumnName=$DataReader[0].Tostring()

      $FieldType=$DataReader[1].Tostring()

      if ($FieldType -eq "Text")

      {

            $choices=$null

      }

      elseif ($FieldType -eq "Choice")

      {

            $choices = New-Object System.Collections.Specialized.StringCollection

            $choicesArray=$DataReader[2].Tostring() -split ","

            Write-Host $choicesArray.Count           

            foreach ($choice in $choicesArray)

            {

                  if($choice.Trim() -ne $null )

                  {

                        $choices.Add($choice.Trim())

                  }

            }

      }    

      Set-CustomField $ColumnName $FieldType $web $contentType $choices #Creating Site column with choice field type...

      $choices=$null

} 

Write-Host "Process Completed...!!!" -ForegroundColor Yellow

#Dispose objects

$web.Dispose()

$site.Dispose()

Excel Prerequisite:-

1)  Excel should have column headers as “Column Name”,”Input Type”,”Options”

2)  In “Input Typr” columns we will have list contains values as “Text” and “Choice”.

3)  In “Options” column enter comma separated values for choice fields and leave it as blank for “Text” fields.

Script Execution:-

1)      Pass  Excel file name with path

2)      Enter worksheet name, where data exists

3)      Enter site url where Content Type needs to be created.