I had a question from one of my online Power BI Training students just this week who was struggling to import data from a large *.xlsb Excel file. I suggested she save the file as an XLSX file and try again – and that worked a treat. But the question is why? I had an email exchange to Miguel Llopis (from the Power Query team) and asked him why this was the case. As it turns out, the way Power Query connects to XLSB files and XLSX files is very different.
- The XLSX file import uses the OpenXML SDK to get into the actual data
- The XLSB file import (and XLS and some other non XML formats) uses the ACE OLEDB Driver (the one used to import Access data).
The bottom line is the OpenXML import approach is far more efficient than the one that uses the ACE OLEDB driver. While this may not be an issue with smaller XLSB files, it seems that it can be an issue for larger files. So if you have a choice, you could consider saving the XLSB file as an XLSX file instead. If that is not an option, you could consider writing some VBA code to save a copy when the XLSB file is closed.
What other file formats have you had problems importing into Power BI? Please post your experiences in the comments below.