网站搜索

SharePoint Online：使用 PowerShell 从 CSV 导入文件

要求：将文件从 CSV 上传到 SharePoint Online。

SharePoint Online：如何使用 PowerShell 从 CSV 导入文件？

您可能知道 PowerShell 是一种功能强大的脚本语言，使管理员能够自动执行许多任务。在这篇博文中，我们将向您展示如何使用 PowerShell 将文件从 CSV 导入到 SharePoint Online。

我们有一个 CSV 文件，其中包含指向网络文件共享的文件列表。我们希望将所有文件从 CSV 文件导入到 SharePoint Online 网站。以下是从 CSV 批量导入文件的 PowerShell 脚本：


#Parameters
$SiteURL = "https://crescent.SharePoint.com/sites/ostro"
$CSVFile = "C:\Users\Salaudeen\Desktop\Intralink-Mapping-v2.csv"

#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive

#Get the data from CSV file
$CSVData = Import-CSV $CSVFile
$Counter =1

#Loop through each Row in the CSV file and upload file to SharePoint
ForEach($Row in $CSVData)
{
    $File = Add-PnPFile -Path $Row.SourceFilePath -Folder $Row.FolderSiteRelativeURL -NewFileName $Row.FileName
    Write-Host "Uploaded File $($Row.FileName) - $Counter of $($CSVData.Count)"
    $Counter++
}

此 PowerShell 脚本将文件从给定的 CSV 上传到 SharePoint Online 库。如果给定的 SharePoint 路径中尚不存在，则 Add-PnpFile cmdlet 会自动创建嵌套文件夹！这是我的 CSV 文件：

使用 PowerShell 从 CSV 批量上传文件并设置元数据

从 CSV 文件上传多个文件并更新其元数据怎么样？嗯，这是我的 PowerShell 脚本，用于导入文件和设置元数据。在执行此脚本之前，请确保您在 SharePoint 库中创建了准确的列名称。


#Parameters
$SiteURL = "https://crescent.sharepoint.com/sites/Marketing"
$CSVFile = "C:\temp\FilesList.csv"
$ListName = "Migration"

#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)
    {
        #Get the Parent Item ID
        Return $ParentLookupItem["ID"]
    }
    Else
    {
        Return $Null
    }
}
  
#Connect to PnP Online
Connect-PnPOnline -Url $SiteURL -Interactive
 
#Get the data from CSV file
$CSVData = Import-CSV $CSVFile
$Counter =1

#Get fields to Update from the List - Skip Read-only, hidden fields
$ListFields = Get-PnPField -List $ListName | Where { (-Not ($_.ReadOnlyField)) -and (-Not ($_.Hidden)) }

#Loop through each Row in the CSV file and update metadata
ForEach($Row in $CSVData)
{

    #Get All columns from CSV
    $CSVFields = $Row | Get-Member -MemberType NoteProperty | Select -ExpandProperty Name | Where {$_.Name -notin ("FileName","SiteRelativeURL")}
     
    #Frame the Metadata to update
    $MetadataValue = @{}

    #Map each field from CSV to list
    Foreach($CSVField in $CSVFields)
    {
        $MappedField = $ListFields | Where {$_.Title -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(",")
                    $MetadataValue.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 $_ }                
                    $MetadataValue.Add($FieldName,$LookupIDs)
                }
                Else
                {
                    #Get Source Field Value and add to Hashtable
                    $MetadataValue.Add($FieldName,$Row.$CSVField)
                }                  
            }
        }
    }

    #Upload the File and Set Metadata 
    $File = Add-PnPFile -Path $Row.LocalPath -Folder $Row.'SiteRelativeURL' -Values $MetadataValue
    Write-Host "Uploaded File $($Row.Name) - $Counter of $($CSVData.Count)"
    $Counter++
}

以下是将文件从 CSV 导入到 SharePoint Online 文档库的 CSV 模板：

相关文章