Can't get data from .xlsx until file is opened and saved

[AntonioM]

Hi all,

I'm trying to work with an xlsx file that our training team export from our training system. The file when it is first saved is larger than expected, but after it's opened and saved (making no changes) the file size drops dramatically. I've read this might be excel not having compressed the content in the initial export, but does once it's saved directly.

Power BI won't connect to the initial xlsx file, instead giving me the error 'The input couldn't be recognised as a valid Excel document'.

Once the file is opened and saved, everything works fine.

Is there any way I can access the file in its initial state, maybe using a different connector (text/csv didn't work)? It's not the end of the world if we can't, but it would be nice if we could remove a step from the refresh process, even if it takes slightly longer to read (which I'm guessing it might).

[lbendlin · Accepted solution]

Most likely whoever is creating the original Excel file is indeed cheating and the data inside is not conforming to the xlsx format.  Worst case they produced an xls file and just slapped the xlsx extension onto it.  Use hex editor on the original file (before you re-save it) to validate.  If it turns out to be a CSV file with an XLSX extension then you can read it as CSV.  If it is an XLS then you may need to use a separate process to open and resave.

[AntonioM]

Yeah, I think you're right, the same thing happens with all the other reports I've tried from this system.

CSV doesn't work unfortunately, so I think we'll have to open and resave for now. 

Thanks!