It's the difference between the way things are accessed/handled behind the scenes in a spreadsheet vs. database[3][1b].
Adding a programming / scripting language isn't going to change exel data access patterns.
One possible approach:
Using excel as front end and storing actual data in database would speed things up.
This assumes one uses 'python' on the database to do behind the scenes analysis[1] vs. moving complete excell worksheet(s) into / out of database[2]).
Relevant background note: Spent quite a few years supporting researchers who were comfortable with excel; but the basic data requirements far exceeded what excel was capabile of handling (pre-analysis, 1-2milion excel cells). aka local machine access only, no remote / cloud stuff.
Our startup, rowzero.io, easily handles tens of millions of rows and is a subset of Excel. Leave us feedback in the app if there are any missing features you need.
Well, there are the HPC Services for Excel, were you can offload calculations to an HPC Cluster and support more rows. https://learn.microsoft.com/en-us/powershell/high-performanc...
its not an alternative, but maybe it helps for your use case.
That's cool but seems overkill for processing 100K-1 million rows
I just feel like that is not really a lot of data for a single computer
For example you could write a python script that loops over an array of a million values and runs a calculation like its nothing.
Yet processing a column of 1M values in excel just falls apart and becomes really slow, even the UI becomes laggy
Something about the implementation just can't handle that
It's the difference between the way things are accessed/handled behind the scenes in a spreadsheet vs. database[3][1b].
Adding a programming / scripting language isn't going to change exel data access patterns.
One possible approach:
Using excel as front end and storing actual data in database would speed things up. This assumes one uses 'python' on the database to do behind the scenes analysis[1] vs. moving complete excell worksheet(s) into / out of database[2]).
Relevant background note: Spent quite a few years supporting researchers who were comfortable with excel; but the basic data requirements far exceeded what excel was capabile of handling (pre-analysis, 1-2milion excel cells). aka local machine access only, no remote / cloud stuff.
---------------------------
[1] : a) excel with access database : https://www.youtube.com/watch?v=-DbvcuE8ITA b) excel with postgres : https://rowzero.io/blog/connect-postgresql-to-microsoft-exce...
[2] input/export excel data : https://www.youtube.com/watch?v=9PxaTPZIYmc
[3] why database instead of excel : https://baserow.io/blog/database-instead-of-excel
Our startup, rowzero.io, easily handles tens of millions of rows and is a subset of Excel. Leave us feedback in the app if there are any missing features you need.
A relational database. Excel data generally maps well to an RDBMS. You can run sqlite, MySQL, or PostgreSQL locally on a typical desktop/laptop.
Excel can serve as the front-end to a “real” database.
Use PowerQuery/PowerPivot/DAX.
[dead]