Details: "The input couldn't be recognized as a valid Excel document."

[smoss115]

I have a report generated each morning from a third party software. The report is in .xlsx format. It contains 3 worksheets of very "vanilla" information. No hyperlinks or weird formatting. I get the error "Details: "The input couldn't be recognized as a valid Excel document."" when I try to connect to that file. However, if I edit the file in ANY WAY, I am able to successfully connect to the file. 

I'm attempting to use workflows to automatically move this file from Outlook to Sharepoint, and then auto-refresh the Power BI report each morning. But the error prevents me from doing that. Why is this error occurring? Are there any workarounds for actually editing the file each day....rather than me having to do it manually?

[patrickchung]

I imported data from an excel with many tabs (I seperate around 7 tabs for each table in PBI). everything was running well for a while since a few months ago and I switched to work on other projects instead, however, I just back to it and found this issue and tried many options in this community but it's not solved my issue.

my solution after I did try my job, in my Power Query, I delete some of tables (3-4 tables) and import them again each one that I deleted and then try to refresh and apply to frontend. that works for me. 

[tengelkes]

And still this has not been resolved. 
I am indeed saving writing data to exel file from R. 
PowerBI give the error. 
I open and close the file in Excel and then PowerBI accepts.

But, I do want to have it automatically in the workflow.
PowerBI, please solve this issue.

[Anonymous]

I know this is a couple years old, but I was receiving the same error.  My problem turned out to be that I had a space in the library name.  Creating a new library with no spaces solved my issue. 

[v-danhe-msft]

Hi @smoss115 ,

From the error information, it may due to that the data you are importing to PBI is not in excel table, you could check your excel file and  remove unhide columns from sheet to have a try and you may also refer to below link:

https://community.powerbi.com/t5/Issues/The-input-couldn-t-be-recognized-as-a-valid-Excel-document/i...

Regards,

Daniel He

[smoss115]

You are correct. The data is not in an excel table. However, I am able to connect to the document (without making the data a table) if I edit anything in the document and save it. So, I don't think the fact that the data isn't in a table is the root cause for the error.

Also, I can connect to each sheet individually (There are three worksheets, so I deleted 2 of the 3 worksheets and saved the file with just 1 of the worksheets. I did that for each of the worksheets). So, the worksheets/data/tables themselves are not the issue. It seems that the file cannot be generated by a system and needs to have been edited by an excel user. That seems like a bug.

[commercial_user]

Did you resolve this error? What people don't understand is that there is no issue with the files you simply open it and save it and the error disappears. I am trying to automate this workflow but the excel not being recognized error is preventing that from happening.

[tengelkes]

And still this has not been resolved. 
I am indeed saving writing data to exel file from R. 
PowerBI give the error. 
I open and close the file in Excel and then PowerBI accepts.

But, I do want to have it automatically in the workflow.
PowerBI, please solve this issue.

[commercial_user]

We have resolved this error, the error is originated in the R script for output of the excel file. R WRITE_XLSX() command has to be used for all automations in R and Power BI will then read the file generated without throwing an error. 

[TerryBear]

this doesn't work for me.

Here's my code from R:

# Filter observations based on Employee Status Code
data <- data %>%
filter(data$`Code` != "X")

# Create a list with a single data frame
data_list <- list(Report = data)

# Specify the output path
output_path <- file.path(output_folder, basename(file_path))

# Write the list of data frames to an Excel file using write_xlsx
write_xlsx(data_list, output_path)
}

#5. Apply the function to each file in the list:

walk(file_list, process_file)

When I try to read in this output direct to Power Query:

An error occurred in the ‘Transform File’ query. DataFormat.Error: The input couldn't be recognized as a valid Excel document.
Details:
[Binary]