• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Memory utilization of excel files goes high

gknath79

gknath79

New Member
I have an excel sheet containing some information in rows (around 6 Lakh) and columns (around 20) and the file size is of around 150 MB. When I open the file the memory utilization of the system (Windows 10) reaches upto 640 MB. Is this normal? Is there a way to reduce the memory utilization for such huge files?

memory.PNG
 
Excel files use compression
When you open them they uncompress into memory
So that is normal
 

Excel is one of the worst software in term of memory management !
It needs to load all content of worksheets in memory :
data but formula and conditional formatting too like objects …
As Excel is just a spreadsheet, a calculation interface,
for big data better is to use a database software
that could be 50 times faster for some treatments …
​
 
Marc L said:
Excel is one of the worst software in term of memory management !
It needs to load all content of worksheets in memory :
data but formula and conditional formatting too like objects …
As Excel is just a spreadsheet, a calculation interface,
for big data better is to use a database software
that could be 50 times faster for some treatments …
​
Click to expand...

Dear Sir @Marc L ,

you are right, So which is best Database software? if more then 6 Laks records,Can Aceess is best?

Regards,

Chirag Raval
 
So which is best Database software?
Click to expand...

That really depends on your need and resources available.

1. MYSQL (or it's derivative MariaDB) is free and can handle most needs, but it will require bit more maintenance than MSSQL.

2. MSSQL Express is good option if database size is less than 8~10 GB. Though, it will lack some functions available in full version, it is free. This is often good choice for data storage, but not for production server (i.e. a lot of transaction takes place, such as pos db). Since it's limited to single CPU, or in more recent versions "The lesser of one socket or four cores".

3. Access is good choice, if database size is relatively small, 2Gb or less (including database objects & data) AND if you don't need remote access to db (i.e. from outside company network). You can get around 2Gb limit by using linked table to other Access DB.

You'll have to decide for yourself, what db truly is best suited for your need.
 
Dear Sir @Chihiro ,

Thank you very much for digging deeper in this subject.

MYSQL (or it's derivative MariaDB)
MSSQL Express is good option...

sure it can be best data storage warehouses... but

Are there any extra effort to learn this? if user aware of Excel?

Or

It Is Basic Database storage container Applications?

Then How to retrieve / Filter/ Summing, subtotalling on that data?

which are basic requirement to learn this 2 aspects of database?
Or

which aspect from which user should be aware?

It can be useful to link this database with excel or Access?

please guide.

Regards,

Chirag Raval
 
As with any tool, you'll need to invest time and effort to learn it. But it isn't hard. And there are plenty of documentations and help sites for those I listed.

If you want minimal learning curb, use Access db, so that you don't need to worry about network etc.

For aggregation, all of them use one type of SQL or another. Each has it's own function, but many functionality are shared. If you learn one, it's not hard to learn others.

If needed, data can be aggregated through data model (PowerPivot) and PowerQuery on Excel side.
 
Dear Sir,

Thank you very much for your effort to teach something towards, best Database Applications.

I think, users should first focus towards Access/ Power Pivot /Power Query ..& then goes towards above your suggested applications.

thank you very much sir.

Regards,

Chirag Raval
 
It really depends on what you want from your data?

There are techniques available within excel that natively allow you to filter 100,000+ records of 200'ish fields as you type values in search boxes and the resultant data is filtering, sorting and paginating on the fly

The techniques involve the use of Conjunctive Truth Tables, a technique borrowed from computer programming, and as far as I am aware only Daniel Ferry teaches them in the ExcelHero Academy course.

Next the MS Power Suite of tools, Power Query, Power Pivot, Power Map, etc allows handling of over 2 Billion records and is pretty swift

Both these techniques will be faster and more flexible than accessing any external DB system
 
Dear sir @Hui ,

Thanks for give useful aspect on large database.

This site construct based on Excel's Magic.
I deeply love excel & I agree that I am far away from many
Useful magical aspects of excel.

Yes , excel is flexible & we can play with data as require.
But flexibility increase risk of loss of accuracy, consistency & trust on database.
Due to every cell can have different formatting or data can looks different again actually
They.

We often need to check data entered & it's type. Increase doubt about accuracy.
So often need to force excel like we use text to column on single column to force
Excel to recognise data type of that column .


If in middle of database accedenty delete any single cell &
Un Knowingly shift cells up or left , whole database cam become messup.
This flexibility & easily playable database aproach can resultl in loss of valuable database.

If ltlle more then small database, When accuracy is matter , Access look strict again flexibility
& we can trust on access database compare to excel. Because
It don't allow to enter other data , except columns data type we fixed.

Finally we collect data to generate various types of results, on which some one
Can take discussion or predict future & can take steps. So flexibility
Effect many aspects.

Now excel have Power Suits , that may be new to
Many users to learn, if , can deeply understand it's
Functionality & how to use it, can really become most helpful
Tools in excel & can decrease of requirement of
Other database applications as per expert sir @Hui

If large database, & if have short time to
Complete/ generate various types of results & reports, Hope there are step by step fundamental learning
Available about this power tools ,for who, love Excel.

Regards,

Chirag Raval
 
Last edited:
You must log in or register to reply here.
Back
Top