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.

PowerShell - Create/Update SharePoint 2010 data from excel file

PowerShell - Create/Update SharePoint 2010 data from excel file

#*=============================================================================

#* Purpose: To Create/Update list items with data extracted from excel file.

#*

#* Note: The Upload is done through OLEDB object, so there is no necessary for MS Office to be installed in the server.

#*=============================================================================

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

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

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

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

}

##This fucntion is used to create new item..

Function fnCreateNewItem

{

param ([string] $webURL,[string] $listName,[string] $Title,[string] $Name,[string] $Address)

           

      #Get the SPWeb object and save it to a variable

      $web = Get-SPWeb $webURL

      #Get the SPList object to retrieve the "List"

      $list = $web.Lists[$listName]

     

      #Create a new item

      $newItem = $list.Items.Add()

      #Add properties to this list item

      $newItem["Title"] = $Title

      $newItem["Name"] =$Name

      $newItem["Address"] =$Address

      #Update the object so it gets saved to the list

      $newItem.Update()

}

##This fucntion is used to update existing item..

Function fnUpdateItem

{

param ([string] $webURL,[string] $listName,[string] $Title,[string] $Name,[string] $Address)

           

      #Get the SPWeb object and save it to a variable

      $web = Get-SPWeb $webURL

      #Get the SPList object to retrieve the "List"

      $list = $web.Lists[$listName]

     

      $spQuery = New-Object Microsoft.SharePoint.SPQuery

     

      #Query to filter data based on condition..

      $camlQuery = "<Where>

                              <Eq>

                                    <FieldRef Name='Title' />

                                    <Value Type='Text'>$Title</Value>

                              </Eq>

                        </Where>"

                       

      $spQuery.Query = $camlQuery  

      $spQuery.RowLimit =100 #Query Paging row limit..

      #Apply query and get all corresponding Item's...

      $spListItemCollection = $list.GetItems($spQuery)

      #Loop through every filtered items for data update...

      $spListItemCollection | ForEach-Object{

            $_["Name"] = $Name;

            $_["Address"] = $Address;

            $_.Update()

      }

}

$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

      $webURL = http://<Site Url>      

      $listName = "<List Name>"

     

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

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

} 

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

$dataReader.close()

$objConn.close()

Script Execution Steps:-
1) Pass PowerShell Script file name with entire path.
2) Pass Excel file name with entire path
3) Pass Excel Sheet name where the data exists.

Note: Web Url and List name has been hardcoded in above script, modify that to full fill your needs.