Lots of data
September 1, 2006 1:29 PM Subscribe
Dealing with a big Excel database.
Multiple questions:
I have a big Excel file (~100 MB).
1. My database has so many entries, I had to break the database into two worksheets. Any way to get it to accept all 70,000+ rows on one worksheet?
2. Every time I do even simple things, like insert a column, Excel recalculates all the fields (at least the ones that got moved). It takes a while. Can I tell it to stop doing that?
3. At this moment, it's doing that. Usually it takes, like 90 seconds, but it seems to have gotten hung up somewhere.
Calculating cells: 7% ---- that's where. :)
From yesterday's experience I'm guessing that if I just clean my office for 15 minutes it'll eventually unfreeze. But I'd rather tell it to stop doing what it's doing. I don't want to cntrl-alt-delete and kill the program. Is there some stop-the-current-process command? Hitting "escape" didn't help.
Sorry if these are obvious questions. Googling "stop doing that!" didn't return anything useful. :)
Multiple questions:
I have a big Excel file (~100 MB).
1. My database has so many entries, I had to break the database into two worksheets. Any way to get it to accept all 70,000+ rows on one worksheet?
2. Every time I do even simple things, like insert a column, Excel recalculates all the fields (at least the ones that got moved). It takes a while. Can I tell it to stop doing that?
3. At this moment, it's doing that. Usually it takes, like 90 seconds, but it seems to have gotten hung up somewhere.
Calculating cells: 7% ---- that's where. :)
From yesterday's experience I'm guessing that if I just clean my office for 15 minutes it'll eventually unfreeze. But I'd rather tell it to stop doing what it's doing. I don't want to cntrl-alt-delete and kill the program. Is there some stop-the-current-process command? Hitting "escape" didn't help.
Sorry if these are obvious questions. Googling "stop doing that!" didn't return anything useful. :)
How about I provide an answer for 'stop doing that'?
You seriously need to look into having someone put that information into a database. There's plenty of easy packages; avoid Access, but I do like FileMakerPro.
posted by SpecialK at 1:35 PM on September 1, 2006
You seriously need to look into having someone put that information into a database. There's plenty of easy packages; avoid Access, but I do like FileMakerPro.
posted by SpecialK at 1:35 PM on September 1, 2006
Just a quick answer to #1 - Excel has a limit of 65,536 rows...
posted by chrisubus at 1:37 PM on September 1, 2006
posted by chrisubus at 1:37 PM on September 1, 2006
1.) The Excel Row Limit is 65,536. Heard that this limit will be extended in the next version of Excel.
2.) Tools | Options | Calculation | Manual
Then use F9 to recalc the monster.
3.) No help here.
Let me be one of many who will tell you that Excel is probably the wrong tool for this job and/or your computer is underpowered for this task.
posted by mmascolino at 1:37 PM on September 1, 2006
2.) Tools | Options | Calculation | Manual
Then use F9 to recalc the monster.
3.) No help here.
Let me be one of many who will tell you that Excel is probably the wrong tool for this job and/or your computer is underpowered for this task.
posted by mmascolino at 1:37 PM on September 1, 2006
1. Microsoft Excel 2003 has a hard limit of 65536 rows and 128 columns in one worksheet. I vaguely recall hearing somewhere that this was going to be increased in the next version (Microsoft 2007).
posted by DevilsAdvocate at 1:38 PM on September 1, 2006
posted by DevilsAdvocate at 1:38 PM on September 1, 2006
I've had similar things happen with huge spreadsheets. A few thoughts:
Often times, a lot of data in a spreadsheet doesn't need the constant recalc - especially time series stuff. You might be able to cut and paste special | values to replace those cells with permanent values that speeds up the recalc. I used to update monthly data live, then paste it down at the end of a month. Archival purposes.
I don't know how you would deal with 70k rows, but you could have some kind of parallel structure where, say, columns DEF are the same as columns ABC. Even if you have to move a date by formula, you could get all the stuff on one sheet. Or you could live with two worksheets and create a third that summarises. If you really can't get around this, then maybe you need a more dedicated database structure. I always try to see if Excel can cope in the first instance.
I think when excel goes off to la-la land it usually requires a ctrl-alt-del to get rid of it. Sometimes if it looks locked up and you wait 10 mins (or 15, or whatever your autosave requires) you find you haven't lost your work, although I find it difficult to be confident in this and often save whatever I'm doing as a new version until I am sure there's been no loss.
posted by sagwalla at 1:40 PM on September 1, 2006
Often times, a lot of data in a spreadsheet doesn't need the constant recalc - especially time series stuff. You might be able to cut and paste special | values to replace those cells with permanent values that speeds up the recalc. I used to update monthly data live, then paste it down at the end of a month. Archival purposes.
I don't know how you would deal with 70k rows, but you could have some kind of parallel structure where, say, columns DEF are the same as columns ABC. Even if you have to move a date by formula, you could get all the stuff on one sheet. Or you could live with two worksheets and create a third that summarises. If you really can't get around this, then maybe you need a more dedicated database structure. I always try to see if Excel can cope in the first instance.
I think when excel goes off to la-la land it usually requires a ctrl-alt-del to get rid of it. Sometimes if it looks locked up and you wait 10 mins (or 15, or whatever your autosave requires) you find you haven't lost your work, although I find it difficult to be confident in this and often save whatever I'm doing as a new version until I am sure there's been no loss.
posted by sagwalla at 1:40 PM on September 1, 2006
Sorry, should be 256 columns.
posted by DevilsAdvocate at 1:41 PM on September 1, 2006
posted by DevilsAdvocate at 1:41 PM on September 1, 2006
Best answer: In the short term, your first step should be following langedon's suggestion to turn off automatic calculations.
In the long-term, I nth the recommendation of getting it into a database post haste. I hate Access as much as the next guy, but for only 70,000 records, it should do you just fine, and it'll be pretty easy to import it straight from your existing .xls file.
If you absolutely have to keep it in a spreadsheet, Excel 2007 (which is still available as a free beta download from Microsoft) can handle 1,048,576 rows worth of data.
If you decide to use Excel 2007, you'll probably still want to disable automatic recalculation (since it's an application setting, not a per-workbook setting). You do that in Excel '07 by clicking the Windows logo (I guess that's what it is in the upper-left corner), then "Excel Options," then "Formulas," and select "Manually" under Calculation options.
posted by Doofus Magoo at 1:53 PM on September 1, 2006
In the long-term, I nth the recommendation of getting it into a database post haste. I hate Access as much as the next guy, but for only 70,000 records, it should do you just fine, and it'll be pretty easy to import it straight from your existing .xls file.
If you absolutely have to keep it in a spreadsheet, Excel 2007 (which is still available as a free beta download from Microsoft) can handle 1,048,576 rows worth of data.
If you decide to use Excel 2007, you'll probably still want to disable automatic recalculation (since it's an application setting, not a per-workbook setting). You do that in Excel '07 by clicking the Windows logo (I guess that's what it is in the upper-left corner), then "Excel Options," then "Formulas," and select "Manually" under Calculation options.
posted by Doofus Magoo at 1:53 PM on September 1, 2006
Please don't use Excel databases this big. There was a WTF on thedailywtf.com somewhere along these lines.. you need a real database. Trust me, you'll be better off. Personally, I'd recommend you get someone who's familiar with postgresql, & get them to set you up with a couple of scripts to manage what you need. pgsql can handle that load no problem, do it safely & redundantly, & a hell of a lot faster than Excel can. Excel is not a database, it's a spreadsheet.
posted by devilsbrigade at 1:56 PM on September 1, 2006
posted by devilsbrigade at 1:56 PM on September 1, 2006
Best answer: The bad news is that you need a database solution. This is bad news because it means you'll have to spend some time learning how to use it, and how it differs from a spreadsheet application. I also strongly recommend getting an intro to database design book. The better you plan your transition to a DB application, the eaisier your job will be later.
The good news is that once you learn how to use a DB, working with your data will become way easier than it is now. Databases were invented to deal with huge data-sets, so they search, sort, retrieve, and even do calculations faster than Excel in almost all cases.
posted by elderling at 2:11 PM on September 1, 2006
The good news is that once you learn how to use a DB, working with your data will become way easier than it is now. Databases were invented to deal with huge data-sets, so they search, sort, retrieve, and even do calculations faster than Excel in almost all cases.
posted by elderling at 2:11 PM on September 1, 2006
Response by poster: Thanks all. It unfroze, and I turned on manual calculation. Now, it's set up like sagwalla suggests -- two worksheets with a third that summarizes. Plus, two more with lookup tables (yeah, it should be in a real database program. I thought I could sprint to the end of these calculations without learning a new program). We have Access and Filemaker Pro, so I'll see what I can do. Sounds like Filemaker is the better one to learn?
posted by salvia at 2:19 PM on September 1, 2006
posted by salvia at 2:19 PM on September 1, 2006
Best answer: PS. To forcefully unfreeze during calcs, CTRL-BREAK might work.
posted by inigo2 at 3:06 PM on September 1, 2006
posted by inigo2 at 3:06 PM on September 1, 2006
Response by poster: inigo2: It did.
My new method: whenever Excel is frozen, work to get the tables into Filemaker. Thanks for your help, everyone.
posted by salvia at 4:39 PM on September 1, 2006
My new method: whenever Excel is frozen, work to get the tables into Filemaker. Thanks for your help, everyone.
posted by salvia at 4:39 PM on September 1, 2006
This thread is closed to new comments.
posted by langedon at 1:31 PM on September 1, 2006