Extracting length from WAV files

I have a recorder that records two-way radio transmissions for my coop. This recorder is reviewed during/after issues that may arise out in the field when the guys are working. Also, the recordings are reviewed to check where people are at any given time and to research a sequence of events. One of the most common issues that I have been presented with is when people are using the radio traffic frivolously. I get really subjective comments like, “When [name] does disconnects, there is an awful lot of noise on the radio. Is there some multi-million dollar system that you can buy that will do this automatically and keep the radio quiet?”
So, my coop purchases a big mobile data system effectively transferring the traffic and cost to a wireless cell phone network. I hate it when this happens because we always have to re-visit the same issues with the introduction of a new system that could have been quantified prior to investing money in a replacement. For instance, instead of hearing about radio traffic (or “noise” as an operations manager may call it), we now have cell phone data cards that we pay monthly fees for to augment a voice system we still have to support. To add insult to injury, the people using the mobile data system still have to use the 2-way radio in places where data service is unavailable. So, what did we really accomplish here?
To add to the idiom “it can’t be managed if it can’t be measured”, most of my posts have to do with quantifying a problem before addressing it. I use the simple Dr. Deming circular methodology to quality problem solving: plan–do–check–act (PDCA) or plan-do-study-act. I really don’t appreciate using purely personal and subjective measurements to determine the success or failure of a project. What ends up happening is the root-cause of the problem are never determined and the symptoms are merely transferred to other systems.

Now I have a list of recordings that are being harvested by the recording device in the server room.  The software really records everything heard over the radio when the squelch is broken.  Either way, it gives me a duration of recording per file.

I wanted to do this on the command line and bring the list into excel for analysis.  What I found was that Windows Explorer had a Length option to view the length of an audio file but there was no way that I knew of to export this file metadata to excel.  I used a command-line application called SOX to get the information from the files in the folder.

After downloading sox, I copied the file sox.exe to soxi.exe.  This essentially changes the running configuration of sox to an information-only application.  Sox has lots of audio modification utilities that I don’t need for the reporting purposes of this post.

Running the command (note the spacing):

C:\sox-14-3-1>forfiles /p r:\MyRadioRecordings\ /m *.wav /C "cmd /c echo echo @file   @fdate    @ftime    [ soxi -d @path [" > file.txt

This gave me a list in the file.txt that looked like this:

echo "w4CA5E1E1p0.WAV" 10/1/2010 6:28:14 AM [ soxi -d "r:\MyRadioRecordings\w4CA5E1E1p0.WAV" [
echo "w4CA6299Cp0.WAV" 10/1/2010 11:34:17 AM [ soxi -d "r:\MyRadioRecordings\w4CA6299Cp0.WAV" [
echo "w4CA7342Cp0.WAV" 10/2/2010 6:31:38 AM [ soxi -d "r:\MyRadioRecordings\w4CA7342Cp0.WAV" [
echo "w4CA73445p0.WAV" 10/2/2010 6:32:03 AM [ soxi -d "r:\MyRadioRecordings\w4CA73445p0.WAV" [
echo "w4CA73892p0.WAV" 10/2/2010 6:50:34 AM [ soxi -d "r:\MyRadioRecordings\w4CA73892p0.WAV" [
echo "w4CA742AFp0.WAV" 10/2/2010 7:34:19 AM [ soxi -d "r:\MyRadioRecordings\w4CA742AFp0.WAV" [

I used a separator “[” to search and replace with ” >> newfile.txt[CRLF]”  I used Notepad++ to do the search and replace and add the newline characters.  This is quick and dirty.  I Googled hoping that I could do this all in a single batch file, but I don’t think that I need to do this report often – maybe once or twice a year or whenever I get someone complaining about crowded radio channels.

After that, I ran the batch and wound up a file with the file description CRLF and duration following that. So, I used notepad++ to replace “M[CRLF]00” with “M 00”. The resulting file looked like this:

"w4CA5E1E1p0.WAV" 10/1/2010 6:28:14 AM 00:00:13.76
"w4CA6299Cp0.WAV" 10/1/2010 11:34:17 AM 00:00:12.16
"w4CA7342Cp0.WAV" 10/2/2010 6:31:38 AM 00:00:13.89
"w4CA73445p0.WAV" 10/2/2010 6:32:03 AM 00:00:14.27
"w4CA73892p0.WAV" 10/2/2010 6:50:34 AM 00:00:23.81
"w4CA742AFp0.WAV" 10/2/2010 7:34:19 AM 00:00:59.65
"w4CA7444Dp0.WAV" 10/2/2010 7:41:20 AM 00:01:07.65

This is easily imported into Excel.  With some fancy pivot chart work, I was able to make a nice little chart of recorded radio time broken into 15 minute intervals.  I added the date and time cells together, formatted the cells as date/time, and calculated the interval using the formula “=ROUND([Date+Time]*96,0)/96”

Now I am armed with some good intel next time someone complains about too much radio traffic!

Summation of duration graphed in 15 minute intervals

Ok, I know that this is quick and a very dirty way of getting simple information about how much my 2-way radio system is actually being used.  But it was free to get!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s