Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maximum filesize?
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. (DataPivotGet 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maximum filesize?
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" wrote: 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. (DataPivotGet 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maximum filesize?
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 "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... 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" wrote: 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. (DataPivotGet 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maximum filesize?
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. -- Cheers, Shane Devenshire "Jen" wrote: 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. (DataPivotGet 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maximum filesize?
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/ac...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 "ShaneDevenshire" wrote in message ... 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. -- Cheers, Shane Devenshire "Jen" wrote: 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. (DataPivotGet 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Maximum filesize?
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 "Jen" wrote: 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/ac...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 "ShaneDevenshire" wrote in message ... 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. -- Cheers, Shane Devenshire "Jen" wrote: 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. (DataPivotGet 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reducing Filesize of Workbooks | Excel Discussion (Misc queries) | |||
Can anybody reproduce my EXPLODING filesize workbooks? 100kb - 30 | Excel Discussion (Misc queries) | |||
filesize | Excel Discussion (Misc queries) | |||
Maximum If Help | Excel Worksheet Functions | |||
Minimize the filesize | Excel Discussion (Misc queries) |