Excel - it's good for everything!
There are, perhaps, a couple things you might not know about me: I have a deep and lasting love of Karaoke, and Microsoft Excel. Rarely do these loves meet... until now.
My love for karaoke used to be as an observer. This changed one fateful night about 6 or 7 years ago. The pub (the best karaoke pub ever) was virtually empty and about to close and (karaoke) Pete asked if I wanted to sing... I was just drunk enough to think it was a good idea, and just sober enough to stand. Now you will find it difficult to keep me quiet (and I have sung sober!)
My love for Excel wasn't always so. My first proper job used Lotus 1-2-3 for our spreadsheet needs, and when I started my next (and current) job I was "an expert." It took a year or two to actually live up to that title, but by then I could make 1-2-3 do almost anything but sing. I could make a spreadsheet seem like a program to the point that someone thought they had a virus on their machine when they opened my "do everything" spreadsheet - and I subsequently had to spend a couple of hours in front of the boss explaining what exactly I had done.
About '97/'98 we moved from the Lotus office products to Microsoft and I was not a happy person. All the things I could do in Lotus I was unable to do in Excel - that is not to say Excel couldn't do it - just that I couldn't using Excel.
The turning point was pivots. For someone who does a lot of data manipulation and reconciliations the pivot table is a mind-blowingly powerful tool. I decided there was nothing for it but to learn this new Excel thing. I doubted I would ever reach the heady heights of my 1-2-3 days, but I would give it a go.
A decade or more later and I have used Excel to do things my younger self would not believe... you see Excel now comes with its own programming language that not only controls stuff inside Excel, but with the correct libraries can control THE WORLD other things on the PC as well.
Meanwhile my love of karaoke has meant I was first given a bunch (5-6 thousand) backing tracks with words and since collected a bunch (30ish thousand) more. One of the problems is the haphazard way people name their files. I hold no truck with the convention of lastname, firstname especially when people can't tell the difference between a band and a person (Jovi, Bon) or where to put the first name if there are a bunch of people (John & Dee, Elton & Kiki)
Now it is probably becoming apparent that I am a "little" anal about file names, and perhaps I have a little too much spare time on my hands. So it'll not surprise you that I tasked myself to "clean" my karaoke file collection.
Each track is actually two files... MP3 (audio) and cdg (graphics). So with my 33,488 karaoke tracks - I actually have to clean 66,976 files. What I really needed (apart from a new hobby) is somewhere to store the names of all the files so I could sort them... enter Excel.
It started off pretty quick and dirty. I piped a directory listing to a text file and loaded it into Excel. This is great for a one off task as the directory listing is designed for a person to view it - not a great way to get an excel table. I spent a few hours/days finding out just how badly my files where named. Once I had spent quite a bit of effort working out what the files should be called I needed a way of getting the info from Excel to use to rename the files. I decided on a quick and dirty "make a batch file and run" it approach. My files were about 50% cleaner!
I used this approach a few times more (limiting myself to a couple thousand files at a time as I didn't want to spend a whole week formatting without any progress) and slowly progress was made. When pointing winamp at the directory with all the karaoke files about 80% would pick up the correct album/track/artist/title!
There is a rule in projects work the last 20% of the project takes about 80% of the time! Formatting a piped directory listing wasn't enough! I needed automation! First I automated splitting the track/artist/title from the file name. I started using formulas, but soon ran into problems where the spreadsheet would take hours to recalc... sometimes after every key press!
I moved to a VBA solution which was fairly quick, but a one-time-only operation - if it was re-run it would break anything already fixed. I was still using the piped directory listing/created batch file approach to update the file names and this seemed to be the part that took the most time (and occasionally gave dodgy results)
I stepped up my game. Using the windows scripting library I could get excel to find the file names for me - and to rename the files as needed! I was now able to load all 67 thousand filenames and check consistency! (Beatles vs The Beatles etc)
It was about this point that I hit my first stumbling block. Spotting that there was a Denise Williams and a Deniece Williams (who would get that wrong!) I was rather shocked to find the latter spelling was the correct one. This meant I was unable to rely on judgment (or in some cases memory) to pick which of the sometime many spellings were correct. I had to check the 5000+ artists to see if they were correct. This problem was magnified when I found that iTunes, Spotify, Last.fm and lastly wikipaedia didn't always have a definitive answer to the correct spelling, as the "artist" themselves appeared to be unsure!
Having checked the majority and being about as happy as I could be with the artists I started on the song names... How many ways do you think you can write the song title "Ain't going down 'til the sun comes up" ? You may be surprised!
I was noticing that winamp isn't quite as smart as it aught to be when parsing the file names. It assumes the first "-" it comes to is the divider between the artist and the song title... so bands like a-ha were truncated to "a" and all their tracks prefixed with "ha - " - so I decided to try and write the mp3 tags whilst I was renaming the files.
After a bit of a shaky start (with online forums suggesting I download dlls from the web!) I found that I could use the dll that winamp uses to read the tags to update them from Excel.
I have built (over the last couple months) a spreadsheet that will trawl my hard disk for karaoke tracks, format the names consistently (with some manual intervention) and then update the tags!
I think I am going to go sing some Depeche Mode and gloat a little.
My love for karaoke used to be as an observer. This changed one fateful night about 6 or 7 years ago. The pub (the best karaoke pub ever) was virtually empty and about to close and (karaoke) Pete asked if I wanted to sing... I was just drunk enough to think it was a good idea, and just sober enough to stand. Now you will find it difficult to keep me quiet (and I have sung sober!)
My love for Excel wasn't always so. My first proper job used Lotus 1-2-3 for our spreadsheet needs, and when I started my next (and current) job I was "an expert." It took a year or two to actually live up to that title, but by then I could make 1-2-3 do almost anything but sing. I could make a spreadsheet seem like a program to the point that someone thought they had a virus on their machine when they opened my "do everything" spreadsheet - and I subsequently had to spend a couple of hours in front of the boss explaining what exactly I had done.
About '97/'98 we moved from the Lotus office products to Microsoft and I was not a happy person. All the things I could do in Lotus I was unable to do in Excel - that is not to say Excel couldn't do it - just that I couldn't using Excel.
The turning point was pivots. For someone who does a lot of data manipulation and reconciliations the pivot table is a mind-blowingly powerful tool. I decided there was nothing for it but to learn this new Excel thing. I doubted I would ever reach the heady heights of my 1-2-3 days, but I would give it a go.
A decade or more later and I have used Excel to do things my younger self would not believe... you see Excel now comes with its own programming language that not only controls stuff inside Excel, but with the correct libraries can control THE WORLD other things on the PC as well.
Meanwhile my love of karaoke has meant I was first given a bunch (5-6 thousand) backing tracks with words and since collected a bunch (30ish thousand) more. One of the problems is the haphazard way people name their files. I hold no truck with the convention of lastname, firstname especially when people can't tell the difference between a band and a person (Jovi, Bon) or where to put the first name if there are a bunch of people (John & Dee, Elton & Kiki)
Now it is probably becoming apparent that I am a "little" anal about file names, and perhaps I have a little too much spare time on my hands. So it'll not surprise you that I tasked myself to "clean" my karaoke file collection.
Each track is actually two files... MP3 (audio) and cdg (graphics). So with my 33,488 karaoke tracks - I actually have to clean 66,976 files. What I really needed (apart from a new hobby) is somewhere to store the names of all the files so I could sort them... enter Excel.
It started off pretty quick and dirty. I piped a directory listing to a text file and loaded it into Excel. This is great for a one off task as the directory listing is designed for a person to view it - not a great way to get an excel table. I spent a few hours/days finding out just how badly my files where named. Once I had spent quite a bit of effort working out what the files should be called I needed a way of getting the info from Excel to use to rename the files. I decided on a quick and dirty "make a batch file and run" it approach. My files were about 50% cleaner!
I used this approach a few times more (limiting myself to a couple thousand files at a time as I didn't want to spend a whole week formatting without any progress) and slowly progress was made. When pointing winamp at the directory with all the karaoke files about 80% would pick up the correct album/track/artist/title!
There is a rule in projects work the last 20% of the project takes about 80% of the time! Formatting a piped directory listing wasn't enough! I needed automation! First I automated splitting the track/artist/title from the file name. I started using formulas, but soon ran into problems where the spreadsheet would take hours to recalc... sometimes after every key press!
I moved to a VBA solution which was fairly quick, but a one-time-only operation - if it was re-run it would break anything already fixed. I was still using the piped directory listing/created batch file approach to update the file names and this seemed to be the part that took the most time (and occasionally gave dodgy results)
I stepped up my game. Using the windows scripting library I could get excel to find the file names for me - and to rename the files as needed! I was now able to load all 67 thousand filenames and check consistency! (Beatles vs The Beatles etc)
It was about this point that I hit my first stumbling block. Spotting that there was a Denise Williams and a Deniece Williams (who would get that wrong!) I was rather shocked to find the latter spelling was the correct one. This meant I was unable to rely on judgment (or in some cases memory) to pick which of the sometime many spellings were correct. I had to check the 5000+ artists to see if they were correct. This problem was magnified when I found that iTunes, Spotify, Last.fm and lastly wikipaedia didn't always have a definitive answer to the correct spelling, as the "artist" themselves appeared to be unsure!
Having checked the majority and being about as happy as I could be with the artists I started on the song names... How many ways do you think you can write the song title "Ain't going down 'til the sun comes up" ? You may be surprised!
I was noticing that winamp isn't quite as smart as it aught to be when parsing the file names. It assumes the first "-" it comes to is the divider between the artist and the song title... so bands like a-ha were truncated to "a" and all their tracks prefixed with "ha - " - so I decided to try and write the mp3 tags whilst I was renaming the files.
After a bit of a shaky start (with online forums suggesting I download dlls from the web!) I found that I could use the dll that winamp uses to read the tags to update them from Excel.
I have built (over the last couple months) a spreadsheet that will trawl my hard disk for karaoke tracks, format the names consistently (with some manual intervention) and then update the tags!
I think I am going to go sing some Depeche Mode and gloat a little.