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?

We have got 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 -UseWebLogin

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

The Add-PnpFile cmdlet automatically creates a nested folder structure, if it’s not there in the given SharePoint path already!

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 prior to 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 -UseWebLogin
 
#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 file: Import Files to 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