Tuesday, September 29, 2009

mp3 Woes: Getting rid of tracks by date modified

So I'm needing to get rid of some files in iTunes. I wrote a script to seek and destroy files in my library that I imported on a specific date.

This script takes the iTunes library and loops through it, looking for tracks that were modified on a certain date. When it finds one, it deletes it from the iTunes library. This has the added benefit of removing it from all playlists as well.

It is worth noting the first comment in this script.



' this script only deletes the entries from iTunes playlist - it doesn't actually remove the files from disk!!


' so here we begin the main portion of the script
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, MyFile, FileName, TextLine
Set fso = CreateObject("Scripting.FileSystemObject")

' Open the file for output.
FileName = ".\ResultsFile.txt"
Set MyFile = fso.OpenTextFile(FileName, ForWriting, True)

' Write the result of the function directly to the file
MyFile.WriteLine findfilesmod

' Because you have to close every door you open
MyFile.Close


function findFilesMod

' I really like the ability to run test runs, and having the timings makes it a cake
resultstring = ""
resultstring = "Beginning " & cstr(time) & vbcrlf

' Otherwise, make some variables!
ITTrackKindFile = 1
deletedTracks = 0
counter = 0

' Get a copy of iTunes running
set iTunesApp = WScript.CreateObject("iTunes.Application")
set mainLibrary = iTunesApp.LibraryPlaylist

' Since we're trying to get to the full list of songs, we need this
set tracks = mainLibrary.Tracks

' Now then! We loop the full library to touch the tracks
for each currTrack in tracks
' is this a file track?
if (currTrack.Kind = ITTrackKindFile) then
' if the track has the date we're looking for, in this case 1 January 2001, then
if (cdate(currTrack.ModificationDate) >= cdate("1/1/2001 00:00:00")) and (cdate(currTrack.ModificationDate) < cdate("1/2/2001 00:00:00")) then

' keep track of what's deleted, so it hits the results file
resultstring = resultstring & currTrack.Name & " - " & currTrack.ModificationDate & vbcrlf

' Delete the track
currTrack.delete

' increment the counter
counter = counter + 1
end if
end if

' if you want an early out after deleting a set number of files, you can use this
' otherwise, this script will loop iTunes whole library, nuking as it goes
'if counter >= 1 then exit for
next

' the last output
resultstring = resultstring & "Counted " & counter & " track(s)." & vbcrlf
resultstring = resultstring & "Finished at " & cstr(time) & vbcrlf

' return the results
findfilesmod = resultstring

end function



Tuesday, September 22, 2009

External connections to SQL Server 2008

In trying to get VBScript to talk to my SQL Server, I found that I needed to enable it to accept outside connections.

While I was in the process of researching, I found the following. Since I certainly give credit where credit is due, check out this guide:

http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

Tuesday, September 15, 2009

mp3 Woes: Finding a missing mp3

So I don't like to hunt for things manually. I especially don't like things that I can't just "Fire And Forget" when it comes to searching, so I wrote the following little script to hunt my hard drive for a particular file I wanted to find.

Because a search like this can take a while, and will basically just run in the background until either cancelled via Task Manager (look for wscript.exe), or it completes a scan of EVERY folder on your hard drive, I don't usually write things like this without starting and ending messages.


Set FSO = CreateObject("Scripting.FileSystemObject")
StartingLoc = "C:\"
LookingFor = "String To Search For"

Sub Display_Fs(Folder)
For Each Subfolder in Folder.SubFolders

' Uncomment to display a popup message indicating where we are
'wscript.Echo "Getting: " & Subfolder.Path

' First, show the files in the local folder
    Set F = FSO.GetFolder(Subfolder.Path)
    Set FilesCollection = F.Files
For Each File in FilesCollection
    if File.Type = "MP3 Format Sound" then
        if InStr(file.name, LookingFor) > 0 then
            wscript.Echo file.path
        end if
    end if
Next

' Call the function so we dive into the current folder
    Display_Fs Subfolder
Next
End Sub

wscript.echo "Starting"

' Because the top folder is assumed to have no higher branch we can reference,
' we display the currenly sitting files first
Set F = FSO.GetFolder(StartingLoc)
Set FilesCollection = F.Files
For Each File in FilesCollection
if File.Type = "MP3 Format Sound" then
    wscript.Echo File.path
end if
Next

' Having displayed everything local, we begin our trip down the tree
Display_Fs FSO.GetFolder(StartingLoc)

wscript.echo "Ending"

Tuesday, September 8, 2009

mp3 Woes Part 1

I don't like things that are hard. I really don't.

I also don't like things that are manual. I mean, really. Shouldn't technology be all about making life easier, not adding layers of abstraction to manually processes? Shouldn't it get rid of the manual processes, and free us up to review log files and tweak the underlying processes?

Or have I been a DBA for too long? :)

So I wanted to rename a bunch of files. At once, automagically, but with some manual oversight. After all, I might come up with some files that I didn't know about, didn't like the renaming of, etc.

So here's what I came up with as requirements:

  1. I want a script that will traverse all subdirectories
  2. However, I have no interest in passing parameters (see above comment on "automatic"), so it needs to run starting in whatever directory it wakes up in
  3. It should produce a text file that I can edit before running
  4. It should not try to use iTunes API

So here's the full text of the script that I came up with, that satisfies these requirements.




' This script will recurse a directory tree, from the location the file is run from,
' and will produce a batch file to run that will automatically un-url-encode all files
' it runs into

Function ShowFileAccessInfo(filespec)
' This function will get the details of the file it's given
' and return the details as a formatted string, intended to be
' output to the report file later
Dim fso, f, myString
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(filespec)
myString = "REM Original File: " & f.Path & vbcrlf
myString = myString & "REM New Name: " & unescape(f.Name) & vbcrlf
myString = myString & "REM on Drive " & UCase(f.Drive) & vbcrlf
myString = myString & "REM Last Modified: " & f.DateLastModified & vbcrlf
myString = myString & "move "".\" & replace(f.Name, "%", "%%") & """ "".\" & unescape(f.Name) & """ "
myString = myString & vbcrlf & vbcrlf
ShowFileAccessInfo = myString
End Function


Function ShowFolderList(folderspec)
' This function takes a folder it's given, and loops through
' the collection of objects, calling the file describer
' function for each one
'
' At the end, it returns the formatted string back
' to the calling function

Dim fso, f, sf1, f1, fdc, fc, myString
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderspec)
Set fc = f.Files
set fdc = f.SubFolders

' loop any found subdirectories
for each sf1 in fdc
myString = myString & ShowFolderList(sf1)
next

' loop any found files
For Each f1 in fc
myString = myString & ShowFileAccessInfo(f1)
Next

' because if you don't return what you find, this whole
' write it to disk thing is for naught...
ShowFolderList = myString

End Function



' so here we begin the main portion of the script
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fso, MyFile, FileName, TextLine
Set fso = CreateObject("Scripting.FileSystemObject")

' Open the file for output.
FileName = ".\FileList.txt"
Set MyFile = fso.OpenTextFile(FileName, ForWriting, True)

' Write the result of the function directly to the file
MyFile.WriteLine showfolderlist(".")

' Because you have to close every door you open
MyFile.Close

Tuesday, September 1, 2009

mp3 Woes Introduction

So I downloaded some mp3 files of some speeches given in one of two languages - either English or Spanish. Unfortunately, the source of the files had URL-encoded the names of the files before putting them up on their server.

So, when I downloaded the files, it was into a directory in my iTunes folder. Then, I copied the files from my iTunes directory into c:\musicfiles, so I'd have a backup of the files to work against.

This left me with a folder structure like

c:\musicfiles\english
c:\musicfiles\spanish

The next article is going to show you how I wrote a quick little script to loop everything in a given directory and un-encode it.