Fixing up those Pesky # Filenames for Importing into OneDrive and SharePoint

Once you're used to the new way of working, OneDrive and SharePoint are great file storage systems. The biggest problem is getting your files into them. Sure, it's usually just a case of drag and drop but the real problems are related to some tighter controls on the file names.

The worst offenders are the ampersand and the hashtag. In fact, it seems to be really common for people to name their files with a hashtag in financial circles. For example: "Invoice No #675853.pdf"

OneDrive and SharePoint will "spit the dummy" if you try to upload a file that breaks these rules.

Interface Problems

What's worse is that while the upload tools will happily report all of the errors, it doesn't give you a chance to correct them and it doesn't tell you exactly where in the path the offending file is.

What's more, you can't copy the log of errors to the clipboard and I haven't seen any signs of any other forms of logging - not that I've really looked that hard for them.

In any case, fixing these problems by hand is just a little too much, especially if you're migrating a large company with lots of files and folders.  The better solution is to prepare the files before you upload them.

OneDrive Check

I looked around for a solution to this problem as I was quite reluctant to try to roll my own. Eventually, after trying several other non-functional solutions, I found what seems to be the Microsoft supported solution.

It's a little PowerShell script called OneDrive-Check.  Since it was so difficult to find, I figured it was worth posting about to see if I could raise awareness for this little hero.

How to Use it

First of all, you need to download the ZIP file, which includes accepting an agreement. On the off-chance that the file is gone one day... and since it's a tiny text file called "OneDrive-Check.ps1", I'll include the actual contents at the end of this post... that way, you can easily recreate it if you need to.

Because there are a lot of people out there that don't seem to remember DOS, I'm going to give you the DOS/PowerShell commands for things like changing directories. I hope everyone else doesn't find this too patronising. ... oh and means to press the enter key.   :-)

1. Click on the link, download the ZIP file and extract OneDrive-Check.ps1
   
2. Copy that file to a useful place (eg: C:\temp\OneDrive-Check.ps1)
   
3. Start Windows PowerShell (Click Start/Window and type Power, you'll find it). You might want to right-click on it and run as Administrator.
   
4. Once PowerShell has loaded, go to the location where you saved your file
   (for example:  C: then CD C:\temp ).
   
5. Type: Import-Module C:\temp\OneDrive-Check.ps1
   This should load the new module.
   
6. Now go to where the files that you want in OneDrive are stored:
   (eg:  M: then CD M:\DATA )
   Remember that if you're changing into a folder with spaces in the name, you will probably want to enclose it in inverted commas;
   (eg: CD "M:\Data\Our Company Files" )
   
7. To check what will happen, you can type
    OneDrive-Check -Folder "M:\Data\Our Company Files"
   (obviously substitute your folder name)
   
8. To actually make the changes, you can type
   OneDrive-Check -Folder "M:\Data\Our Company Files" -Fix
   
9. Best of luck. It's not perfect but it will significantly reduce your workload.
   (did I mention that it does sub-directories too?)
   

The main thing that doesn't seem to work (for obvious reasons) is that it doesn't resolve super-long file names.

Once the script has finished its work you can start dragging and dropping your folders into OneDrive or SharePoint document libraries.

And Now the "OneDrive-Check.ps1" File

So, just in case the OneDrive-Check.ps1 file disappears off the internet someday, here it is.  Bear in mind that in posting it on the internet, it's possible that some character changes might occur, so test it carefully before use.

(and a huge thank you to Andreas Molin, wherever he is. What a Hero!)


### Created by Andreas Molin
### Usage: Import-Module OneDrive-Check.ps1
### OneDrive-Check -Folder
### OneDrive-Check -Folder -Fix

function OneDrive-Check($Folder,[switch]$Fix){
    $Items = Get-ChildItem -Path $Folder -Recurse

    $UnsupportedChars = '[!&{}~#%]'

    foreach ($item in $items){
        filter Matches($UnsupportedChars){
        $item.Name | Select-String -AllMatches $UnsupportedChars |
        Select-Object -ExpandProperty Matches
        Select-Object -ExpandProperty Values
        }

        $newFileName = $item.Name
        Matches $UnsupportedChars | ForEach-Object {
            Write-Host "$($item.FullName) has the illegal character $($_.Value)" -ForegroundColor Red
            if ($_.Value -match "&") { $newFileName = ($newFileName -replace "&", "and") }
            if ($_.Value -match "{") { $newFileName = ($newFileName -replace "{", "(") }
            if ($_.Value -match "}") { $newFileName = ($newFileName -replace "}", ")") }
            if ($_.Value -match "~") { $newFileName = ($newFileName -replace "~", "-") }
            if ($_.Value -match "#") { $newFileName = ($newFileName -replace "#", "") }
            if ($_.Value -match "%") { $newFileName = ($newFileName -replace "%", "") }
            if ($_.Value -match "!") { $newFileName = ($newFileName -replace "!", "") }
         }
         if (($newFileName -ne $item.Name) -and ($Fix)){
            Rename-Item $item.FullName -NewName ($newFileName)
            Write-Host "$($item.Name) has been changed to $newFileName" -ForegroundColor Green
         }
    }
}