Using WSH to Catalog MP3 Files - Updating the database
(Page 3 of 4 )
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.
objRs.AddNew
objRs("Name") = objFile.Name & ".mp3"
objRs("Size") = objFolder.GetDetailsOf(objFile, intSize)
objRs("Title") = objFolder.GetDetailsOf(objFile, intTitle)
objRs("Comments") = objFolder.GetDetailsOf(objFile, intComments)
objRs("Artist") = objFolder.GetDetailsOf(objFile, intArtist)
objRs("AlbumTitle") = objFolder.GetDetailsOf(objFile, intAlbumTitle)
objRs("Year") = objFolder.GetDetailsOf(objFile, intYear)
objRs("TrackNumber") = objFolder.GetDetailsOf(objFile, intTrack)
objRs("Genre") = objFolder.GetDetailsOf(objFile, intGenre)
objRs("Duration") = objFolder.GetDetailsOf(objFile, intDuration)
objRs("BitRate") = objFolder.GetDetailsOf(objFile, intBitRate)
objRs.Update
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.
Next: Finishing the script >>
More Windows Scripting Articles
More By Nilpo
