Suggestion for Huge Flat Excel file

[Gondi]

Hi All,I have a huge Excel file that includes exports of products used in my business for the last 3 years. The sheet is 566,000 rows and 50 columns. I take that data and break it down further, but this is the "raw" data source.Unfortunately, it's gotten rather cumbersome. Sorting is slow and freezes up the computer until it crunches through. About the first 40 Columns are straight data exports and the last 10 are derived from the data (like breaking down a DATE column into different columns like MONTH, DAY, WEEK OF YEAR, etc) Once those are calculated, I cut and past as values, so there are no active formulas or calculations in the whole thing.I'm looking on suggestions on how to speed this thing up. I do run a PivotTable off of the data, so I've looked at PowerPivot, but for the columns that I have to derive, it doesn't seem like the PP interface does that well? Am I wrong there? Are there any other suggestions on different formats that I could put the data in, from within or outside Excel?This is Excel 2010 64bit running on a Xenon E5-1607 w/ 8GB of ramThanks in Advance!-Brian

 

[Gondi]

(Sorry for the formatting -- it stripped the breaks and now it doesn't seem to let me save edits)

 

[dispelthemyth]

What error messages do you get?


I would suggest exporting to a database (Access....) as that much data in Excel is just never a good idea, your file must be >100MB

 

[Gondi]

What error messages do you get?

Excel will just freeze up -- sometimes it comes back in a few minutes, sometimes it's (Not Responding) and the process needs to be killed.

I would suggest exporting to a database (Access....) as that much data in Excel is just never a good idea, your file must be >100MB

Yes, it's almost 200MB at this point. I'm open to the idea of getting into a more robust platform, but I'm a little ignorant of how well I can create those derived columns in that setting. If someone says "Yeah (blank) will do that just fine" I'm happy to go learn, but I'd like a blessing before I trot down the Access or SQL or something path

 

[jamtay317]

It is very easy to import the data into access, you do have the oppitons of going with SQL which is a much bigger project. however I do know that you really dont want to get to much more than 100K records in Access before it starts slowing down. if you plan on get a lot more thank 100K records then you may want to go with SQL, which I have have seen SQL databases 110GBs and they run ok, seams to start slowing down at about 100gb.

 

[marious]

If you are working with Excel 2013 64 bits open task manager > performance and see if it is using all the available RAM, When you open PP it is consuming resources so keep this in mind. If you can add more RAM to the system since there is no restriction, do it