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

KingL2

I made a spreadsheet to manage my monster mp3 collection.  It is driven from a list of the path-statements of the files on my computer. This lets me do things like make a list of Command-line Copy statements to extract a randomised subset of files and copy them over to my (tiny) phone for a different playlist each week.  I also made other excel formulae to extract information from the path statements for a more user-friendly view; allow for easy updating of new additions and automate the 'playlist extraction' process.
e.g. IF(S101937="","",IF(Inputs!$B$8=1,"xcopy "&CHAR(34)&F101937&CHAR(34)&" "&CHAR(34)&Inputs!$B$2&CHAR(34)&" /y","xcopy "&CHAR(34)&M101937&CHAR(34)&" "&CHAR(34)&Inputs!$B$2&CHAR(34)&" /y"))

The problem is, the combination of long formulae and lots of lines (6 figures and growing) means that the spreadsheet is starting to grind to a halt (RAM-wise) in MS Excel. So I'm wondering whether it would help to go over to Database software, rather than a spreadsheet.   I know nothing about MS Access etc, so can someone give me a pointer on this, plus suggest suitable software, please? 

I don't want to have to learn programming languages (to make something alien to me like a SQL database) - just to have something simple that I can migrate my spreadsheet over to. Presumably, MS Access would be the default option.  Would MS Access give a significant performance-improvement over spreadsheets? Would it be able to handle my 'excel-like' formulae/commands (MID, LEFT, IF, SUBSTITUTE, VLOOKUP etc.)? (It doesn't need to support macros/VBA, just formulae). 

Personally, I'd prefer to use Libreoffice (not microsoft), but (1) the Libreoffice database ('Base') software requires Java Runtime - is that still OK, security-wise? (2) how is LibreOffice's 'Base' software performance compared with MS Access/Excel (e.g. number of lines of formulae before it starts struggling)? (the LO _spreadsheet_ sw is much worse than MS, [RAM-wise].

thanks


Windows. Core i5-8250U, 8GB RAM. Currently using MS Office 2013 and Libreoffice latest. The spreadsheet is approx 120,000 lines and 30 columns.

bengalknights

Access is the way to go, in terms of performance you will probably get a 30% gain but once the volume is too much you will start hitting the same issues again.

Andy_L

Can't you  just
1. use some off the shelf MP3 management software to organise your files 
2. Just use excel to randomly extract some files for you phone, assuming that said management software can't just export a random playlist to your phone.

What phone have you got? Could you just stick in a big enough SD card to store all your music? Or just buy a cheap MP3 player to do the same

Why do you need to extract (I'm guessing) track names from the filepath rather than look at the Metadata on the file?

GDB2222

How good are you atVBA?

KingL2

Andy_L said:

Can't you  just
1. use some off the shelf MP3 management software to organise your files 

When I'm at home I use Foobar2k and a NAS with the whole superset set of files on it, which is fine. But I don't know of off the shelf software that will let me automatically extract a subset of files and copy them to a different (smaller) drive/device.

2. Just use excel to randomly extract some files for you phone

That's exactly what I am doing, but the spreadsheet has got too big! (Actually, its not totally random but weighted by a score of how good/bad each song is).

What phone have you got? Could you just stick in a big enough SD card to store all your music? Or just buy a cheap MP3 player to do the same

No - its several TB.

Why do you need to extract (I'm guessing) track names from the filepath rather than look at the Metadata on the file?

I need the full path statements to be able to copy a subset of the files from one drive to another using the OS command line interface. AFAIK, you can't do this with metadata. (I also extract the track names to a different column in the spreadsheet as they are easier to view than the path statements).

KingL2

GDB2222 said:

How good are you atVBA?

Near zero, but I have it on my list of things to learn! I'm guessing that this might not be the best starter-project...?!?1

Username03725

Personally I'd go for MS SQL Server as a good free database, but if you don't want to learn SQL as a language that's a dead end. MS Access would seem to be your best bet as an easy app to export your Excel data into but you'd still need to understand SQL in Access to make the best use of it, and the underlying issue seems to be that your spreadsheet has become too big for Excel but is otherwise the ideal solution for you. 

A project like this though is a brilliant learning curve to grasp the basics of SQL, and going on a step learning simple coding either in Python or a simple app built in MS Visual Studio, to achieve what you want but with a much simpler interface - abstract the underlying commands away behind a button action or similar. But that's a step beyond where you want to be now.

8GB of RAM should be plenty though tbh, so it might be that you just need to tidy the spreadsheet content. Without seeing it it's hard to guess what's wrong, but e.g. if you have 120,000 file path references and they're all pointing to the same place and maybe just the last 1 or 2 folders in the paths are subfolders, you could have one reference to the main path and remove the 120,000 references to to it. 

e.g. some of your MP3s are in
E:/My Music/My Downloads/MP3 Downloads/Rock

and some are in 
E:/My Music/My Downloads/MP3 Downloads/Jazz

etc then E:/My Music/My Downloads/MP3 Downloads can be stored once in a cell, and each of the 120,000 file paths can be reduced to /Rock or /Jazz as appropriate, with your extract routine concatenating the two on output. 

As ever with this sort of thing, there are numerous ways to skin a cat. Identifying the actual problem rather than trying something lock stock & barrel different would be my start point.

Andy_L

KingL2 said:

Andy_L said:

Can't you  just
1. use some off the shelf MP3 management software to organise your files 

When I'm at home I use Foobar2k and a NAS with the whole superset set of files on it, which is fine. But I don't know of off the shelf software that will let me automatically extract a subset of files and copy them to a different (smaller) drive/device.

2. Just use excel to randomly extract some files for you phone

That's exactly what I am doing, but the spreadsheet has got too big! (Actually, its not totally random but weighted by a score of how good/bad each song is).

What phone have you got? Could you just stick in a big enough SD card to store all your music? Or just buy a cheap MP3 player to do the same

No - its several TB.

Why do you need to extract (I'm guessing) track names from the filepath rather than look at the Metadata on the file?

Wow! Thats, what, a years worth play time ;-)

As @GDB2222 implied I think you'd be better of abandoning a spreadsheet holding each track path & using VBA (within excel) to play with the files directly

Jenni_D

Wow - and I thought a MUSIC folder of 67.5GB was quite a lot. (>6,200 MP3 files)

GDB2222

It would make the spreadsheet unwieldy if you have many of your 30 columns filled with formulae. It might work better to have just basic data in the 120,000 rows of information. Ideally, you can then make any calculations when needed using VBA. 

Given that you're not au fait with VBA, can I check that you have replaced most of the formulae with the 'values'?  [Copy then paste special.]

In other words, you don't need to have a formula containing 
IF(S101937="","",IF(Inputs!$B$8=1,"xcopy "&CHAR(34)&F101937&CHAR(34)&" "&CHAR(34)&Inputs!$B$2&CHAR(34)&" /y","xcopy "&CHAR(34)&M101937&CHAR(34)&" "&CHAR(34)&Inputs!$B$2&CHAR(34)&" /y"))

You just need its value. You only need the formula for any new data added.  



 

mksysb

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