To Database, or not...

[Bill Martin]

To database or not to database, that is the question...

I'm running Excel 2003 under XP. I have several Excel workbooks that
I've built up over time. They basically store sheets of data and have
various programs I've written in VB or DLL files that analyze and/or
plot data. Sometimes one Excel program opens several of these other
workbooks to access their data too. I'm the only user of this system.

All is well and they work as desired. The thing is that some are
getting rather large. The biggest one is nearly 100MB (and yes, I've
crunched the bloat out of it which Excel can create). It's basically
one sheet for each date, and each sheet is maybe 25 columns by 1800
rows. There are about 200 sheets at present and adding another each
week for just this one workbook. My only complaint is that it can
take time to open or save the large files. Otherwise they seem to
work ok.

My question is whether I'm tempting fate with such large Excel files.
Will Excel become cranky and unreliable at some point, or will it just
get slower as the files get bigger?

I don't feel it's worth recasting everything into a database
repository just for speed (which I could also improve with better
hardware), but I worry a bit about Excel reliability.

Thanks.

Bill

 

[Kenneth Hobson]

Yes, I would be very concerned. I highly recommend that you make regular
backups of the file.

It is much better to create a workbook for each sheet and then import what
you need into a master xls if you want to keep it all in Excel.

If you can find a way to insert the data into Access, I think your life will
be alot eaiser. So, yes, it appears that it is database time.

 

[Bill Martin]

Yes, I would be very concerned. I highly recommend that you make regular
backups of the file.

It is much better to create a workbook for each sheet and then import what
you need into a master xls if you want to keep it all in Excel.

If you can find a way to insert the data into Access, I think your life will
be alot eaiser. So, yes, it appears that it is database time.

----------------------

Concerned why Ken? Is Excel known to get unreliable? That's kind of
the crux of the question.

As for backups, I'm admittedly a bit paranoid on that subject and do
keep weekly backups of data files. (I worked in the data storage
industry) Multiple levels of backups and stored in multiple locations
in case the building burns down or something, as well as the
inevitability of disk failure.

Bill

 

[rumkus]

It's basically one sheet for each date

But what was the reason for "one sheet for each date" ?
I mean why didn't you add another column for the date in the first
place ?
rgds

 

[Bill Martin]

But what was the reason for "one sheet for each date" ?
I mean why didn't you add another column for the date in the first
place ?
rgds

---------------------------------------

Well, it's adding another 25 columns for each date - not one. And one
date per sheet makes it easier to find the data since the date is the
first part of the name.

Each sheet is a snapshot of data for that date - 45,000 cells worth.

One can stuff in 255 columns of data per sheet I guess, but does that
make any less data to store? It seems that adding sheets is a very
low cost process, but perhaps I'm wrong.

Bill

 

[Chip Pearson]

I would start to be concerned with an Excel file that was pushing 100
MB. If you do nothing else, back it up frequently. Excel isn't a
database (although many people have tried to make it so), so you might
find that complex tasks in Excel are much simpler in a real database
like Access or, better, SQL Server. However, Excel is a great
calculation engine, so if the work done by your Excel application is
more computational in nature than data retrieval, you may want to
stick with Excel. Of course, with some VB6 or NET programming, you can
create a hybrid system in which the raw data is stored in and queried
from a database, and is fed into Excel for the heavy lift
calculations. Whether this is, in practice, a viable solution depends
on several factors, including but not limited to, what the application
actually does, the available developers, their skill sets, and the
dollars in the budget.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Bill Martin]

Some of the workbooks are pretty much just data stores for the moment.
Others however do heavy duty optimization calculations which may run
for rather extended periods. Those are the ones where I've written
DLL files that do the real work. The medium effort ones are in VBA.

The "developers" are me. I'll play with MySQL a bit I guess and see
how easily the data moves back and foth.

Thanks.

Bill
---------------

 

[rumkus]

Well, it's adding another 25 columns for each date - not one.

You've left me French here Bill.
Maybe your data structure is different than I guess.
If you have to gather your data under a spesific date why you should
repeat it 25 times on a single record i don't understand.
rgds

 

[Bill Martin]

You've left me French here Bill.
Maybe your data structure is different than I guess.
If you have to gather your data under a spesific date why you should
repeat it 25 times on a single record i don't understand.
rgds

-------------------------

My data structure is something like 45,000 data points for each date -
1800 rows by 25 columns. All independent - nothing repeated.

Bill