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
The script below also shows how to update metadata on the document you have uploaded using data from the CSV file.
$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”
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.