My friend and colleague, John Miller, taught me a nice trick the other day.
My compression software of choice is 7-Zip. As you know, modern Excel files are nothing more the zipped XML packages. If you change the extension of your file from xlsx to zip, you can unpack the xml using any compression software that supports zip (which is probably all of them).
7-Zip has a menu item that I’ve never used. It’s the first on the list and it’s called
When you use
Open archive, 7-Zip doesn’t care what the file extension is. It looks at the file contents, determines how it was compressed, and displays its contents.
From here you can navigate to
xl/worksheets, for example, to see all the sheet XML files and their sizes.
That’s much easier that changing file extensions. Of course you can use this for inspecting anything in the file, not just worksheets.
We’ve opened registration for our third annual
May 26, 2016
Join us in Amsterdam to learn how to Excel from our Experts (all MVPs):
Jon Peltier, Bob Umlas, Roger Govier, Henk Vlootman, Oz du Soleil, Tony de Jonker, Jan Karel Pieterse.
Attend this comprehensive training event and you will:
- Improve your Power Query skills
- Learn how to Customize the ribbon for your workbooks and add-ins.
- Get insight how to Build Excel models based on ranges and positions.
- Understand how to create involved Array Formulas
- Get advice on Best practices in Power pivot.
- See how to use VBA to customize charts
- Receive Tips & Tricks, documentation and lots of valuable files
The Excel Charting And Dashboard Masterclass
May 27th 2016
The Amsterdam Excel summit also features a post-conference training. Attend this one-day masterclass and:
Excel MVP and charting Guru Jon Peltier teaches you how to visualize your numerical information in the most effective way.
Excel MVP and financial expert Tony de Jonker and communication & visualization expert David Hoppe unveil the secrets of creating powerful and flexible dashboards.
So head over to our website to register or to signup to our mailing list so we can keep you posted!
Jan Karel Pieterse
Don’t call it a streak, but it’s been almost five days since MySQL crashed. I made a change late last week that masks the problem appropriately. I still don’t know what causes the problem, and probably never will, but as long as the server stays up, I’m in a better place. I’ve been fighting this for over a year.
I run DDoE on a 1GB virtual server at Digital Ocean. The heart of the problem is that a memory usage spike causes the Ubuntu kernel to shut down the MySQL service and it never gets started again. Memory management is one of the things a kernel does, so it’s not strange that services get kicked out of RAM when there’s a spike. MySQL will try to restart itself, but it’s not able to because it doesn’t have enough memory. I don’t know what’s causing the memory spike. I do know that the only services of consequence that are running are MySQL and a bunch of Apache2 services. Apache2 is the web server that directs the traffic.
I don’t recall where I read it, but someone on the internet said that it’s likely Apache2 is spawning too many processes. I don’t know how to verify that. I followed the advice on ServerFault on how to tune Apache on Ubuntu. When I shut down Apache, I could see that I was using about 660MB of memory, leaving 340MB free. I was surprised how much memory it was using without Apache, which I assumed would be the biggest component. Using the
command, I could see that an Apache2 process was using 30MB on average. That means if I limit my Apache2 processes to 10, I should not run out of memory. I changed the MaxRequestWorkers parameter from 150 to 10, and so far it’s worked.
There must be a cost to that change. I imagine that when the memory spikes, for whatever reason, and Apache isn’t allowed to spawn more processes, that the user gets an error and is unable to reach the site. As bad as that is, it’s better than what was happening – MySQL would shut down and nobody could reach the site until I got up the next morning and rebooted.
Now that we’re seemingly stable, maybe I’ll make some posts. I’ve been working in SQL Server a lot lately and have a lot to say about it.
Now that summer has arrived I’ve planned a summer sale. From July 1st up to July 10th I offer a 25% discount on both products I sell:
The Excel File Remediation Utility
To get your discount, just go through the purchasing process and enter this coupon code to redeem your discount:
Regards and have a great summer!
Jan Karel Pieterse
Our event is coming real soon now and we’re very much looking forward to it. We have outstanding speakers and excellent content, so everything is lined up to make this a superb Excel event.
To entice the undecisive Excel lovers to make up their minds and subscribe after all, we decided to make it even more attractive to attend.
As of March 27st, 2015 we offer a € 200 last-minute discount per attendee for both days and € 100 for one day!
Register now at http://topexcelclass.com/index.php/amsterdam-excel-summit/registration/ and meet us on April 13th and 14th in Amsterdam.
Jan Karel Pieterse
When Doug posted about Data Comparison Tricks, I saw Dick tell him to “pimp his site” in the comments.
Having a vivid imagination, this is what went through my head.
Anyway, that’s what I’ll now proceed to do. (Having obtained Dick’s permission first of course!)
Here’s some stuff I’ve been working on recently.
A multi-field Find and Select/Replace tool.
Although a bit old, (like me), some of the code came from this.
Here’s the old post about it from back in 2009. (From my former blog, which I’ll also pimp!)
An alternative Status Bar that recognizes numbers even if the format is text. Woohoo!
And some games. (For the kids, but you can play too)
That’s enough pimpin’ for now. (I’m making new stuff as I write this) See you next time?
I “pre registered” even though there’s not a lot of information on the site. For example, I could not find any information about the organizers, nor could I find any motivational details other than “The prize pool…will include cash, interviews with leading global Financial Institutions, product giveaways and more.” Neither is there any information about how the finalists will participate in the “Live Judged Event in New York.”
Of course, the competition, by necessity, will be measure ability / skill along whatever dimension(s) the organizers value. Nonetheless, it might be fun and an opportunity to see what others do in such an event.