office-365-advent-calendar

Scenario

A very common scenario is that you have multiple files that need to be added to SharePoint together with some metadata.  One way of achieving this is by uploading all files to the target library, and then using the Quick Edit functionality to update the metadata for them. However, this is a bit cumbersome, especially when you do not just have a few documents but potentially hundreds of them.

This solution provides an easy way to upload as many documents as wanted together with their corresponding metadata to to a single library in SharePoint Online. You’ll first need to prepare a CSV file with all relevant data, and then run a script to upload the documents and add their individual metadata.

Code

To demonstrate the script, I created a document library and added two columns called ‘Country’ and ‘Customer’.

bulk-upload-metadata-files-1

 

First, you need to prepare the CSV file with all the relevant data – the path to the files you want to upload as well as the metadata you want to use for each file. This data will then be used by the script by first uploading the files mentioned in the CSV file, and adding the corresponding metadata. Here is my example CSV:

file,Country,Customer
Invoice 18932.pdf,Singapore,Qi Feng Pte Ltd
Cost Calculations.xlsx,USA,Contoso Inc.
Sales Agreement Template.docx,Germany,Fabrikam GmbH

The first row, the header row, is very important here. As you can see, I have three columns: ‘file’, ‘Country’, and ‘Customer’. ‘file’ is used to point to the file that should be uploaded. In my case, they are in the same folder as the script and the CSV, but you could also add an absolute path to a file somewhere else (C:\Users\rmodery\Desktop\MyFile.docx). This is the only column that is referenced directly in my script, and you should keep it.

Country and Customer are the additional columns from my document library. Important to note is that you need to use the internal names for your columns in the CSV file, not the display names (here‘s how you can find it out). You can replace these two columns with any of your columns without having to modify the code, it will read and use the available columns dynamically. So your structure could look like file,Status, DueDate,AssignedTo.

After you created your CSV file, modify the following code to match your environment. Replace ‘mytenant.sharepoint.com’ with the site you want to upload files to, rename ‘filestoupload.csv’ with your CSV filename, and lastly replace ‘Metadata Test’ further below with the path to your document library. You could also specify a path to a specific folder in a document library here, please review the Add-PnPFile documentation for more details.

$cred = Get-Credential
Connect-PnPOnline -Url https://mytenant.sharepoint.com -Credentials $cred

$files = Import-Csv filestoupload.csv
$metadatacolumns = $files | Get-Member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name' | Where {$_ -ne "file"}

foreach($file in $files) {
 $values = @{}
 foreach($metadatacolumn in $metadatacolumns) {
  $values.Add($metadatacolumn, $file.$metadatacolumn)
 }
 Add-PnPFile -Path $file.file -Folder "Metadata Test" -Values $values
}

 

And here’s my library after I ran the script with the CSV file above, three documents were uploaded with the corresponding metadata:
bulk-upload-metadata-files-2

12 thoughts on “Office 365 Advent Calendar – 06 Bulk-Uploading Files with Metadata to SharePoint Online”

  5. Excellent! Very nice and concise. It was what I was looking for. Two challenges… SharePoint doesn’t seem to like when there is no value even if the SP field is not mandatory. The bigger challenge I’m running into is setting the Enterprise Keywords column. I know this post is old but if you happen to see it and can write a response in less time it takes to drink a cup of coffee that would be great. If not, thanks much for posting this originally!!

    Reply

    1. Hi Rob,
      found a solution for the Enterprise Keywords. You need to update the foreach($metadatacolumn…) loop as follows:

      foreach($metadatacolumn in $metadatacolumns) {
      if($metadatacolumn -eq “TaxKeyword”) {
      $values.Add($metadatacolumn, @($file.$metadatacolumn,$file.$metadatacolumn))
      } else {
      $values.Add($metadatacolumn, $file.$metadatacolumn)
      }

      For the Enterprise Keywords (Field: TaxKeyword) the term (in the format “System|Keywords|MyTerm”) needs to be provided twice. Found this in the following article: https://www.thelazyadministrator.com/2018/08/20/manage-and-administer-sharepoint-using-sharepointpnp-powershell/
      Note that I’m only able to use a single term in my code above. There are way how it could work with multiple terms, but decided to provide this simple solution first.

      Reply

  6. Hi Rene,
    Thanks for posting, this is the best solution that I have found so far.

    2 questions:

    1) Is there a preferred way to format the ‘file’ column to allow for folder/sub-folder creation? I have tried the following with no luck.
    \Fruits\Grapes\concord.pdf
    C:\Amon\Fruits\Grapes\concord.pdf
    /Fruits/Grapes/concord.pdf

    2) Only one column of metadata populates, even when they are all set to ‘Single Line of Text’. Have you ran across this issue?

    I am using SharePoint Online.

    Thanks in advance!

    Reply

    1. Hi Amon,

      haven’t got a solution for your first problem. Might work with an updated code that checks the value of the file column for a folder hierarchy and then creates it accordingly, with the last step being the upload. Will see if I can code something like this at some point.
      Second issue doesn’t sound familiar, it was working for me without any issues so far. Not sure why it doesn’t work properly for you

      Reply

    2. Ok, was easier than I thought, as Add-PnPFile actually creates any folders that are provided as part of the -Folder parameter.
      Here’s the code:
      $cred = Get-Credential
      Connect-PnPOnline -Url https://mytenant.sharepoint.com -Credentials $cred
      $library = "Metadata Test"

      $files = Import-Csv filestoupload.csv
      $metadatacolumns = $files | Get-Member -MemberType 'NoteProperty' | Select-Object -ExpandProperty 'Name' | Where {$_ -ne "file"}

      foreach($file in $files) {
      $values = @{}
      foreach($metadatacolumn in $metadatacolumns) {
      $values.Add($metadatacolumn, $file.$metadatacolumn)
      }
      $path = $file.file.split("/")
      if($path.length -eq 1) {
      Add-PnPFile -Path $file.file -Folder $library -Values $values
      } else {
      $folder = $library + "/"+($file.file -replace $path[$path.length-1],"")
      Add-PnPFile -Path $file.file -Folder $folder -Values $values
      }
      }

      This way it actually also works with a folder structure that is present. Let me know if any further clarification is required

      Reply

      1. Hi Rene,

        Thanks so much for this!
        Very helpful and thorough and I’m able to make it work for my needs. I really appreciate your time.

        Amon

  7. Hi Rene,

    Thanks for the post.

    I am getting the below error while executing your code,

    Add-PnPFile : Cannot bind argument to parameter ‘Path’ because it is null.
    At line:6 char:20
    + Add-PnPFile -Path $file.file -Folder “SPMTTEST” -Values $values

    Can you please help?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.