Rockbox mail archive
Subject: Re: using excel to manage mp3's
From: Neon John (johngd_at_bellsouth.net)
Here's what I wrote in response to a similar question on usenet:
On Sun, 08 Feb 2004 10:50:51 -0500, "novellconsulting_at_nospammindspring.com"
>I have 18,000 songs and want a program which will print out a catalog
>based on artist name. Most I have tried seem to crash after trying to
>read this many song into their database. Any ideas?
My, we've been a busy little bootlegging bee, haven't we?...
Using the Unix philosophy of doing things by chaining together several
utilities, here's how I do it without any other software than what comes with
most windoze computers.
My music is organized thus:
Get a command prompt. cd to the mp3 directory. Execute the following
f:\mp3>dir *.* /b/A-D/On/s >list.txt (do dir /? to see what the commands
If you want a list of artists and albums but not individual songs, do the
above command but substitute /AD for /A-D.
Open list.txt in excel or your favorite spreadsheet. Specify a "delimited"
file and specify the "\" character as the delimiter. What you will get is a
spreadsheet with each component of the path to each song in a column. You can
delete the "f:" and "mp3" columns. If you want your list sorted by artist,
simply select all columns and sort on artist.
I have a little batch file sitting in f:\mp3 that executes the above command
and then invokes excel. All I have to do is click on the batch file from file
Carrying this a little further, to select songs for copy to the Archos, bring
up list.txt in your favorite editor, say Vi. Go down the list and in front of
every song you want to copy, place a marker, say a "*". When finished, sort
the list (%s ! sort in vi). This will group all the marked files together.
Write this group of files to another file. Edit that file.
replace the marker with "xcopy " (:%s/\*/xcopy / in vi). On the tail end of
each line add "G:\ /R/Y" (:%s/$/G:\\ \/R\/Y/ in vi). Make "G:\" whatever
drive and directory on your Archos you desire. The Xcopy command will make
the necessary directories on the fly. Save this file, rename it to a .bat
file and run. This is MUCH faster than trying to do it in a bat file loop.
In Vim (and presumably other Vi clones), one can set up a macro to execute the
above editing commands with only 2 or 3 keystrokes.
Once you do this a couple of times it becomes almost automatic. I recommend
setting up a scheduler job to produce the list.txt file periodically, say,
once a week. Then when you want to make up a new batch of files to copy you
already have the file list available.
I'm sure there are fancy GUI programs that will do this task while hogging
resources and taking a long time. But I'm an old unix guy who still thinks VI
and text files is the way to go.
On Mon, 12 Apr 2004 16:58:51 -0500, "Jason Williams" <jwilliams_at_wi.rr.com>
>I just spent an entire day exporting my mp3's to Excel, deciding what of my
>50gig collection i wanted on my jukebox and then figuring out how to use vis
>basic to copy the files.
>Anyone got a better way?
>I ran the macro in 2 parts : once to creat the dirs and once to copy files.
>Hence, the stuff with ' in front of it was the file folder creation part and
>the other part was to copy.
>I couldn't even do it all in one. I had to run it like 8 times cuz the
>procedure was too big.
>For mycount = 1 To 3828
>' For mycount2 = 1 To 4
>' If mylist(mycount, mycount2) <> "" Then
>' On Error Resume Next
>' MkDir mylist(mycount, mycount2)
>' On Error GoTo 0
>' ChDir mylist(mycount, mycount2)
>' End If
>' Next mycount2
>' ChDir "G:\"
>FileCopy mylist(mycount, 1), mylist(mycount, 2)
John De Armond
Cleveland, Occupied TN
Page was last modified "Jan 10 2012" The Rockbox Crew