SharePoint Online：使用 PowerShell 将 CSV 文件导入 SharePoint 列表

场景：我的要求是每天将数据从本地驱动器的 CSV 文件导入到 SharePoint Online 列表！

您是否有需要导入到 SharePoint Online 列表中的 CSV 文件？嗯，PowerShell 让一切变得简单！在这篇文章中，我将向您展示如何使用 PowerShell 将 CSV 文件导入 SharePoint Online 列表。您将能够将任何类型的数据导入到列表中，包括文本、数字和日期。让我们开始吧！

用于从 CSV 文件导入 SharePoint 列表项的 PowerShell 脚本：

虽然可以使用“快速编辑”视图将数据从 CSV 文件复制粘贴到 SharePoint Online 列表，但 PowerShell 方法对于自动化来说是必要的。以下是用于将 CSV 导入列表的 SharePoint Online PowerShell。

#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
 
##Variables for Processing
$SiteUrl = "https://crescent.sharepoint.com/sites/POC/"
$ListName="Contacts"
$ImportFile ="c:\Scripts\EmpData.csv"
$UserName="Salaudeen@crescent.com"
$Password ="Password goes here"

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl) 
$Context.Credentials = $credentials
 
#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)

#Get the Data from CSV and Add to SharePoint List
$data = Import-Csv $ImportFile
Foreach ($row in $data) {
    #add item to List
    $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
    $Item = $List.AddItem($ListItemInfo)
    $Item["FirstName"] = $row.FirstName
    $Item["LastName"] = $row.LastName
    $Item["Department"] = $row.Department
    $Item["Mobile"] = $row.Mobile
    $Item.Update()
    $Context.ExecuteQuery() 
   
}
Write-host "CSV data Imported to SharePoint List Successfully!"

这将使用 PowerShell 将 CSV 导入到 SharePoint Online 列表。

PnP PowerShell 将 CSV 文件数据导入 SharePoint Online 列表

让我们借助 Import-CSV 和 Add-PnPListItem cmdlet，使用 PnP PowerShell 将 CSV 导入到 SharePoint Online 列表：

#Parameters
$SiteUrl = "https://crescent.sharepoint.com/sites/marketing"
$ListName = "Contacts"
$CSVPath = "C:\temp\Contacts.csv"

#Get he CSV file contents
$CSVData = Import-CSV -Path $CSVPath

#Connect to site
Connect-PnPOnline $SiteUrl -Interactive

#Iterate through each Row in the CSV and import data to SharePoint Online List
ForEach ($Row in $CSVData)
{
    Write-Host "Adding Contact $($Row.FirstName)"
    
    #Add List Items - Map with Internal Names of the Fields!
    Add-PnPListItem -List $ListName -Values @{"FirstName" = $($Row.FirstName); 
                            "LastName" = $($Row.LastName);
                            "Department"=$($Row.Department);
                            "Mobile" = $($Row.Mobile);
    };
}

这是我的 CSV 文件，您可以下载：

以及列表中导入的数据：

此 PowerShell 将给定 CSV 文件中的新项目添加到 SharePoint Online 列表。如果要从 CSV 文件更新 SharePoint Online 列表，请使用：PowerShell 从 CSV 更新 SharePoint Online 列表

使用 PnP PowerShell 将列表项从 CSV 文件批量添加到 SharePoint Online

上述脚本适用于简单的字段类型，例如单行文本、选择等。如果您有复杂的字段类型，例如“查找”、“人员选择器”等，该怎么办？以下是我的“项目”列表，其中包含 SharePoint Online 中几乎所有可用的字段类型：

列表结构：

以下是更新后的 PowerShell 脚本，用于从 CSV 批量导入列表项。

#Config Variables
$SiteURL = "https://crescent.sharepoint.com/sites/projects"
$ListName = "Projects"
$CSVFilePath = "C:\Temp\ProjectData.csv"

#Function to get Lookup ID from Lookup Value
Function Get-LookupID($ListName, $LookupFieldName, $LookupValue)
{
    #Get Parent Lookup List and Field from Child Lookup Field's Schema XML
    $LookupField =  Get-PnPField -List $ListName -Identity $LookupFieldName
    [Xml]$Schema = $LookupField.SchemaXml
    $ParentListID = $Schema.Field.Attributes["List"].'#text'
    $ParentField  = $Schema.field.Attributes["ShowField"].'#text'
    $ParentLookupItem  = Get-PnPListItem -List $ParentListID -Fields $ParentField | Where {$_[$ParentField] -eq $LookupValue} | Select -First 1 
    If($ParentLookupItem -ne $Null)  { Return $ParentLookupItem["ID"] }  Else  { Return $Null }
}

Try {
    #Connect to the Site
    Connect-PnPOnline -URL $SiteURL -Interactive

    #Get the data from CSV file
    $CSVData = Import-CSV $CSVFilePath

    #Get the List to Add Items
    $List = Get-PnPList -Identity $ListName
    
    #Get fields to Update from the List - Skip Read only, hidden fields, content type and attachments
    $ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) -and ($_.InternalName -ne  "ContentType") -and ($_.InternalName -ne  "Attachments") }
     
    #Loop through each Row in the CSV file and update the matching list item ID
    ForEach($Row in $CSVData)
    {
        #Frame the List Item to update
        $ItemValue = @{}            
        $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name
        #Map each field from CSV to target list
        Foreach($CSVField in $CSVFields)
        {
            $MappedField = $ListFields | Where {$_.InternalName -eq $CSVField}
            If($MappedField -ne $Null)
            {
                $FieldName = $MappedField.InternalName
                #Check if the Field value is not Null
                If($Row.$CSVField -ne $Null)
                {
                    #Handle Special Fields
                    $FieldType  = $MappedField.TypeAsString 
                    If($FieldType -eq "User" -or $FieldType -eq "UserMulti") #People Picker Field
                    {
                        $PeoplePickerValues = $Row.$FieldName.Split(",")
                        $ItemValue.add($FieldName,$PeoplePickerValues)
                    }
                    ElseIf($FieldType -eq "Lookup" -or $FieldType -eq "LookupMulti") #Lookup Field
                    {
                        $LookupIDs = $Row.$FieldName.Split(",") | ForEach-Object { Get-LookupID -ListName $ListName -LookupFieldName $FieldName -LookupValue $_ }                
                        $ItemValue.Add($FieldName,$LookupIDs)
                    }
                    ElseIf($FieldType -eq "DateTime")
                    {
                        $ItemValue.Add($FieldName,[DateTime]$Row.$FieldName)
                    }
                    Else
                    {
                        #Get Source Field Value and add to Hashtable
                        $ItemValue.Add($FieldName,$Row.$FieldName)
                    }
                }
            }
        }
        Write-host "Adding List item with values:"
        $ItemValue | Format-Table
        #Add New List Item
        Add-PnPListItem -List $ListName -Values $ItemValue | Out-Null
    }
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

确保 CSV 中的数据格式正确，并且 CSV 文件将列表字段内部名称作为其列！您可以从以下位置下载 CSV 文件：

将 Excel 文件（而不是 CSV）导入 SharePoint Online 列表怎么样？当然，开始吧：使用 PowerShell 将 Excel 导入到 SharePoint Online 列表