There are a number of ways to upload files to SharePoint, but every now and then I get asked to do something a bit different. In this case it was to migrate files from a database into SharePoint. The database had a file path reference with documents stored in a file structure on Windows file share. Success would see over 80,000 documents transferred into just over 600 Document Sets. Each Document Set contains several sub folders (because it is accessed primarily via Explorer).
I used a SQL Query to extract a CSV file containing the filename for each attachement, it’s path, employeeID for the file and the date field from the source database. You can easily add other fields if needed and then update the metadata on the documents as they are uploaded.
Each document Set was named with the EmployeeID and then contained a subfolder called “Imported Files”. This is the bit that wasn’t immediately obvious to script in PowerShell, the trick was to use the SubFolder reference in the first level subfolder (Document Set in this case) and then reference the second level subfolder:
$spFile = $spList.RootFolder.SubFolders[$Target].SubFolders["Imported Files"].Files.Add($doc.Filename,$Uploadedfile)
If you don’t need to go two folders deep, use this PowerShell instead
$spfile=$spList.RootFolder.SubFolders[$Target].Files.Add($doc.Filename,$Uploadedfile)
The script below also shows how to update metadata on the document you have uploaded using data from the CSV file.
Full script:
$spWeb = Get-SPWeb "http://sharepoint/subsite"
$spList = $spWeb.Lists["DocumentLibrary"]
# Read CSV file
$documents = Import-CSV X:\filestoimport.csv -Header Filename,FilePath,EmployeeID,Name, DocumentDate
# Upload the files
foreach($doc in $documents)
{
$File= "x:\files\" + $doc.FilePath
$Target = $doc.EmployeeID
$UploadedFile = [io.file]::ReadAllBytes($File)
$spFile = $spList.RootFolder.SubFolders[$Target].SubFolders["Imported Files"].Files.Add($doc.Filename,$Uploadedfile)
write-host "File:" $spFile
$item = $spFile.Item
$item["DocumentDate"] = $doc.DocumentDate
$item["Source"] = "Old Database"
$item.SystemUpdate($false)
}
So that’s it! I can think of a few times I could have used this script for migrating documents in the past including things like PDF files generated from an ERP system via a batch process and other bulk file imports.
SharePoint lightbulb moments 
Hi Steve,
Thanks for the great post. I am having a problem though using a modified form of the upload .add method to assign an original file to a created document set in an SPO environment. I assume we are using Microsoft’s overload format for a SPFileCollection where the arguments refer to (urlOfFile as a string, file as a stream). Thus, two arguments.
BTW – I have just created the document set inside the same Powershell block of code. Here is my script –
#Load SharePoint CSOM Assemblies
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.DocumentManagement.dll”
$ErrorActionPreference = “Stop”
#Parameters
$SiteURL=”https://banana.sharepoint.com/sites/developerrfischer”
$DocumentLibraryName = “Documents”
$DocumentSetName = “Test-DocSet2-NewCT”
#Content Type ID from the List!
$ContentTypeID=’0x0120D52000518EFBF523880E4BA504787F04369F7200FCCB71563C52C14CA7DBC31BF83C76B9′
#Get Credentials to connect
$Cred= Get-Credential
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)
#Get the Artifacts
$DocumentLibrary=$Ctx.Web.Lists.GetByTitle($DocumentLibraryName)
$Ctx.Load($DocumentLibrary)
$Ctx.Load($DocumentLibrary.RootFolder)
#Get content type from library by ID
$ContentType = $DocumentLibrary.ContentTypes.GetById($ContentTypeID)
$Ctx.Load($ContentType)
$Ctx.ExecuteQuery()
$setColor = “Green”
$setHeight = [Double]::Parse(“6.5”)
$setFavCity = “Detroit”
If($ContentType -ne $Null)
{
# sharepoint online powershell create document set from content type
$DocumentSet = [Microsoft.SharePoint.Client.DocumentSet.DocumentSet]::Create($Ctx,$DocumentLibrary.RootFolder,$DocumentSetName,$ContentType.ID)
$Ctx.ExecuteQuery()
$CurrentDocumentSet = $Ctx.web.GetFolderByServerRelativeUrl($DocumentSet.Value)
$Ctx.Load($CurrentDocumentSet.ListItemAllFields)
$Listitem = $CurrentDocumentSet.ListItemAllFields
$Listitem[“Color”]=$setColor
$Listitem[“Height”]=$setHeight
$Listitem[“Favorite_x0020_city”]=$setFavCity
$Listitem.Update()
$Ctx.ExecuteQuery()
$File = “C:\Users\myuser\Desktop\Download.xls”
$UploadedFile = [io.file]::ReadAllBytes($File)
write-host “File digitalized”
$docFilename = “Download.xls”
$spFile = $DocumentLibrary.RootFolder.Files.Add($File,$Uploadedfile)
Write-host “Document Set ‘$DocumentSetName’ Created Successfully!'” -f Green
}
else
{
Write-host “Content Type ‘$ContentTypeName’ doesn’t exist!'” -f Yellow
}
}
Catch {
write-host -f Red “Error:” $_.Exception.Message
}
In your example, I assume your CSV file contains only the file name when doc.Filename is referred to. Is that correct? I keep getting an error because the system says it cannot find a suitable overload with 2 arguments. I have tried using the filename as a string in the first position, but this is not a URL. In any case, what URL is Microsoft referring to? I’m a little confused here. Can you help me out?
Thanks for your great article sir.
—Ray
Thanks.