The mystery of spreadsheet sizes

Thanks to Colorado’s Library Research Service, I found out on Wednesday (June 26) that the IMLS released the 2011 public library datafiles a little early. (I was expecting them some time in July, so “a little” is the operative word. Still: early is better.)

So I went to check the site, figuring I’d do my usual: Download the .zip file containing the .mdb (Access) databases, extract the database consisting of library data (as opposed to outlet data or state summaries), download the PDF documentation, then open the .mdb database in Excel, convert the whole thing to a table, and save it as an Excel spreadsheet for later use (assuming I do the Give Us a Dollar… project). The Excel spreadsheet would probably start at around 8MB, but once I peeled off the columns I actually care about, it would get a lot smaller.

Surprise!

First, the surprise: The data is no longer available in your choice of .mdb or flat (.txt) files, the latter requiring a form of string processing I’m not sure how I’d do.

Nope. Now it comes in SAS form (only for 2010 and 2011), .txt, and two other forms: .csv (comma-separated values, which Excel’s only too happy to open) and .xsl (Excel native form).

That’s not only true for the 2011 tables, IMLS has gone back and replaced earlier .mdb databases with .csv and .xls files.

Well, that’s one step I won’t have to include in the how-to chapter of “Mostly Numbers” if I do that particular project–namely, how to open the .mdb database and convert it to an Excel spreadsheet.

Oh, but look: The Excel spreadsheet is more than 21MB, about three times as large as I’d expect.

Mystery?

That was surprising enough that I went back and downloaded the .csv files. The .csv library file is about 7MB. When I open it in Excel, it looks precisely like the Excel spreadsheet (as it should, unless there are formulas hidden in the Excel version)…and when saved in Excel form, it’s about 7MB.

In other words, just about the size I would have expected.

What’s going on here? Unless somebody from IMLS reads this and sends me a note, I’ll either figure it out later or not. As things stand, I’m more likely to work with the .csv-to-Excel form (although I suspect that both would wind up shrinking to about the same size for the 15-20 columns I actually need out of the scores of columns that are there now).

This could be one of those Office mysteries, where if I delete and restore one cell in the 21MB Excel spreadsheet it suddenly turns into a 7MB spreadsheet. Or not.

Meanwhile, it’s just one of those mysteries.


Memes

One little note here on an entirely different topic. I’m generally not much of one for following memes, such as the idea of blogging every day in June. That’s a lot of blogging for an occasional blogger like me. I’d feel silly signing up for it and then not doing it (or having odd “post 23″ titles on daily posts).

Whereas not signing up for it, and then (accidentally?) doing it? That’s just fine.

[No, I won’t accidentally write a 50,000 word novel in November or any other month. I lack the personal observation skills to be a good fiction writer. Could I produce 50,000 words of decent second-draft material in a month? Yeah, I think so…but November’s not likely to be one of those months.

 

Comments are closed.


This blog is protected by dr Dave\\\\\\\'s Spam Karma 2: 104743 Spams eaten and counting...