Ness.ch - misc

1. Get a listing in Excel of all properties of your MP3s

Create an Excel file and don’t forget to save it with an .xlsm extension (Excel worksheet with macros). Add the following labels for each column in row 1:

1. Full Path
2. File name
3. Artist
4. Album
5. Year
6. Genre
7. Title
8. Artist 2
9. TrackNr
10. Duration
11. Quality

Open the Visual Basic for Application Editor of Excel ([Alt] + [F11]), and add a new module. Insert the following code, which contains all the needed Excel VBA logic:

Dim line As Integer
' Dumps the properties of all the files within the given path into the current worksheet
Sub StartMp3Processing()
line = 2
dumpFileProperties "[PATH_TO_YOUR_MP3_ROOT_FOLDER]"
End Sub
Function UpdateMp3(file, field, oldValue, newValue)
If file <> "Full Path" Then
exe = "[PATH_TO_TAGUPDATE_BINARY]\TagUpdate.exe"
Shell exe & " " & SurroundWithQuotes(file) & " " & _
SurroundWithQuotes(field) & " " & _
SurroundWithQuotes(oldValue) & " " & _
SurroundWithQuotes(newValue)
End If
End Function
' TODO: enhance this - e.g. strip existing quotes with \
Function SurroundWithQuotes(value)
SurroundWithQuotes = Chr(34) & value & Chr(34)
End Function
' Recusively dumps specific properties of files present within the given path
Function dumpFileProperties(pathToAnalyse)
' List of the properties ID we want to dump
' A full list of available properties is available in the comments of
' http://msdn.microsoft.com/en-us/library/windows/desktop/bb787870(v=vs.85).aspx
propertiesIdList = Array(0, 13, 14, 15, 16, 20, 21, 26, 27, 28)
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(pathToAnalyse)
For Each strFileName In objFolder.Items
If strFileName.isFolder Then
dumpFileProperties strFileName
' Uncomment this line and comment the other Else if you want to restrict the dump...
'ElseIf objFolder.getDetailsOf(strFileName, 2) = "VLC media file (.mp3)" Or objFolder.getDetailsOf(strFileName, 2) = "VLC media file (.wav)" Then
Else
' Dump the full file path in the first column
ActiveSheet.Cells(line, 1) = strFileName.Path
col = 2
For Each propId In propertiesIdList
ActiveSheet.Cells(line, col) = objFolder.getDetailsOf(strFileName, propId)
col = col + 1
Next
line = line + 1
End If
Next
End Function

Update placeholder [PATH_TO_YOUR_MP3_ROOT_FOLDER] with the path to your MP3 files and, once done, press [F5] in the Visual Basic Editor to start sub “StartMp3Processing”. Wait a few minutes for the processing of the mp3 files to finish:

By splitting the window and freezing the panels, you can keep the headers always visible. Next to this, add the smart filters to the data and you’re now ready to process all the available data:

2. Edit the properties in Excel

Let’s now update inaccurate properties within Excel. Unfortunately, VBScript object Shell.Application only provides a read-only interface to get this data. To edit these properties, we have to rely on an external program. In this case, I decided to code my own little command line program in C#, which references the taglib sharp library. No compiled version of it is available on GitHub but you can download my compiled version here.

The code of the little command line program – TagUpdate.cs – is pretty simple (see below). It can be easily compiled on any recent Windows machine using command c:\Windows\Microsoft.NET\Framework\v3.5\csc.exe TagUpdate.cs /r:taglib-sharp.dll

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace TagUpdate
{
class Program
{
// Created by Alexandre Herzog under a CC-BY-SA licence (Creative Commons Attribution + ShareAlike)
// You can compile this software on any Windows machine with the following command:
// C:\>c:\Windows\Microsoft.NET\Framework\v3.5\csc.exe TagUpdate.cs /r:taglib-sharp.dll
// Lists all the fields we can update with their function
private static Dictionary> dict =
new Dictionary>();
static void Main(string[] args)
{
if (args.Length != 4)
{
Console.WriteLine("Error while invoking TagUpdate - {0} arguments were passed instead of 4", args.Length);
Console.ReadLine();
return;
}
// Populate the list of available fields to update
dict.Add("Artist", checkAndUpdateArtist);
dict.Add("Album", checkAndUpdateAlbum);
dict.Add("Year", checkAndUpdateYear);
dict.Add("Genre", checkAndUpdateGenres);
dict.Add("Title", checkAndUpdateTitle);
dict.Add("TrackNr", checkAndUpdateTrackNr);
// sets the variables
string file = args[0];
string champ = args[1];
string oldValue = args[2];
string newValue = args[3];
try
{
UpdateMp3Tags(file, champ, oldValue, newValue);
}
catch (Exception ex)
{
Console.WriteLine("\n\nError {0} while updating file {1}:\n{2}\n\n", ex.Message, file, ex.StackTrace);
Console.ReadLine();
}
}
public static int UpdateMp3Tags(string file, string attribute, string oldValue, string newValue)
{
TagLib.File tlf = TagLib.File.Create(file);
if (!dict.ContainsKey(attribute))
throw new Exception(String.Format("No such attribute / action is defined:\nReceived: {0}\nPossible actions: {1}", attribute, FromArrToString(dict.Keys.ToArray())));
dict[attribute](tlf, oldValue, newValue);
return 0;
}
/*
*		Helpers
*/
private static string FromArrToString(string[] param)
{
if (param.Length == 0)
return "";
// Linq beauty :)
return param.Aggregate((current, next) => current + "; " + next);
}
private static string ReturnsEmptyIfNull(string txt)
{
if (String.IsNullOrEmpty(txt))
return "";
return txt;
}
private static uint? ReturnNullIfEmpty(string txt)
{
if (String.IsNullOrEmpty(txt))
return 0;
return Convert.ToUInt32(txt);
}
/*
*		Check and update functions for the various properties
*
*		All functions have the same signature to match the dictionary
*/
private static int checkAndUpdateAlbum(TagLib.File file, string oldValue, string newValue)
{
if (ReturnsEmptyIfNull(file.Tag.Album) != oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Album, oldValue));
file.Tag.Album = newValue;
file.Save();
return 0;
}
private static int checkAndUpdateArtist(TagLib.File file, string oldValue, string newValue)
{
if (FromArrToString(file.Tag.Artists) != oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", FromArrToString(file.Tag.Artists), oldValue));
file.Tag.Artists = new string[] { newValue };
file.Save();
return 0;
}
private static int checkAndUpdateTitle(TagLib.File file, string oldValue, string newValue)
{
if (ReturnsEmptyIfNull(file.Tag.Title) != oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Title, oldValue));
file.Tag.Title = newValue;
file.Save();
return 0;
}
private static int checkAndUpdateYear(TagLib.File file, string oldValue, string newValue)
{
if (file.Tag.Year != ReturnNullIfEmpty(oldValue))
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Year, oldValue));
file.Tag.Year = Convert.ToUInt32(newValue);
file.Save();
return 0;
}
private static int checkAndUpdateTrackNr(TagLib.File file, string oldValue, string newValue)
{
if (file.Tag.Track != ReturnNullIfEmpty(oldValue))
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", file.Tag.Track, oldValue));
file.Tag.Track = Convert.ToUInt32(newValue);
file.Save();
return 0;
}
private static int checkAndUpdateGenres(TagLib.File file, string oldValue, string newValue)
{
if (FromArrToString(file.Tag.Genres) !=oldValue)
throw new Exception(String.Format("The old value is incorrect\nExpected:\t{0}\nReceived:\t{1}\n\n", FromArrToString(file.Tag.Genres), oldValue));
file.Tag.Genres = new string[]{newValue};
file.Save();
return 0;
}
}
}

Once compiled, you just have to replace placeholder [PATH_TO_TAGUPDATE_BINARY] in your Excel VBA code with the full path pointing to TagUpdate.exe and you’re ready to go. You can use this binary to update the following mp3 properties:

• Artist
• Album
• Year
• Genre
• Title
• TrackNr

To automate the update process and allow mass-modifications, use the previously defined custom Excel formula as below:

The first argument of UpdateMp3 is the full path to the MP3 file, the second is the type of information to update – here the artist’s name. The third value is the old value and the fourth the new value to set. Having to specify the expected old value of the file ensures you have a consistent set of data before the update and ensures that you’re not overwriting unwanted information.