Maximum filesize?

[Jen]

Hello There,

I am using Essbase to retrieve data from our DB... etc etc. It works as an
Excel-add-in and dumps my data on my workbook.

The dumpdownload I store in 6 different Ranged Names on 6 different
sheets... each 15 columns wide, and 35000 rows deep.
This results in a 45MB-size file!

I use MSQuery to "join" my data from this file and upload it into a Pivot
table into another file. (Data>Pivot>Get External Data).
As such I do not have any problem with the size of this file ... as I do not
have to work with this file actively. It stays closed and get accessed with
another file via MSQuery.

I don't see many ways to reduce the filesize of the data-file. I´ve re-set
the last cell on every sheet too. When I take a blank Excel file and file
the same ranges with a number, I get the same file-size.
So far this is working without any significant problems... (*) but I am a
bit worried that I will run out of luck rather sooner than later.
(I see postings from people who get worried already with filesizes of 2 MB
....?)

My File with PivotTable has just 1 Pivottable, no charts, no other
calculations and is approx. 18 MB!
BUT it works fine I do not have any problems so far. The PivotCache
MemoryUsed is approx 7.5MB ..i've no idea what that means exactly....
The files are newly build (no old cluttering whatever), contain no
VBA-code,...

I just would like to know what file-sizes Excel can handle?
Will the big Datafile be a problem at a certain level? (by preference I
would like to double the amount of data in it for more comparative analysis
but I am a bit scared)
...it is a passive file. Or the PT-file?
Are there ways I could upload my data from Excel into Access eg.? ... Other
solutions?

(*) Ron Coderre has an Add-in PivotPlay (super!, thanks Ron) that allows you
to automatically rewrite the queries ... but this functionality does not
work anymore, which I think is simply due to the filesize. It works great
with smaller Files.

Jen

 

[JLatham]

Jen,
I can't answer your questions with hard numbers. In theory the maximum file
size should be based on what your operating system can handle and store and
retrieve. In reality it is probably going to be smaller than that. A lot
regarding responsiveness and the ability to deal with all of the data will be
governed by the power of your CPU, the memory installed in your computer and
how much free space is available on your hard drive.

It is possible that the apparent loss of functionality by PivotPlay may be
due to memory constraints and to a lesser degree by CPU speed: it may just be
taking a lot of time to rearrange the data and find places in memory/on the
hard drive to store and retrieve it.

Two things I'd recommend in light of your concerns:
#1 make regular backups of all (both?) files concerned. And you have to
exercise a little caution even in doing that - before backing the files up
after working with them and closing them, open them back up and use Save As
to save the backup copy. This helps assure you that your backup is of a
working version. If you cross some limit somewhere and suddenly you can't
access the file, if your backup was based on that file it would be
conceivable that the backup was similarly inaccessable.
#2 (after backing up these files to another location, hard drive, CD, etc)
defragment the hard drive you work from. Getting these large files into a
more orderly condition will help with data retrieval, especially from the
45MB file if it is now fragmented.

For better performance (not something you mentioned, but a consideration
anyway) you might consider increasing the amount of physical RAM in the
system. With files that large I'd consider 2 or 3 GB of RAM installed to be
helpful.

As for moving it all into Access, with the amount of data you have (6 * 15 *
35000 = 3,150,000 individual data items) it is definitely something to give
consideration to. The maximum file size in recent versions of Access is 2GB
which is also the limit on table size - but you can build even larger
databases by placing tables into separate files and linking it all together.
But this shouldn't be a factor in your case: 45 or even 145 MB is a small
fraction of 2GB.

I wish I could provide more concrete information for you, but I simply
cannot. The Excel Help topic "excel specifications and limits" does not
mention a maximum file size, although it goes into detail on other limits (#
of sheets, pivot tables, etc), and many of those are "limited by available
system memory". You may want to read through that topic on your system to
see if you think you're getting close to any of the limits it mentions.

I will repeat one thing: it sounds like this file is very important to you.
The amount of data you have alone probably represents a considerable
investment of time and effort. With that in mind, I repeat my recommendation
to develop and adhere to a good backup strategy. To fail at the most
inopportune moment is typical of a computer - to have a recent backup of
critical files at those inopportune moments is simply divine!

 

[Jen]

Hi JLatham,

I found some information on Charles Williams's site concerning Memory Limits
....
http://www.decisionmodels.com/memlimitsc.htm
It is hard though to get a clear picture on what the limitations are. At
least I am not technical enough to properly interpret what Charles is
saying.
Quote>
Excel 2003 is officially limited to 1 Gigabyte (GB) of formula memory for
workbooks (heap space).
Excel 2003 has a substantially increased memory capacity I have successfully
used over 750MB of workbook memory on a 768MB RAM Windows XP system.
<Unquote

The discusion does not really turn around the file size but about
availability of memory... as the main constraint.
I am working with 1GB RAM and Excel2003, so according to the "theory" I can
work with files of 1GB ..or say 0,5GB to be on the safe side?

As such it is not difficult to create my datafile... it is just a
dumpdownload. But I do intend to put some extensive work on files accessing
the big one!
Hmm, still a bit sceptic.

Jen

 

[ShaneDevenshire]

Hi,

The file size limit for Excel 2003 and lower is 1GB. That has changed in
Excel 2007 but Microsoft has given various number: I have seen 2G and
"Limited by available memory. In Windows, (not 64 byte) systems the
available memory is 3 GB.

As for pivot tables - it is restricted by the same limits, but remember that
the file size, when saved is affected by whether you choose to Save Data with
Layout, a pivot table option. The pivot table cache is part of the Excel
file.

I have worked with Excel files containing very large cache's - which made
the file size 300MB. This was not a problem, although some things may run
more slowly.

 

[Jen]

Hi Shane,
Thank you for sharing your experience... makes me feel more comfortable!

I have been reading up in the meantime on how to export Excel-sheets to
Access, just in case
http://office.microsoft.com/en-us/access/HP010950951033.aspx?pid=CH063648351033
It is rather straightforward at least via the "Use a Macro"-procedure ...I
just fear that it is just a pain to update the paths and Named Ranges
manually...It does not have an Error-handling. My guess is that updating
such a database via a VBA-procedure is better manageable.
Just would love to see a simple example of how someone does it via VBA. I'll
better start a post about that topic in NG excel.programming.

Jen

 

[JLatham]

If you haven't already posted the question in Excel Programming, I can
probably help you if you want. You are correct that using VBA is more
manageable in the long run - the constants and variables are more visible to
you and so can be altered more easily.

You'd need to make some decisions as to either what method needs to be used
to transfer which data in which direction (from excel to access/access to
excel). It may even be necessary for someone to see what you're doing in the
'active' workbook to generate your pivot table.

If you seek help elsewhere first, that's fine - if you get hung up or don't
get help you need, keep me in mind as a possible resource. You can reach me
via emal at (remove spaces) HelpFrom @ jlathamsite.com