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

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.

On Sat, 3 Jan 2009 11:42:35 -0600, "Kenneth Hobson"
<(E-Mail Removed)> wrote:

>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

> 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

On Sat, 3 Jan 2009 10:59:36 -0800 (PST), "(E-Mail Removed)"
<(E-Mail Removed)> wrote:

>> 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
---------------------------------------

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

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)


On Sat, 03 Jan 2009 12:23:36 -0500, Bill Martin
<(E-Mail Removed)> wrote:

>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

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
---------------
On Sat, 03 Jan 2009 16:15:25 -0600, Chip Pearson <(E-Mail Removed)>
wrote:

>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)
>
>
>On Sat, 03 Jan 2009 12:23:36 -0500, Bill Martin
><(E-Mail Removed)> wrote:
>
>>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

> 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

On Sun, 4 Jan 2009 02:59:29 -0800 (PST), "(E-Mail Removed)"
<(E-Mail Removed)> wrote:

>> 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
-------------------------

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

Bill