In my last article I introduced you to a workaround that allows you to read MP3 ID3 tags in WSH. Today I’m going to show you how to create a database for cataloging those MP3 files.
If you haven’t read “Reading MP3 ID3 Tags is WSH” you should go do that now. We’ll be building off the code created in that article. Without further ado, let’s get going.
strDatabase = "mp3catalog.mdb"
You’ll want to begin your script by specifying a database to use. It doesn’t have to exist. The script will check for the file and create it if it doesn’t. If it does, however, the script will instead update the existing database file.
Dim intSize, intTitle, intComments, intArtist, intAlbumTitle
Dim intYear, intTrack, intGenre, intDuration, intBitRate
Next, you’ll want to instantiate a few variables that will be used to hold Column ID numbers. You won’t generally see me using the Dim statement in my code, but in this case it’s necessary.
As you may remember from my last article, the column IDs we’ll be using vary across different versions of Windows. I’ve created a subroutine that will check the currently running version of Windows and assign the correct column IDs accordingly. Since this is done as a subroutine, it’s important to instantiate the variables first so that they are available globally.
Call SetColIds
Now is a good time to call that subroutine so that our variables will have the proper values. We’ll create the subroutine here in a bit.
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0, _
"Please select a folder containing MP3 files.", 0)
Finally, we’ll instantiate the objects that we need. The FileSystemObject will be used to determine whether or not the database file exists. We use the Shell object and its BrowseForFolder method to display a dialog box for the user to select a folder to catalog. The script will find all MP3 files in the specified folder.
We’ll start by creating our database connection string. We’re going to use Microsoft’s Jet driver to create and access our database. I’ve chosen this driver because it is compatible with Access databases and because it is available natively. You could use any other driver you choose, or even DSN, to create and manage your database. The Jet driver just provides a nice level of simplicity and availability.
If Not objFso.FileExists(strDatabase) Then
At this point, we’re going to begin an If statement. This statement uses the FileSystemObject to determine whether or not the database file exists. If it doesn’t, it will execute the code inside the If statement before continuing. In this case, that code will be used to create our database.
Set objCatalog = CreateObject("ADOX.Catalog")
objCatalog.Create strConnection
Set objCatalog = Nothing
This is the code used to create the database. Notice that we’re using the connection string that we specified earlier. You should also note that I’ve intentionally released the catalog object in the last line. I’ve done this so that the database file will not be in use as we continue.
Set oConn = CreateObject("ADODB.Connection")
oConn.Open strConnection
Next, we’ll use ADODB to open the database we’ve created. This database is empty and has no structure, so the next logical step is to create a table in which to store our data.
oConn.Execute "CREATE TABLE MP3Table(" _
& "[Name] TEXT(255), " _
& "[Title] TEXT(255), " _
& "[Artist] TEXT(255), " _
& "[AlbumTitle] TEXT(255), " _
& "[Year] TEXT(25), " _
& "[TrackNumber] TEXT(25), " _
& "[Genre] TEXT(255), " _
& "[Duration] TEXT(255), " _
& "[Size] TEXT(255), " _
& "[BitRate] TEXT(255), " _
& "[Comments] MEMO)"
We use the ADODB Connection object’s Execute method to execute a line of SQL code that creates the MP3Table table in our database. We’re also specifying each of the fields to create within the table. These match the data that we’ll be collecting from the ID3 tags on our MP3 files.
You’ll also notice that I’ve kept the field names in square brackets. This is because several of my field names are considered reserved keywords by Microsoft Access. Enclosing them in square brackets will avoid errors when this query is executed. For uniformity, I carried this practice for each of my fields.
oConn.Close
Set oConn = Nothing
End If
The code needed to create our database is complete, so we can close the database connection, release the object and close our If statement.
When I created the code to be used in this article, I wanted it to remain expandable. To truly automate this process, I believe that the code should be easily modified to support multiple folders and folders containing multiple file types.
If Not objFolder Is Nothing Then
This line of code checks to make sure that we’re using a valid folder object. When the code uses the BrowseForFolder method as I’ve shown, this should never be a problem. However, if this code is modified to support multiple folders or folder recursion, it is possible that it could try to run against a folder that doesn’t exist. This line simply prevents errors by making sure that doesn’t happen.
Set colFiles = objFolder.Items
Suppose that we have a valid Folder object. We need to use its Items property to return a collection of File objects representing the files in that folder. We’ll create a For Each loop to iterate through the collection and process each of the files.
Set oConn = CreateObject("ADODB.Connection")
oConn.Open strConnection
Set objRs = CreateObject("ADODB.Recordset")
objRs.Open "SELECT * FROM MP3Table", oConn, 0, 3
Before we get into the processing of our collection of File objects, we need to reopen a connection to our database. I’ve chosen to open a connection here, because a connection will not be available at this point in the script if the database already exists when the script is run. I’m also creating a record set so that we can update the data in our database.
For Each objFile In colFiles
strType = objFolder.GetDetailsOf(objFile, 2)
If strType = "MP3 Format Sound" Then
Now we can create the For Each loop that will process our files. I’ve also gone a step further to check the file’s type. An If statement is used to ensure that the commands that follow are only executed for MP3 files. This effectively filters the files in our collection in case a folder contains files other than MP3s.
You might wonder why I haven’t included any code to determine whether or not the folder actually does contain MP3 files. You certainly could, but it’s really not necessary. If there are no files in the folder, the colFiles collection with simply be empty. A For Each statement will not cause an error if it is executed against an empty collection. It will simply continue without executing the loop.
Now as we iterate through the files, we update our database with the information that is read from the MP3 files. This is based off the code that was presented in my last article. It uses the Folder object’s GetDetailsOf method to read the details of each file and adds that data to the record set. That record set is then used to update the database.
End If
Next
objRs.Close
End If
oConn.Close
With all of the work done, we can close all of our structures. Don’t forget to include the lines that close the record set and the database connection.
We’ve now added all of the information that was gathered from the MP3 files to the database that we’ve created. Now let’s create the supporting subroutine and function that we’ll need to make this script usable.
As I noted in my last article, the column IDs that need to be read by the GetDetailsOf method will vary across different versions of Windows. In order to maintain compatibility I’ve chosen to use the SetColIds subroutine to assign the proper column IDs to variables depending upon the current version of Windows.
Sub SetColIds
If GetOSVersion = "XP" Or GetOSVersion = "2003" Then
intSize = 1
intTitle = 10
intComments = 14
intArtist = 16
intAlbumTitle = 17
intYear = 18
intTrack = 19
intGenre = 20
intDuration = 21
intBitRate = 22
We’ll begin by creating the subroutine structure. This subroutine simply uses an If statement to assign variable values for each OS version. Since Windows XP and Windows Server 2003 use the same column IDs, we can combine them into the same branch of our If statement using the Or operator.
ElseIf GetOSVersion = "Vista" Then
intSize = 1
intTitle = 21
intComments = 24
intArtist = 13
intAlbumTitle = 14
intYear = 15
intTrack = 27
intGenre = 16
intDuration = 36
intBitRate = 28
End If
End Sub
The Else branch is used to assign variable values that are compatible with Windows Vista. Notice that both conditional statements rely on the result from a GetOSVersion function. This is a custom function that we’ll need to create in order to determine the OS version.
Set colOperatingSystems = objWMIService.ExecQuery _
("Select * from Win32_OperatingSystem")
For Each objOperatingSystem in colOperatingSystems
strVersion = objOperatingSystem.Caption
Exit For
Next
If InStr(strVersion, "2000") > 0 Then GetOSVersion = "2000"
If InStr(strVersion, "XP") > 0 Then GetOSVersion = "XP"
If InStr(strVersion, "2003") > 0 Then GetOSVersion = "2003"
If InStr(strVersion, "Vista") > 0 Then GetOSVersion = "Vista"
End Function
The GetOSVersion function looks complicated, but it’s actually pretty simple if you break down the steps. It begins by using WMI to poll the name of the operating system. This is done by using the Caption property of the Win32_OperatingSystem class. This class does return a collection of instances, so we have to use a For Each loop to process it properly.
The Exit For statement ensures that the function only returns information for the first instance in a case where a system has multiple operating systems installed. The first instance will be the first “bootable” operating system.
Finally, a set of If statements is used to determine which operating system is being used. VBScript’s InStr function searches the caption string for identifying information. Once the version is determined, it is returned by the function as a short string.
And that’s all there is to it. You’ve just created a script that is capable of looking in a folder, finding any MP3 files, reading their ID3 tags, and then storing that information in an Access-compatible database. By pointing this script to each of your music folders, you can use it to create a catalog of all your music files.
You can use this database in any way you wish. Perhaps you just want an easy way to maintain a music library. Or maybe you have a large collection of music and you’re looking for a better way to manage it. Whatever your need, this script should put you well in the direction you want to go.
How about a scripting challenge? Can you think of a way to create an HTML application to serve as a GUI interface for managing your MP3 catalog? You could have drop down boxes for artist selection and album names and have it return appropriate song titles. Or, you could add a Browse… button that would allow you to select new folders for adding to your database.
The possibilities are endless. Experiment with this code and see what you can accomplish. Until next time, keep coding!
Windows Scripting 
Secure Audio Streaming with ASP.Net and Flash
