Import Multiple Excel files from Sharepoint to Power Query "File Contains Corrupted Data" Error

%3CLINGO-SUB%20id%3D%22lingo-sub-1043480%22%20slang%3D%22en-US%22%3EImport%20Multiple%20Excel%20files%20from%20Sharepoint%20to%20Power%20Query%20%22File%20Contains%20Corrupted%20Data%22%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1043480%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20need%20to%20import%20multiple%20excel%20files%20from%20SharePoint%20with%20Power%20Query.%20During%20loading%20the%20data%2C%20Excel%20showed%20error%20notification%20which%20said%20%22Files%20Contains%20Corrupted%20Data%22.%20The%20funny%20thing%20is%20when%20I%20tried%20to%20import%20those%20same%20files%20from%20local%20folder%20with%20Power%20Query%2C%20it%20worked%20fine.%20All%20the%20files%20extension%20are%20the%20same%20%22.xlsx%22%2C%20containing%20only%201%20sheet%20with%20the%20same%20quantity%20of%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20ever%20encounter%20such%20similar%20problem%20and%20already%20have%20a%20solution%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1043480%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1045544%22%20slang%3D%22en-US%22%3ERE%3A%20Import%20Multiple%20Excel%20files%20from%20Sharepoint%20to%20Power%20Query%20%22File%20Contains%20Corrupted%20Data%22%20Error%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1045544%22%20slang%3D%22en-US%22%3EI%20don't%20know%20why%20this%20is%20happening%20but%20what%20method%20are%20you%20using%20to%20import%20the%20files%3F%20From%20SharePoint%20Folder%3F%20Is%20it%20a%20very%20long%20folder%20path%20name%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1045619%22%20slang%3D%22en-US%22%3ERE%3A%20Import%20Multiple%20Excel%20files%20from%20Sharepoint%20to%20Power%20Query%20%22File%20Contains%20Corrupted%20Data%26amp%3Bqu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1045619%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3EHi%20thanks%20for%20the%20reply.%20Yes%20I%20indeed%20use%20the%20get%20data%20function%20--%26gt%3B%20From%20SharePoint%20Folder%2C%20also%20the%20folder%20path%20name%20is%20short%20such%20as%20%3CA%20href%3D%22https%3A%2F%2Fxxxxxxxx.sharepoint.com%2Fsites%2Fxx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxxxxx.sharepoint.com%2Fsites%2Fxx%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1045662%22%20slang%3D%22en-US%22%3ERE%3A%20Import%20Multiple%20Excel%20files%20from%20Sharepoint%20to%20Power%20Query%20%22File%20Contains%20Corrupted%20Data%26amp%3Bam%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1045662%22%20slang%3D%22en-US%22%3EAnd%20are%20the%20source%20files%20.xlsx%20or%20.xls%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20just%20fishing%20for%20ideas%20here...%20as%20it%20sounds%20like%20a%20total%20bug.%20How%20big%20are%20the%20files%20in%20MB%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1045665%22%20slang%3D%22en-US%22%3ERE%3A%20Import%20Multiple%20Excel%20files%20from%20Sharepoint%20to%20Power%20Query%20%22File%20Contains%20Corrupted%20Data%26amp%3Bam%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1045665%22%20slang%3D%22en-US%22%3ESounds%20like%20a%20similar%20issue%20here.%20One%20person%20suggested%20the%20fact%20that%20the%20files%20were%20bigger%20than%2030MB%20was%20the%20problem%20%3CA%20href%3D%22https%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FDesktop%2FSharepoint-excel-data-refresh-showing-corrupted-data%2Ftd-p%2F674816%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fcommunity.powerbi.com%2Ft5%2FDesktop%2FSharepoint-excel-data-refresh-showing-corrupted-data%2Ftd-p%2F674816%3C%2FA%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1056992%22%20slang%3D%22en-US%22%3ERE%3A%20Import%20Multiple%20Excel%20files%20from%20Sharepoint%20to%20Power%20Query%20%22File%20Contains%20Corrupted%20Data%26amp%3Bam%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1056992%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%20Hi%20thanks%20for%20the%20reply%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20total%20files%20in%20said%20folder%20were%20500%20MB.%20On%20the%20other%20hand%2C%20I%20have%20another%20SharePoint%20Site%20with%20100%20MB%20file%20and%20successfully%20fetch%20its%20data%20with%20Power%20Query.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20for%20now%2C%20the%20error%20notification%20has%20been%20come%20and%20go%20randomly.%20At%20times%2C%20I%20can%20refresh%20the%20power%20query%20just%20fine.%20When%20it%20showed%20the%20error%20notification%2C%20I%20just%20close%20and%20load%20the%20excel%20file%20with%20power%20query%20and%20refresh%20the%20SharePoint%20Site%20several%20times.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20now%20seems%20not%20too%20catastrophic%2C%20but%20its%20getting%20annoying%20when%20I%20need%20the%20data%20urgently%20and%20I%20get%20the%20error%20with%20no%20absolute%20solution%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1057182%22%20slang%3D%22en-US%22%3ERE%3A%20Import%20Multiple%20Excel%20files%20from%20Sharepoint%20to%20Power%20Query%20%22File%20Contains%20Corrupted%20Data%26amp%3Bam%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1057182%22%20slang%3D%22en-US%22%3EOK%2C%20thanks%20for%20letting%20us%20know.%20One%20other%20thing%20to%20try%20is%20Pausing%20OneDrive%20sync%20when%20you're%20running%20Power%20Queries.%20Occasionally%20seems%20to%20cause%20issues.%3C%2FLINGO-BODY%3E
New Contributor

So I need to import multiple excel files from SharePoint with Power Query. During loading the data, Excel showed error notification which said "Files Contains Corrupted Data". The funny thing is when I tried to import those same files from local folder with Power Query, it worked fine. All the files extension are the same ".xlsx", containing only 1 sheet with the same quantity of row. 

 

Does anyone ever encounter such similar problem and already have a solution?

6 Replies
I don't know why this is happening but what method are you using to import the files? From SharePoint Folder? Is it a very long folder path name?

@Wyn HopkinsHi thanks for the reply. Yes I indeed use the get data function --> From SharePoint Folder, also the folder path name is short such as https://xxxxxxxx.sharepoint.com/sites/xx

And are the source files .xlsx or .xls?

I'm just fishing for ideas here... as it sounds like a total bug. How big are the files in MB?
Sounds like a similar issue here. One person suggested the fact that the files were bigger than 30MB was the problem https://community.powerbi.com/t5/Desktop/Sharepoint-excel-data-refresh-showing-corrupted-data/td-p/6...

@Wyn Hopkins  Hi thanks for the reply

 

The total files in said folder were 500 MB. On the other hand, I have another SharePoint Site with 100 MB file and successfully fetch its data with Power Query.

 

As for now, the error notification has been come and go randomly. At times, I can refresh the power query just fine. When it showed the error notification, I just close and load the excel file with power query and refresh the SharePoint Site several times.

 

The problem now seems not too catastrophic, but its getting annoying when I need the data urgently and I get the error with no absolute solution

OK, thanks for letting us know. One other thing to try is Pausing OneDrive sync when you're running Power Queries. Occasionally seems to cause issues.