- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
10gb+ excel files
Posted on 11/21/22 at 9:00 am
Posted on 11/21/22 at 9:00 am
Quick question, I am running a large medical billing look back trying to find a correlation on a macro scale. File is saved on my desktop until I finish and upload to our server. Current size is 14gb, about 60k rows, 15-18 columns wide depending on my final clean but, my issue is, every time I resize a column program comes to screeching halt to process. Just taking unnecessary time, anyone have recommendations? I can't break the file into pieces because it would take 3-4x as long to clean every file to same format. It's an archaic database so I don't have a workaround until we finally finish our migration to the cloud based system.
Of note:
File: .xlsx
Processor: i7-8700 CPU @ 3.20GHz
RAM: 32gb
Of note:
File: .xlsx
Processor: i7-8700 CPU @ 3.20GHz
RAM: 32gb
4
Posted on 11/21/22 at 9:19 am to Rendevoustavern
Do any of the cells have formulas in them? How many times are you resizing the columns? Seems like an odd thing to do frequently.
Excel isn't very friendly once you get that much data. I'm surprised it opens with 10g of data.
Are you able to throw the data into a database like SQL or Access?
Excel isn't very friendly once you get that much data. I'm surprised it opens with 10g of data.
Are you able to throw the data into a database like SQL or Access?
Posted on 11/21/22 at 9:36 am to Rendevoustavern
quote:
File: .xlsx
Save as a .xlsb file.
Posted on 11/21/22 at 9:39 am to Rendevoustavern
quote:
Current size is 14gb, about 60k rows, 15-18 columns wide
This doesn't seem to add up. Can you copy the data and paste values only into a blank Excel document and work off that?
Posted on 11/21/22 at 9:50 am to WhiskeyThrottle
No formulas in the file. It's a raw flat data file.
Thankfully it's one time process because we have kept track of this metric but I need to provide consolidated data to consultants building out cloud service.
Merging 12 files was far more difficult because folks put hands on them and adjust a little here and there, so no two files are the same making a simple merge of those files not possible.
Resizing was just an example. The data layout is the problem. 3 pieces of patient data in one column for the same patient. Row 1 might be DOB, row 2 might be service date row 3 might be name. I have to move all three of these rows into one and then consolidate. Current program is a trash database so dropping into sql or access just moves my problem from one program to another because the data isn't in any formal array.
Almost finished with it after crashing excel a few times.
Thankfully it's one time process because we have kept track of this metric but I need to provide consolidated data to consultants building out cloud service.
Merging 12 files was far more difficult because folks put hands on them and adjust a little here and there, so no two files are the same making a simple merge of those files not possible.
Resizing was just an example. The data layout is the problem. 3 pieces of patient data in one column for the same patient. Row 1 might be DOB, row 2 might be service date row 3 might be name. I have to move all three of these rows into one and then consolidate. Current program is a trash database so dropping into sql or access just moves my problem from one program to another because the data isn't in any formal array.
Almost finished with it after crashing excel a few times.
Posted on 11/21/22 at 10:25 am to Rendevoustavern
quote:Like philabuck said, something's not adding up. From the info you've given that works out to about 12k per cell give or take minus excel overhead. Like a 2000-word essay in each and every cell, do you think that's right? If so you've got quite a mess.
No formulas in the file. It's a raw flat data file.
Posted on 11/21/22 at 5:53 pm to Rendevoustavern
quote:
Current size is 14gb
I'm not an Excel geek, but for f!*% sake, that should probably be an actual database, not an Excel file.
The other commenters are heads and shoulders above my expertise in Excel, but couldn't you just export it to SQL Express as a CSV to make your spreadsheet an actual database?
Posted on 11/21/22 at 9:44 pm to Rendevoustavern
I have worked with way bigger datasets in Excel (bumping against 1M rows by 20 columns), and I haven't even touched 1GB file sizes. Been a few years, but I seem to recall them landing in the 300MB range.
So something isn't right in that file. I'd advocate saving your data tabs as individual CSVs and reimporting, That should strip out any weird formulas, formatting, etc that may be blowing up the file size - and reduce it down to just data.
So something isn't right in that file. I'd advocate saving your data tabs as individual CSVs and reimporting, That should strip out any weird formulas, formatting, etc that may be blowing up the file size - and reduce it down to just data.
Posted on 11/22/22 at 8:18 am to blackoutdore
I am thinking he must mean 14MB rather than 14GB.
Posted on 11/22/22 at 9:09 am to chryso
Definitely not adding up. I have had 1.3 million records be about 300mb.
Posted on 11/22/22 at 11:24 am to bayouvette
Is OP ever going to come back
Posted on 11/22/22 at 11:55 am to castorinho
quote:
Is OP ever going to come back
Got fired from twitter for storing their content database in excel.
Posted on 11/22/22 at 2:46 pm to bayouvette
Made a mistake on the file size, was actually 14mb and, initial download was about 16mb, when I finished cleaning and lining up everything. Took about 3 hours which was ridiculous but thankfully its done. Excel crashed a few times and locked up maybe 8-10 times.
Final spreadsheet was A-P and 1-126,220
Final spreadsheet was A-P and 1-126,220
Posted on 11/23/22 at 6:47 am to philabuck
quote:
This doesn't seem to add up.
First thing I thought as well.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News