In this article, we will see how to use PowerShell to create a SharePoint list and it's columns using csv file as input.

This PowerShell script reads the csv file to get all the fields dynamically. It will create the List first and then adds the list columns. I have used different column types as shown below.


PowerShell to Create SharePoint List and Fields using CSV file


Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

$SiteURL = "https://myclassbook.sharepoint.com/sites/capex"
$ListName = "ProjectDetails"
$csvPath = "C:\Users\mayur\OneDrive\Documents\Apurva\PS\Column Creation\book1.csv"
$ImportedColumnsData = Import-Csv -Path $csvPath
 
Try {
    $Cred = Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
 
    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Credentials
    $ListCreationInfo = New-Object Microsoft.SharePoint.Client.ListCreationInformation
    $ListCreationInfo.Title = $ListName
    $ListCreationInfo.TemplateType = 100
    $List = $Ctx.Web.Lists.Add($ListCreationInfo)
    $List.Description = "This list contains project details"
    $List.Update()
    $Ctx.ExecuteQuery()

    #Get the List
    $List = $Ctx.Web.Lists.GetByTitle($ListName)
    $Ctx.Load($List)
    $Ctx.ExecuteQuery()
 
    #Check if the column exists in list already
    $Fields = $List.Fields
    $Ctx.Load($Fields)
    $Ctx.executeQuery()
    for ($i = 0; $i -lt $ImportedColumnsData.length; $i++) {
        Switch ($ImportedColumnsData[$i].Type) {
            'Single Line of text' {
                $FieldSchema = "<Field Type='Text' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "'/>"
            }
            'Number' {
                $FieldSchema = "<Field Type='Number' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "'/>"
            }
            'Multiline of text' {
                $FieldSchema = "<Field Type='Note' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "'/>"
            }
            'Person and group' {
                $FieldSchema = "<Field Type='User' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "'/>"
            }
            'Choice' {
                $ChoiceOptions = [string]::Empty
                $Choices = $ImportedColumnsData[$i].Other.Split(",")
                foreach ($Choice in $Choices) {
                    $ChoiceOptions = $ChoiceOptions + "<CHOICE>$Choice</CHOICE>"
                }
                $FieldSchema = "<Field Type='Choice' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "'><Default>" + $ImportedColumnsData[$i].Default + "</Default> <CHOICES>$ChoiceOptions</CHOICES></Field>"
            }
            'Hyperlink or Picture' {
                $FieldSchema = "<Field Type='URL' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "' Format='"+$ImportedColumnsData[$i].Other+"'/>"
            }            
            'Date and Time' {
                $FieldSchema = "<Field Type='DateTime' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "' />"
            }
            'Yes or No' {
                $FieldSchema = "<Field Type='Boolean' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "'><Default>" + $ImportedColumnsData[$i].Default + "</Default> </Field>"
            }
            'Currency' {
                $FieldSchema = "<Field Type='Currency' ID='{$FieldID}' DisplayName='" + $ImportedColumnsData[$i].DisplayName + "' Name='" + $ImportedColumnsData[$i].InternalName + "' />"
            }
        }    
        $FieldID = New-Guid     
            
        $NewField = $Fields | Where-Object { ($_.Internalname -eq $ImportedColumnsData[$i].InternalName) -or ($_.Title -eq $ImportedColumnsData[$i].DisplayName) }
        if ($null -ne $NewField) {
            Write-host "Column $Name already exists in the List!" -f Yellow
        }
        else
        {
            $NewField = $List.Fields.AddFieldAsXml($FieldSchema, $True, [Microsoft.SharePoint.Client.AddFieldOptions]::AddFieldInternalNameHint)
            $Ctx.ExecuteQuery()   
            Write-host "New Column Added to the List Successfully!" -ForegroundColor Green 
        }
    }
}
Catch {
    write-host -f Red "Error Adding Column to List!" $_.Exception.Message
}
Downloads:
1) Microsoft.SharePoint.Client.Runtime.dll
2) Microsoft.SharePoint.Client.dll

YouTube Video: