SharePoint Online: Import Files from a CSV using PowerShell

Requirement: Upload files from a CSV to SharePoint Online

SharePoint Online: How to Import Files from a CSV using PowerShell?

You may know PowerShell is a powerful scripting language that enables administrators to automate many tasks. In this blog post, we’ll show you how to use PowerShell to import files from a CSV into SharePoint Online.

We have a CSV file with a list of files pointing to a network file share. We wanted to import all files from a CSV file to the SharePoint Online site. Here is the PowerShell script to bulk import files from a CSV:

#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++
}

This PowerShell script uploads files from the given CSV to SharePoint Online libraries. The Add-PnpFile cmdlet automatically creates a nested folder if it’s not there already in the given SharePoint path! Here is my CSV File:

powershell to import files to sharepoint online from csv

Bulk Upload Files from a CSV and Set Metadata using PowerShell

How about uploading multiple files from a CSV file and updating their metadata? Well, Here is my PowerShell script to import files and set metadata. Make sure you have the exact column names created in your SharePoint Library before executing this script.

#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++
}

Here is the CSV template to import files to the SharePoint Online document library from a CSV:

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply