Create a normalised music database with Access

When creating a normalised database of music ­ the artists, titles and tracks held in Windows Media Player, the task splits into three parts.

How to:
1. Generate a text file of the data that Media Player holds;
2. Import that file into Access; and
3. Create a normalised set of tables from the data.

It is worth splitting the process into steps because importing data and/or normalising it are common operations and apply to more than this example.

Importing data
To recap last month, the solution is based on the fact Media Player allocates one folder for each artist (for example, Air).

Within each artist folder there is a folder for each album (Talkie Walkie); within the album folders are the appropriate tracks (Venus, Cherry Blossom Girl).

We can use the Dos command ‘Dir’ (see last month’s issue) to dump the entire folder structure to a text file. This generates a line in the text file for every folder and sub-folder.

In this exercise we will only use the lines that define the tracks, for example:

C:\Users\Mark\Music\Air\Talkie Walkie\01 Venus.wma or C:\Users\Mark\Music\Air\Talkie Walkie\02 Cherry Blossom Girl.wma, but we’ll import the whole lot into Access because it is easier to do that than to remove the ones we don’t want.

Later you’ll see that we use a WHERE clause in a query to ignore those rows not defining the tracks.

I have also supplied a tiny example of the text file (penguin.txt) generated by the Dir command.

In Access 2007, you import the data from the text file by selecting External Data, Text File, choose to import the source data into a new table, browse to the file and then click OK.

In Access 2003 it’s slightly different ­ you start from File, Get External Data, Import.
In the next screen select Fixed Width and click Next.

In this screen we can insert breaks that split the incoming data into separate fields. Depending on the set of data you use, Access may already have inserted one or more breaks. Remove these (by double-clicking on them). Then inset one as shown in screen 1, just at the point where the artist names begin.

Click on Next. In this screen, mark the first field as ‘Do not import field (Skip)’ ­ this saves us from creating a field that always contains ‘C:\Users\ Mark\Music\’. Rename the second field as RawData, then click on the Advanced button and set the width of the RawData field to 255 characters and click OK.

Click Next, allow Access to add a primary key and click Next once more. Name the table Penguin and click Finish. We now have a table that contains the data. I have supplied a tiny sample table; just replace it with your own data.

Converting flat-file data
Now we need to split the data into a set of normalised tables, where each table is about one entity. The entities will be Artist; Album; and Track.

It is easy to extract the names of the artists, albums and tracks using GROUP BY queries. The trick is to do this while retaining the information that a particular track is on a particular album performed by a given artist.

For this we have to assign a primary key to each table and ensure we also insert the appropriate foreign keys in the other tables that point to those primary keys.

Our starting point is the string we have in RawData about each track, for example: Air\Talkie Walkie\02 Cherry Blossom Girl.wma.

The good news is that this string always has two delimiters (backslashes) separating the artist, album and track.

So we can write a query that finds the positions of those delimiters. This query relies on a function called InStr (short for In String). It is specific to Access but all databases engines have a similar function.

The query is called SplitString:

SELECT Penguin.ID, Penguin.RawData,
InStr(1,[RawData],”\”) AS PosSlash1,
InStr([PosSlash1]+1,[RawData],”\”) AS PosSlash2
FROM Penguin;

It creates two fields (PosSlash1 and PosSlash2), which record the position of each backslash in the string. If there is no appropriate backslash, the field contains a zero.

We can write a second query (DissectedData) that runs against the SplitString answer table and dissects the string into its components, including the track number:

SELECT Left([RawData],[Posslash1]-1) AS Artist,
Mid([RawData],[Posslash1]+1,[posslash2]-[Posslash1]-1) AS Album,
Mid([RawData],[Posslash2]+1,2) AS TrackNo,
Mid([RawData],[Posslash2]+4,255) AS Track
FROM SplitString
WHERE SplitString.PosSlash2>0;
Note that the WHERE clause:
WHERE SplitString.PosSlash2>0;

ensures we are only running this on those rows that have a second backslash ­ in other words, those rows that define tracks. All you have to do is to open this query to see it working.