Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WesL
Regular Visitor

The input couldn't be recognized as a valid Excel document

Hello everyone.  Long time lurker, first time poster.  

I'm working with another department on getting some of their data into PowerBI from excel spreadsheets.  When I load the file to PowerBI I get a simple yet very frustrating error message of...

The input couldn't be recognized as a valid Excel document

Now this is a very complex spreadsheet with multiple tabs and lots of data.  Any commonly known issues that would prompt this error?

3 ACCEPTED SOLUTIONS
mitsu
Resolver IV
Resolver IV

One of the reason could be that the data that you are importing to PBI is  not in excel table. Select the rows and columns from the sheet that you want to import >> Insert Table in excel and try importing it should be able to read the data then .

View solution in original post

mllopis
Community Admin
Community Admin

Please use the Issues Forum for reporting bugs or issues like this one. Link: http://aka.ms/issues.powerbi.com

 

This Community Forum is aimed at allowing users to interact/share solutions or questions for how to achieve something with Power BI, rather than reporting specific bugs (we have a whole separate forum for that :))

 

When reporting the issue, it would be good to clarify whether you're using PowerBI.com directly or are using Power BI Desktop to import your Excel Workbook. If you're using Power BI Desktop, any spreadsheet data is supported (does not need to be a table), so if you're running into this issue there, chances are that the Excel document has some invalid parts that make it unrecognizable.

 

Thanks,
M.

View solution in original post

Actually I don't think it is a bug.  The user that supplied the excel spreadsheet had hidden the tabs that had the actual data.  So technically PowerBI was correct.  Might want to wait to hear back from the user before closing out and not getting all the information.

View solution in original post

19 REPLIES 19
grengifo
Frequent Visitor

After I installed the Aug 15, 2018 release of Power BI I experienced this issue with a couple of Excel files. I have recreated these files as Excel tables (no formulas, no formatting) and I was able to refresh the files successfully.

tkr_office
Advocate I
Advocate I

Hy,
I had this problem myself.
After deleting sheet by sheet, I found out that the Power BI/ Q can load the excel.
After deleting row by row in the problem sheets, I found out that the Power BI/ Q gave this error,
when there were Active hyperlinks in cells.
After removing hyperlinks the xlsx just loaded.
So if you store www.google.com in a cell it's working.
But when the www.google.com is an active hyperlink (clickable, blue underlined text, which goes to specified address) it says:
Unable to connect
We encountered an error while trying to connect.
Details: "The input couldn't be recognized as a valid Excel document."

mllopis
Community Admin
Community Admin

Please use the Issues Forum for reporting bugs or issues like this one. Link: http://aka.ms/issues.powerbi.com

 

This Community Forum is aimed at allowing users to interact/share solutions or questions for how to achieve something with Power BI, rather than reporting specific bugs (we have a whole separate forum for that :))

 

When reporting the issue, it would be good to clarify whether you're using PowerBI.com directly or are using Power BI Desktop to import your Excel Workbook. If you're using Power BI Desktop, any spreadsheet data is supported (does not need to be a table), so if you're running into this issue there, chances are that the Excel document has some invalid parts that make it unrecognizable.

 

Thanks,
M.

Actually I don't think it is a bug.  The user that supplied the excel spreadsheet had hidden the tabs that had the actual data.  So technically PowerBI was correct.  Might want to wait to hear back from the user before closing out and not getting all the information.

mitsu
Resolver IV
Resolver IV

One of the reason could be that the data that you are importing to PBI is  not in excel table. Select the rows and columns from the sheet that you want to import >> Insert Table in excel and try importing it should be able to read the data then .

This isn't really a good solution for .xslx files that are already set tables. I have many files that a very simple tables. The nuance is that this file is being generated by a third party program (R) and the workaround is opening the file manually and saving it. No changes to the table/format etc. We want to automate this process but it's going to be a huge headache with no long term solution in place.

Have you found a solution to this ? I have the exact same issue.

Yes the best solution is running Powershell script and automated it through SQL Agent on a server or automating via window scheduler if on your personal computer.  If you are running several files you can use the following two files

 

Config file

 

{
"LogFile" : "OpenSaveExcel.log",
"Files":
[


"C:\Program Files\test1.xlsx",
"C:\Program Files\test2.xlsx",
"C:\Program Files\test3.xlsx"
]
}

 

 

 

 

Powershell script:

 

$config = Get-Content 'config.json' | Out-String | ConvertFrom-Json
$fileList = $config.Files
$logFile = $config.LogFile

function WriteLog
{
param ([string]$logString)
Write-Host $logString
Add-content $logFile -value $logString
}

$excelComObject = New-Object -COM "Excel.Application"
$excelComObject.DisplayAlerts = $false

foreach ($file in $fileList) {
$timeStamp = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
WriteLog "$timeStamp Processing file $file"
try {
$fileHandle = Get-Item -Path $file -erroraction stop
$fileHandle.IsReadOnly = $false
$wb = $excelComObject.Workbooks.Open($file, $true, $false)
$wb.Save()
$wb.Close()
} catch {
WriteLog "$timeStamp Error: $_"
}
WriteLog "$timeStamp Done processing file $file"
}

$excelComObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelComObject)
Remove-Variable excelComObject

 

This creates a error log and also runs the command to open the file, save, then closes the file. 

 

Risks are permission issues if running on a virtual machine or server you may need to adjust for that when running remotely.

I put all my data in tables. It still does not work.

Hy,

I had this problem myself.

After deleting sheet by sheet, I found out that the Power BI/ Q can load the excel.

After deleting row by row in the problem sheets, I found out that the Power BI/ Q gave this error,

when there were Active hyperlinks in cells.

After removing hyperlinks the xlsx just loaded.

So if you store www.google.com in a cell it's working.

But when the www.google.com is an active hyperlink (clickable, blue underlined text, which goes to specified address) it says:

Unable to connect
We encountered an error while trying to connect.
Details: "The input couldn't be recognized as a valid Excel document."

Anonymous
Not applicable

Hi,

 

I am facing the same issue as above, However i am copying the whole file and paste it as value.

 

However its giving me sam error.

 

Kinldy suggest!

This problem arises when the Excel workbooks have cells with malformed URLs.

 

As a workaround, you can create an environment variable named "PQ_DisableNet45Containers", with a value of "true".

 

We are working on a fix that we hope to include in the September release of Power BI Desktop.

 

Sorry for the incovenience this is causing.

Will this be fixed for Power Query in Excel too, that's where I'm having the error and where most will too.


@SidJay wrote:

This problem arises when the Excel workbooks have cells with malformed URLs.

 

As a workaround, you can create an environment variable named "PQ_DisableNet45Containers", with a value of "true".

 

We are working on a fix that we hope to include in the September release of Power BI Desktop.

 

Sorry for the incovenience this is causing.


 

We believe this should have been addressed in all Power Query hosts.

 

Which version of Excel are you currently using? Which version of Power Query does that version of Excel currently have (available via Get Data -> Query Options -> Diagnostics).

 

Thanks

Anonymous
Not applicable

Just updated an hour ago and am still getting the error message (which only pops up when loading a table that's an append of multiple tables that all load just fine otherwise)

I was having the same issue - the workbooks were very simple but I believe the issue was probably caused by malformed urls as previously mentioned.

 

However, after downloading version 2.62.5222.761 published Sep 18, I found everything to be working. 64-bit version in case that matters.

@tkr_office Thanks for sharing this. This was the cause of the issue for me as well. At least the fix was easy!

Anonymous
Not applicable

Just had the same problem, but our data was already in the table format.

 

Oddly enough, changing it back to a standard data range ended up fixing it for us.

 

An alternate solution for anyone else getting stuck with this.

Spot on, just ran into this today, that was the cause for my error.  Appreciate the response.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors