File too large for excel

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • roasthawg
    SBR MVP
    • 11-09-07
    • 2990

    #1
    File too large for excel
    So I've been doing that vast majority of my calculations in excel for a long time. I keep adding more and more stuff to my db's and I'm at the point where excel can't run my calculations anymore... it shoots me an error saying "not enough resources to complete this task." Very frustrating as I feel I have the data and the correct way to use it to improve my win percentage... but I can't crunch the numbers!

    So basically I'm looking for advice on what to use... I scrape stuff to a mysql database but that's about it. I have never used Access much. I hate taking on the chore of learning something new when I'm so dialed in excel but apparently I'm going to have to. Which route should I go? What are others using that have too much data for excel? Excel is really easy to use to perform calculations in and my statistics programs run as an "add-in"... anything else out there as easy to use?
  • Data
    SBR MVP
    • 11-27-07
    • 2236

    #2
    Fine programming is the thing of the past. The current day solution to any IT problem is to "throw more hardware at it". You need at least a RAM upgrade, may be a new computer. For a reference, I have a spreadsheet that is almost 800MB in size, Excel takes up to 1.5GB of RAM to work with it but it is running just fine.
    Comment
    • RickySteve
      Restricted User
      • 01-31-06
      • 3415

      #3
      Does it still give you the error if you restart?
      Comment
      • TomG
        SBR Wise Guy
        • 10-29-07
        • 500

        #4
        I agree with Data. Max out your computer's RAM. Consider upgrading to a multi-core CPU. With the right hardware, the latest version of Excel can handle some monster spreadsheets.

        If that doesn't help, look into streamlining formulas/macros. Learning a new platform should be a last resort.
        Comment
        • uva3021
          SBR Wise Guy
          • 03-01-07
          • 537

          #5
          Stata is easy to learn and far quicker
          Comment
          • roasthawg
            SBR MVP
            • 11-09-07
            • 2990

            #6
            Originally posted by Data
            Fine programming is the thing of the past. The current day solution to any IT problem is to "throw more hardware at it". You need at least a RAM upgrade, may be a new computer. For a reference, I have a spreadsheet that is almost 800MB in size, Excel takes up to 1.5GB of RAM to work with it but it is running just fine.
            Ah, a much easier solution than learning something new! All the excel forums recommend using a database management system rather than excel for large files. I've fought that idea tooth and nail by using every trick in the "excel book" to get what I need from my excel spreadsheets. A new computer is long overdue anyways (had my xps for almost 5 years now). Besides spending money I hate making the switch to a new computer... I setup computers at work quite often and it's just a chore to do in general.

            Thanks for the responses, two votes for "upgraded computer" is enough for me as I would hate to stray from excel at this point.
            Last edited by roasthawg; 12-23-10, 01:32 AM.
            Comment
            • Wrecktangle
              SBR MVP
              • 03-01-09
              • 1524

              #7
              Upgrading the computer is easiest, next is chopping the Excel sheet into cascading sheets, i.e. transforming your problem into one that can be done in chunks with each intermediate result imported into the next sheet and so on.

              Another thing to think about is to "flatten" all your calculation into values vice calced formulas, for example for a set of long columns of calculated data sets, only have the first row with the calculations, "pound" (using a Lotus 123 term) out the formulas in rows 2 thru the rest of the sheet into values as formulas are much heavier than values. You keep the first row so you can upgrade formulas over time.

              MSOffice 2007 & 2010 uses 64bit words so your sheets wind up getting smaller also (in case you are still using MS Office 2003 or earlier). Upgrading to to Windows 7 & the 64 bit world most likely will involve a new computer also.
              Comment
              • Indecent
                SBR Wise Guy
                • 09-08-09
                • 758

                #8
                Might not be the advice you were looking for... If you are putting the data into a MySQL database as well, why couldn't you use a scripting language (php, python, etc) to handle the logic you need?
                Last edited by Indecent; 12-23-10, 11:00 AM.
                Comment
                • Emily_Haines
                  SBR Posting Legend
                  • 04-14-09
                  • 15917

                  #9
                  I have had success with just using the formulas for the last few rows so you have them for future entries. Take all the rest of the data and copy and paste back as text.
                  Comment
                  • Data
                    SBR MVP
                    • 11-27-07
                    • 2236

                    #10
                    Originally posted by Wrecktangle
                    MSOffice 2007 & 2010 uses 64bit words so your sheets wind up getting smaller also (in case you are still using MS Office 2003 or earlier).
                    Excel 2003 is perfectly capable of using 64-bit length words (values). The capability of using large 64-bit values has nothing to do with 64-bit system (processor + OS) support that provides an opportunity to work with larger (2GB+) files and more RAM. That 64-bit support was first introduced in Excel 2010, there is no 64-bit edition of Excel 2007. So, while 2007 and 2010 version share no unique property from any 64-bit related point of view, there is something that they have in common in different area. Starting with MS Office 2007, MS Office files are smaller in size due to MS Office applications using pkzip compression on the fly while saving a file. In other words, the newer MS Office files are essentially zip archives.
                    Comment
                    • roasthawg
                      SBR MVP
                      • 11-09-07
                      • 2990

                      #11
                      First thought for a new laptop... feedback appreciated.

                      Dell Inspiron 17R (had good luck with my current Dell)
                      6 gb ram
                      640 gb harddrive
                      Core tmI5 processor

                      It's only like $1000 which isn't bad at all. Is this ideal for handling huge excel sheets?
                      Comment
                      • roasthawg
                        SBR MVP
                        • 11-09-07
                        • 2990

                        #12
                        Originally posted by Indecent
                        Might not be the advice you were looking for... If you are putting the data into a MySQL database as well, why couldn't you use a scripting language (php, python, etc) to handle the logic you need?
                        My scripting skills are basically limited to scraping websites at the present time. I have excel down pat... basically don't want to learn a bunch of new stuff if I don't have to.
                        Comment
                        • roasthawg
                          SBR MVP
                          • 11-09-07
                          • 2990

                          #13
                          Originally posted by Emily_Haines
                          I have had success with just using the formulas for the last few rows so you have them for future entries. Take all the rest of the data and copy and paste back as text.
                          Won't let me do this... "not enough resources" to paste special.
                          Comment
                          • Wrecktangle
                            SBR MVP
                            • 03-01-09
                            • 1524

                            #14
                            Originally posted by Data
                            Excel 2003 is perfectly capable of using 64-bit length words (values). The capability of using large 64-bit values has nothing to do with 64-bit system (processor + OS) support that provides an opportunity to work with larger (2GB+) files and more RAM. That 64-bit support was first introduced in Excel 2010, there is no 64-bit edition of Excel 2007. So, while 2007 and 2010 version share no unique property from any 64-bit related point of view, there is something that they have in common in different area. Starting with MS Office 2007, MS Office files are smaller in size due to MS Office applications using pkzip compression on the fly while saving a file. In other words, the newer MS Office files are essentially zip archives.
                            This could be. However, when I asked my MicroSoft "expert" at work, he attributed it to operating under Vista 64. The files I had under Excel 2003 (32 bit) was more than twice as large as under Vista 64 with Excel 2007. Having seen it and verified by an "expert" I just accepted it and didn't take the additional step of verifying with MicroSoft.
                            Comment
                            • Wrecktangle
                              SBR MVP
                              • 03-01-09
                              • 1524

                              #15
                              Originally posted by roasthawg
                              Won't let me do this... "not enough resources" to paste special.
                              Sounds like you are right at the limit. Make a copy, strip out everything else but what you are flattening, flatten, save, open original, and paste from flattened file over original could work after you reboot and take care to do nothing else but this operation.

                              Otherwise, flatten by small chunks of rows (say, 100 rows at a time), and slowly work thru the entire data set.
                              Comment
                              • Data
                                SBR MVP
                                • 11-27-07
                                • 2236

                                #16
                                Originally posted by roasthawg
                                First thought for a new laptop... feedback appreciated.

                                Dell Inspiron 17R
                                Dell is a crapmaker and a bait-and-switcher. If I can ever give a good advice than perhaps the best one I can give is to always use http://www.resellerratings.com before you buy something online. Preferably, buy from vendors rated 9 or higher.

                                Originally posted by roasthawg
                                (had good luck with my current Dell)
                                Too small of a sample size, don't you think? http://www.resellerratings.com/store/Dell


                                Originally posted by roasthawg
                                Is this ideal for handling huge excel sheets?
                                While buying a computer online you have to consider three things in this order:
                                1) vendor
                                2) maker
                                3) specs

                                1) http://www.newegg.com/, always start here. http://www.resellerratings.com/store/Newegg

                                2) + HP, Samsung, Sony, Lenovo, Asus, Fujitsu
                                - Acer, Dell, Toshiba, Gateway, MSI

                                3) If you are looking for "ideal" or close you must go with i7. For the same money, a desktop will be faster than a laptop. Here is an example:

                                Here is a good albeit a slower laptop for the same money:
                                Comment
                                • roasthawg
                                  SBR MVP
                                  • 11-09-07
                                  • 2990

                                  #17
                                  Originally posted by Wrecktangle
                                  Sounds like you are right at the limit. Make a copy, strip out everything else but what you are flattening, flatten, save, open original, and paste from flattened file over original could work after you reboot and take care to do nothing else but this operation.

                                  Otherwise, flatten by small chunks of rows (say, 100 rows at a time), and slowly work thru the entire data set.
                                  100 rows at a time would take for ever with over 250,000 in the set.
                                  Comment
                                  • roasthawg
                                    SBR MVP
                                    • 11-09-07
                                    • 2990

                                    #18
                                    Originally posted by Data
                                    Dell is a crapmaker and a bait-and-switcher. If I can ever give a good advice than perhaps the best one I can give is to always use http://www.resellerratings.com before you buy something online. Preferably, buy from vendors rated 9 or higher.



                                    Too small of a sample size, don't you think? http://www.resellerratings.com/store/Dell




                                    While buying a computer online you have to consider three things in this order:
                                    1) vendor
                                    2) maker
                                    3) specs

                                    1) http://www.newegg.com/, always start here. http://www.resellerratings.com/store/Newegg

                                    2) + HP, Samsung, Sony, Lenovo, Asus, Fujitsu
                                    - Acer, Dell, Toshiba, Gateway, MSI

                                    3) If you are looking for "ideal" or close you must go with i7. For the same money, a desktop will be faster than a laptop. Here is an example:

                                    Here is a good albeit a slower laptop for the same money:
                                    http://www.newegg.com/Product/Produc...82E16834110415
                                    Invaluable information my man... many thanks!! Needs to be a laptop as I am in between work and home a bunch. Do you have a specific vendor and make in mind that would accomplish what I'm looking for? Thanks data.
                                    Comment
                                    • Data
                                      SBR MVP
                                      • 11-27-07
                                      • 2236

                                      #19
                                      Originally posted by Wrecktangle
                                      I asked my MicroSoft "expert" at work
                                      90% of IT professionals have very little clue about what they are doing. Still, by leaps and bounds, they know more than non-IT computer "experts" who, by leaps and bounds, know more than an average user.

                                      My advise to everyone, do not listen to your IT guy, those clowns are full of shit.
                                      Comment
                                      • Data
                                        SBR MVP
                                        • 11-27-07
                                        • 2236

                                        #20
                                        Originally posted by roasthawg
                                        Do you have a specific vendor and make in mind that would accomplish what I'm looking for? Thanks data.
                                        Use the link on the bottom unless you are willing to spend more. In that case, what is your budget?
                                        Comment
                                        • roasthawg
                                          SBR MVP
                                          • 11-09-07
                                          • 2990

                                          #21
                                          Originally posted by Data
                                          Use the link on the bottom unless you are willing to spend more. In that case, what is your budget?
                                          That price is actually right about what I was hoping to spend although I could go higher if the increase in performance is big.
                                          Comment
                                          • Dex17
                                            SBR High Roller
                                            • 09-06-10
                                            • 230

                                            #22
                                            Originally posted by Emily_Haines
                                            I have had success with just using the formulas for the last few rows so you have them for future entries. Take all the rest of the data and copy and paste back as text.
                                            You'd probably want to paste special as values rather than text.

                                            Many have mentioned this type of tactic - probably the easiest; I can't imagine you would need to upgrade your hardware. It seems like you don't need all of your embedded formulas for data that you're just pulling.

                                            Like someone else mentioned, just streamline your model and/or build in some macros. It's got to be built to scale!

                                            Good Luck.
                                            Comment
                                            • hubie69
                                              SBR Hall of Famer
                                              • 09-16-10
                                              • 7329

                                              #23
                                              The honest best way to do this, go get a cheap low end P4 box with 2 GB of ram, throw a command line version of linux on it along with MySQL. The Linux will use no resources at all, mysql wont really either, then you can parse that data in a variety of ways, php, shell scripting, you name it. I do this and I have it run on a P3 1.5/2GB RAM
                                              Comment
                                              • mminkovski
                                                SBR MVP
                                                • 06-22-07
                                                • 1077

                                                #24
                                                no need to buy a new PC just because your file is too big. You can save to a txt and work with any DB analysis software. There are quite a few good and free options. It would take more time to get used to it but you'll never run into resources issue
                                                Comment
                                                SBR Contests
                                                Collapse
                                                Top-Rated US Sportsbooks
                                                Collapse
                                                Working...