Export Help: Total tracks & time, plus more

Support
1

tl;dr How do I export “total time” and “total tracks”?

Background: I am creating a DB in Access of my own music. No commercial program really does what I want, and I love to learn new things (I’m a newbie to Access). Each record will be a single album. I am using the Export feature in Mp3tag to create a text file, so I can import my albums into Access.

My music folders are very organized, in the format:
Artist\AlbumName\{all the tracks}
All my tracks are numbered XX/NN, where XX is the track #, and NN is the total tracks.

My plan is to load the folders I want to export in Mp3tag, sort by track# to get all the 01/NN at the top, then Select & Export only the first tracks. That will give me one TXT line per album in the export file.

Q1: How do I get the total track count when the export data is always 01/15 (or whatever total)? I just want the “15”.

Q2: How do I get the total play time of each individual folder? I see %_total_time%, but it returns a single file.

Bonus Q3: Instead of loading a bunch of folders and selecting the first tracks, is there a way to load all the tracks, and tell the export process to only export where track# equals 01?

Many thanks!

Edit to add: Here’s my current export program…

$filename(MusicDB.txt,utf-8)

$loop(%track%)%album%,%artist%,%year%,%track%,%_total_files%,%_total_time%,%comment%,%_folderpath%
$loopend()

2

You could try:

$filename(MusicDB.txt,utf-8)
album$char(9)artist$char(9)year$char(9)track$char(9)total_files$char(9)total_time$char(9)comment$char(9)folderpath
$loop(%albumartist%)$loop(%album%)$loop(%track%)$loopend()%album%$char(9)%artist%$char(9)%year%$char(9)%track%$char(9)%_total_files%$char(9)%_total_time%$char(9)%comment%$char(9)%_folderpath%
$loopend()$loopend()

Possibly, the reference to %track% is now superfluous as the number to tracks is covered by %_total_tracks%
I used the tab character (ASCII 9) as separator as this leads to a more reliable import as you do not have to handle the comma or semicolon within the data to avoid irritations in respect to the number of fields.
This export sorts first by ALBUMARTIST (not by ARTIST as that may vary within an album), then by ALBUM and then by track, loops through all the tracks to find out how many there are (and does not rely on a possibly outdated numbering in the track field), and then outputs the data for an album by that albumartist.
Please note that all the functions are case-sensitive.
So, e.g. To the Moon <OST> is a different album from To the Moon <ost>

3

Thank you @ohrenkino I need to work my way thru that idea. I want to make sure I actually understand it.

4

If you tell us more about your goal, we may be able to show you other ways of achieving it.

5

Access is a relational database software. You should use this feature and I hope this quote does not mean, that you intend to create a flat-file database.

6

At the risk of sounding like I'm nitpicking:
"Microsoft Access" is a relational database management system (RDBMS) that combines the relational ACE database engine with an integrated graphical development environment (Forms, Reports, Macros, VBA).

However, of course you are right @poster, splitting data into related tables tends to work much better than a single flat one.

7

I appreciate everyone’s replies. I’m still working on @ohrenkino’s idea from above. More on that once I parse it and understand it. Regex stuff is foreign to me.

I want to keep track of my music in ways commercial software just can’t handle. They’re all about “what format?” or labels or pressings. Discogs collections require you to pick a pressing/release. Music Collectorz makes you choose from a DB of recordings. None of them allow you to reorganize their data.

I listen to a lot of bootleg recordings. Almost all live music. A lot of Box Sets and multi-CD releases. I want to be able to track them, and commercial software handles both poorly. I want to track what I listen to, my ratings, do I want to revisit it, etc. The date it was recorded vs the date released. With Box Sets (and I have dozens and dozens of them), commercial forces you to treat it as a whole. I want to treat it as individual albums, under a Box Set label. That way I can track which I’ve listened to, rate each one individually, etc.

I used to program big iron DBs decades ago. Like IBM rDBMS. I decided to write my own for this, and Access seemed like a good compromise–user friendly, still powerful, reasonably priced. And yes, I ABSOLUTELY plan on using “relations”. It’s funny someone mentioned flat-file, I’ve been using Excel to track this stuff until now, and it’s getting overwhelming trying to use it. I do understand flat-file vs relational. For example, I’ll have an ARTIST table that links/relates to that field in the ALBUM table.

I’ve got my fields worked out, I’m still learning how to implement all of it. I’m working on learning Cascading Combo Boxes now, which is how the Box Sets will “relate” to an artist and an album. I also plan to use Links to Cover Images, so they aren’t stored in the DB itself. That’s why you see “folderpath” in my original post formula.

I am working on this Mp3tag export, and also the final design/programming of the DB.

If this isn’t considered too far off-topic, I’m happy to share more as it comes up. And thanks to anyone who read this far!

8

There is no regular expression in the suggested export script.

9

I tried your idea, but I’m doing something wrong. It does run. Look at the beginning of the exported data… there’s some of the programming words at the beginning.

=================below===========

album$char(9)artist$char(9)year$char(9)track%$char(9)total_files$char(9)total_time$char(9)comment$char(9)folderpath

A Whole New Thing [2007 Remaster] Sly & The Family Stone 1967 01/17 1 00:03:59 D:\Music\Master Files\Artists\Sly & The Family Stone\A Whole New Thing [2007 Remaster]\

Dance to the Music [2007 Remaster] Sly & The Family Stone 1967 01/15 1 00:03:01 D:\Music\Master Files\Artists\Sly & The Family Stone\Dance to the Music [2007 Remaster]\Fresh [2007 Remaster]

10

There is a small typo: track% should be written without the % character at the end of the word.

11

Okay, it actually did work, I misinterpreted both the results, and how to use it. I didn’t realize the first line was a header line, I thought it was garbled output. And I was still running it on track #1s only. Once I opened multiple folders, sequenced properly, it does the track count and run time properly. THANK YOU!

One thing: It doesn’t appear to have “line enders” after each folder (or whatever makes a new line). It’s one, long continuous wrap. Is there something I can add to make a New Line after each record?

12

As the line breaks in the script are actually forwarded to the script output, simply experiment a little with extra line breaks in the script until you get the required layout.

13

Bingo! That worked. One line per folder, all the counts work.

Now back to work finalizing the Access DB.

Thanks @ohrenkino for your help, and everyone else who helped/replied.

14

I just want to clarify one thing: It doesn’t seem to matter how the Mp3tag display is sorted, the export process works regardless. I thought it had to be sorted to get the “Album” and “AlbumArtist” to increment properly. But testing showed display order doesn’t matter.

So this means the export process goes thru ALL the records first, does its own sort, and then outputs?!? That’s amazing.

FWIW, it loads 52,000 records/tracks in 9 seconds. Then the export takes 19 seconds if display/sorted, 32 seconds if random sorted.