0KB Files in SharePoint Online – ShareGate Migration
Issue: Opening a 0KB file in Excel produces the error “The workbook cannot be opened” via Web. On Desktop, it says, “Excel cannot open the file X because the file format or file extension is not valid,” Other files like Docx and PPTx appear blank when opened for the first time and don’t throw errors but will save the file and update the size.
The file Size in SharePoint is blank for these files:
Solution: In our scenario, the problem was caused by copying with ShareGate. Every piece of file migration software has some issues, some worse than others, but you should never fully trust this kind of software without testing the results. Unfortunately, this copy job in ‘Insane’ mode seems to have created 0KB empty files, and since the file now exists, and the timestamps align, other copy jobs aren’t looking at the hash or size of the files and skipping them. This is a nightmare, given there are many files and sub-folders to go through, so I’ve devised an amateur scripting solution to recursively identify these 0KB files within all Document Libraries in a Site Collection.
Download: Scan0KBFiles (PowerShell)
You’ll end up with a randomly named CSV file with all of the 0KB files listed when the scan completes. Ignore what’s on the console unless you want to keep an eye on things, but capturing that data won’t be necessary as it scrolls in PowerShell.
If you get errors, make sure you have done Install-Module MSOnline, AzureAD, and Microsoft.Online.SharePoint.PowerShell, SharePointPnPPowerShellOnline, and all modules are on the latest versions if they already exist on the box.
How to Install the PnP PowerShell Module
If you’re seeing Throttling, I do have a version of this script that works as an AzureAD registered app, versus using credential authentication.
I tried testing other migration tools, all the ones you’d find on Google, and it’s pretty bad out there right now. If they can even hook into your tenant, the UIs are wonky, and you get none of the visibility or configurability in other tools. But, now, amongst many other bugs, you can see that in the case of ShareGate, that 5k gets you problems like this one, which admittedly, are a nightmare considering there are over 1/4 million files to search through looking for these 0KB stubs that have replaced valid data due to the bug.
Ad: FixFinder – Self-Help Automation platform that lowers your Tier-1 volume with a user empowering Desktop interface. FixFinder hooks into your existing MSP and IT tools to provide Unified Knowledge, Custom Wizards, Status Pages, Full Ticketing, and an Application library. Check it out!