Migrate a monster spreadsheet over to Database software? If so, which software?

[Deleted User]

I think you are making it harder than it needs to be by copying music files around. I got around 30k / 500 GB of music tracks but can listen to the anywhere including on the phone, in the car, on any computer or device without copying or thinking about what I play in advance.

Simple solution, Plex server.

The whole collect is available all the time wherever I am.

I have playlists set up so I can listen to a subset of tracks and with the Plexamp app on the phone, I can just ask for a style / genre and let it select some tracks for me.

In fact the whole family can use it, not just me, each with their own personalised preferences and playlists. Like having a personal Spotify.

Sandtree

120,000 rows and 30 columns is tiny for Excel, the issue isn't the amount of data but the formulas you are using and I am guessing you have formula calculation set to automatic.

Obviously a database holds data not formulas, you can create views with formulas but generally its more efficient to transform the data in the load process than to do it on the fly with each query. The exact same can be said about excel too though

Heedtheadvice

Lots of good (if sometimes differing) approaches.

If changing to a database then I would stick with Access as it is easy to transfer data to it from Excel and you can even use some of the same or similar formulas or change them to VBA in Excel and port to the database. Easy to link the two together.

However, as you already have experience and are using Excel and have your process working currently then I would stick with it and modify your approach rather than start learning more database methods. Like Sandtree posted Excel xan cope with lots of data and the time issue is probably all those formulas. With auto calc on (the default) every time a cell is changed all the formula calculations are processed. That is not required in your specific case.

You can do several things. As was writ above process once at loading where you can. Process loaded data once and then store the values rather than formulas, then work on those for your random selections.

If you kearn a little VBA (I know some learning....) You can write your complex formula in it's script once. Run it though the range of cells that is the scource and save the result in an adjacent column as data.  So then you do not necessarily need to turn off auto calc, and use more vba script to load the data at the outset and export your random selected list. Using single formulas in vba rather than a formula in every cell in a sheet has a big potential benefit in that it is just one bit of code to manage instead of every cell that can become a nightmare if one is changed in error or cell copying is done imperfectly.

Getting some knowledge of vba also leads you into thinking more about templates, using script that is available on line for many tasks and will make writing some processes in vba for other tasks easier. It opens up many new approaches and add a lot more power to Excel and the other Office packages.

If you didn't have so many mp3s to chose from then I would have gone for the player approach with a shuffle facility!

As has been oft written many way to skin a cat in Excel.

onomatopoeia99

mksysb said:

SQLite would probably be the easiest to install and use.  If not them MariaDB would handle it.

OP doesn't want to have to learn programming languages or create SQL databases.  SQL isn't a programming language and it's not a database either, but I have a fair idea what they meant and that would rule out SQLite and MariaDB.

MariaDB (I'd run it in a debian VM, no need for lots of data to be in my desktop OS) and rewrite the queries into SQL would be the option I'd pick in the same situation as well though if there weren't restrictions on what the solution could be.

tafelmoneysaver

KingL2 said:

 starting to grind to a halt 

You can try to switch the formula to manual calculation. This will prevent them all updating after each edit and you can choose when go and boil the kettle as you run the monster update

https://support.microsoft.com/en-us/office/calculating-and-recalculating-formulas-in-browser-based-workbooks-cdab7e4c-6b15-4004-8958-6b89c0484545

KingL2

OP says
Lots of great inputs - thanks a lot everyone - it gives me plenty to chew on.