0

I've build some power pivots that are appending data from couple binary excel files (they have more than 600k rows each).

However I have noticed, that query/pivots that are build on *.xlsb files have terrible performance. The data is refresing 5-10x slower than when collected from the same files but with *.xlsx format.

Currently I'm manually opening these *.xlsb files and saving copy to *.xlsx - query then connects to these *.xlsx files.

I would like to add a macro to my query/pivot report file, that will automatically save copies of the *.xlsb files to save them as *.xlsx, without opening these files prior to saving and after saving.

Is it possible?

Vincent
  • 417
  • 3
  • 12
Drcs
  • 13
  • 1
  • 5
  • You can't extract data from a file without 'opening' it (reading it). However, there are ways to batch convert a bunch of files from one format to another. Google is your friend. However, someone has already asked a very similar question that I think would work for your scenario anyway: https://stackoverflow.com/questions/29167539 – CLR Apr 07 '20 at 07:36
  • It is not possible. The files cannot be converted in Excel without opening them. Therefore you would need to write a code that loops through your files opens them and saves them in the new format. • Actually I wonder about what you say about the performance. Xlsb files are known to be smaller and more stable than xslx files (especially when it comes to big data). – Pᴇʜ Apr 07 '20 at 08:13
  • They are indeed are smaller and works faster, however the queries works terribly with *.xlsb files, and I have tested quite a lot of them recently – Drcs Apr 07 '20 at 08:33
  • Well, you might be right I cannot test it. I have never heared about that and it sounds very odd to me that they should be so much slower. Before converting everything I would make sure that you use the latest Excel version (just to be sure it is not a bug) and or test it on other computers or even other Excel Versions. – Pᴇʜ Apr 07 '20 at 08:39
  • This boils down to the number of times you're going to run a query against each of these spreadsheets. In order to convert the file, you need to open and save-as the file as your new format. Compare the time it will take to do that once, against the amount of **extra** time if takes to run the query multiplied by the number of times you'll do it. – CLR Apr 07 '20 at 10:25
  • If converting each file works out to be the most time effective way of doing it, then my first comment points you to a script that batch converts files. – CLR Apr 07 '20 at 10:26
  • Maybe you'd want to simplify the data because that's a lot of rows. – Patrick Lepelletier Apr 07 '20 at 11:30

0 Answers0