none
Error opening Excel file with Power Query RRS feed

  • Question

  • I am trying to connect to an excel file (it exports from a system i use as "Microsoft Excel 97-2003 Worksheet" format), however when I try to connect to the file in Query (i am using Excel version Office 2016) it returns the following error message

    "Unable to connect

    We encountered an error while trying to connect

    Details:"External Table is not in the expected format"

    If I open the file in Excel then resave as a 2016 version .xlsx format, the error will not reappear, but it would be great if someone could tell me if there is a way to do this in the Query itself.

    Thanks

    Oliver




    • Edited by OllyRuf Thursday, February 20, 2020 11:53 PM
    Thursday, February 20, 2020 11:51 PM

Answers

  • Just curious, I have a file that exports from a system the same way.  It looks like it is an Excel 97 - 2003 file but if you open it, it tells you the file format extension and the filename.xls do not match and if you want to open it anyway.  Then, you have to save it as an Excel file or it thinks it is still in csv format.  Have you tried importing it into Power Query as From File From Text/CSV?
    Monday, March 2, 2020 1:29 PM
  • Hi Oliver. In addition to what Robert suggested, you may want to try importing the file into the latest version of Power BI Desktop to see if you get the same error. If not, this indicates you're hitting a bug in an older version of Power Query, and would need to update Excel to a more recent version (thus updating PQ in the process) in order to successfully import the file.

    Ehren

    Monday, March 2, 2020 6:34 PM
    Owner

All replies

  • Hi there. What version of PQ do you have running inside Excel 2016? Does the external table error go away if you click the refresh button in the navigator pane?

    Ehren

    Thursday, February 27, 2020 9:42 PM
    Owner
  • Hi Ehren,

    Excel is version 16.0.4954 & the PQ version seems to be 2.61.5192

    Steps to recreate

    1. New Query

    2. From File, from Workbook

    3. The error message appears at this stage (have not gotten to the query editor yet)

    4. it then prompts me to select a different format to open the file, however all options from there (automatic, CSV, excel) then it returns the same error message.

    Thanks for your advice.

    Regards

    Oliver

    Monday, March 2, 2020 5:37 AM
  • Just curious, I have a file that exports from a system the same way.  It looks like it is an Excel 97 - 2003 file but if you open it, it tells you the file format extension and the filename.xls do not match and if you want to open it anyway.  Then, you have to save it as an Excel file or it thinks it is still in csv format.  Have you tried importing it into Power Query as From File From Text/CSV?
    Monday, March 2, 2020 1:29 PM
  • Hi Oliver. In addition to what Robert suggested, you may want to try importing the file into the latest version of Power BI Desktop to see if you get the same error. If not, this indicates you're hitting a bug in an older version of Power Query, and would need to update Excel to a more recent version (thus updating PQ in the process) in order to successfully import the file.

    Ehren

    Monday, March 2, 2020 6:34 PM
    Owner
  • Thanks Ehren & Robert. I will try as you suggest with Power BI and when I get a chance I will let you know how I go.
    Friday, March 6, 2020 1:18 AM