Sharepoint Team Site corrupting Excel Documents

Question

• I have an organisation that uses a proprietary web app that produces .XLS files.
• The organisation has a 365 subscription and use a Sharepoint Team Site as their document storage.
• They exclusively use OneDrive to sync the team site, and only expect to be able to access file this way.

We have found that copying these .XLS files to a Sharepoint Online Team Site makes the Excel files unreadable/corrupt.

PS C:\temp\exceltest> $LocalFileHash = Get-FileHash .\EXCEL_Document.xls
PS C:\temp\exceltest> copy .\EXCEL_Document.xls $OD
PS C:\temp\exceltest> Copy .\EXCEL_Document.xls $TD
PS C:\temp\exceltest> $ODFileHash = Get-FileHash $od\EXCEL_Document.xls
PS C:\temp\exceltest> $TDFileHash = Get-FileHash $Td\EXCEL_Document.xls
PS C:\temp\exceltest> $LocalFileHash.Hash
    A93E7761317C1DE502533DEDC87F6F301F1E426C4EF68280F94FE3A27DA1E4F4
PS C:\temp\exceltest> $ODFileHash.Hash
    A93E7761317C1DE502533DEDC87F6F301F1E426C4EF68280F94FE3A27DA1E4F4
PS C:\temp\exceltest> $TDFileHash.Hash
    BCB70570834EE10FB27748C69B2D550D671404CE8BD8E1861A4786DD81396D9E

• In the above example I have a sample Excel Document (EXCEL_Document.xls) on local storage.
• This file is copied to a location in a personal OneDrive for Business folder ($OD)
• The file is copied to a location on the Sharepoint Team Site ($TD)

After this sequence we can see the Local file and the OneDrive for Business copy are identical, where as the Sharepoint Team Site version is different.

Attempting to open all of these in Excel works for the Local and OneDrive copies, but fails with the Sharepoint version.

I've since identified a large number of these files that will no longer open, thanks it seems to some process that alters the .XLS file on copy to the team site.

Further to this the file sizes change:

local:

gci .\EXCEL_Document.xls 
Length Name              
------ ----              
79360 EXCEL_Document.xls

OneDrive:

gci $OD\EXCEL_Document.xls 
Length Name              
------ ----              
79360 EXCEL_Document.xls

Sharepoint:

gci $TD\EXCEL_Document.xls 
Length Name              
------ ----              
92160 EXCEL_Document.xls

As a further test I opened the excel document from local storage and saved it straight back to the same location as .\EXCEL_Document_v2.xls

PS C:\temp\exceltest> Get-FileHash .\EXCEL_Document_v2.xls
Hash                                                                   Path
----                                                                   ----
1C3A3E626EE535A1CB64DD018D10DFF54D801E02983C6089F07C6D1B0F9D6792       C:\temp\exceltest\EXCEL_Document_v2.xls



PS C:\temp\exceltest> cp .\EXCEL_Document_v2.xls $TD\
PS C:\temp\exceltest> Get-FileHash $TD\EXCEL_Document_v2.xls
Hash                                                                   Path
----                                                                   ----
4F3E3E1EFDBAB162F0A62F5C2D3EC691908C9ECE98C67A9C15289AF1EDCB498E       $TD\EXCEL_Document_v2.xls

Again we can see the file changing on copy to Sharepoint Team Site ($TD), however in this instance the file copied to $TD will open in Excel as expected.

As a third example, copying the same file with the .xls extension removed entirely shows the file hash the same on both local and the Sharepoint storage, demonstrating that this is an automatic process applied to Microsoft Office files on copying to the sharepoint team site (but interestingly not OneDrive for Business).

• This behaviour is the same between Mac and Windows clients.
• I'm pretty sure that this is new as we've been using everything this way since April and this week is the first time we have noticed an issue.
• Some historic documents are corrupt beyond repair, some others are not. I'm assuming those that have been edited and saved in Excel are OK and the ones that are not were just copied in as is.

---

This is not behaviour I desire or expect, I assume that Microsoft/Sharepoint is altering the file in some way to be 'compatible' with Sharepoint's capabilities.

I also assume that the root of the problem lies with the web app and how it produces the .xls files, probably in a way that doesn't properly conform to Microsoft's standards for these files, as demonstrated in the second example.

However on the assumption that I can't get the web app updated, and that we also have a large number of older files that we still need to keep that are impractical to re-create them and/or deeply time consuming to repair/convert each of them...

• Are there any resources I can use to understand whats happening here.
• What are my options for controlling/preventing this behaviour in sharepoint?

Answer 1

The difference in file size

The difference in file size in SharePoint and Onedrive as opposed to local file system is due to a SharePoint process called property promotion and demotion. It is a process which syncs sharepoint properties into the file and properties in file back to SharePoint. Please refer to article here and here for more information.

The reason file in onedrive is same as local file is that Microsoft stopped/disabled document parser on onedrive few years ago (probably because onedrive is used mostly to dump documents as opposed to structured team site with content types and columns).

Unfortunately there is no way of changing the parser behavior on SharePoint online or ondrive (the option exists on on-premise environment).

Possible cause of issue

As you mentioned there may be an issue with your document generation application where in it may be creating file in incompatible format. When the file is saved to SharePoint, it is probably trying to push properties back to document and in process making it corrupt. Regarding the case when you performed file save as, when you try to save as a file it tries to correct the older file format while saving a new copy (I have seen this behavior esp with older file formats like Office 2003)

Possible Fixes:

Although it may not be possible to diagnose the issue and provide accurate solution without examining the files, you can try below.

1. Check if the library uses custom content types, site columns. Try to save it to a library that does not use any custom metadata and just default document content type (although SharePoint by default will atleast try to sync title field).
2. Try removing the document metadata from the files saved to SharePoint. You can try using this script as mentioned in this blog post (perform this operation on a copy of the actual files on SharePoint for testing :) )

For future files, I would suggest to update the webapplication to generate files using latest file format. In the meanwhile try to save files to SharePoint on-premise site (if available, where you can disable document parse on that site) or onedrive if possible.