[DataFormat.Error] External table is not in the expected format.

Question

Community,&nbsp;I am having a problem importing files from a sharepoint folder (6 files that need to be brought together).&nbsp;I see these as .xls files in Sharepoint and therefore use:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; let&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Source = Folder.Files(.... /sharepoint location".......),// no problem&nbsp;&nbsp;&nbsp; #"Removed Other Columns" = Table.SelectColumns(Source,{"Content"}),&nbsp;&nbsp;&nbsp; #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "GetExcelData", each Excel.Workbook([Content])),//no problem - I am able to see all the data in PowerQuery&nbsp;Everything looks fine - ready to go.&nbsp;However, when I hit "Close and Apply" - the system returns an error:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [DataFormat.Error] External table is not in the expected format.&nbsp;The original files generated from a SQL query - and then the results of the query are pasted into an Excel file.... The orginal response that is being pasted is a tab delimited file - which may be causing the problem at some level.&nbsp;&nbsp;&nbsp;&nbsp;Is there a different approach I should be using to open these files? Instead of Excel.Workbook([Content}) is there a something else that will be more appropriate for a tab delimited file?&nbsp;Thanks in advance.&nbsp;&nbsp;&nbsp;

johanno · Answer

I saw in another forum that someone wrote that this can occur when you export to Excel files from third party software. A workaround that worked for me is that if you open each file with Excel and save it, after this you can import it. It's not a good solution though if you have many files.

przemekkucia · Answer

Same error,&nbsp;In our case it seems that access restrictions create the problem. Even with full control given to me. After removing them it worked like magic.&nbsp;&nbsp;Sadly, if files have to be restricted - we haven't found any workaround. (Power Query for Excel also didn't work)

augustindelaf · Answer

Virtual_Ames&nbsp;I have exactly same problem (also xls files from a SharePoint.Files request) !&nbsp;&nbsp;The only solution that I found is to convert those files to .xlsxHope that helps !&nbsp;Augustin de la FouchardiereMYPE&nbsp;https://www.mype-consulting.com/

ankitpatira · Answer

Virtual_Ames&nbsp;Can you not use in-built sharepoint connector and connect that way ? Maybe it can get rid of that error using in-built connector.

virtual_ames · Answer

Ankit,&nbsp;Thanks -&nbsp;using the SharePoint Folder approach has the same result.&nbsp;I can see the folder, and see the .xls files.&nbsp;&nbsp;However, when I&nbsp;click the Content column to combine binaries, I get the same problem.&nbsp;(Excel.Workbook(#Combined Binaries) is auto-generated upon clicking the double down arrows in the Content column shown above).

Forum Discussion

[DataFormat.Error] External table is not in the expected format.

10 Replies

Related content

[DataFormat.Error]

External table is not in the expected format

Expression.SyntaxError: Token ',' expected.

DataFormat.Error: External table is not in the expected format. Details: when i've removed rows and

DataFormat.Error: There were more columns in the result than expected..