I can automate it for you wholesale

I'm not much of a video editor. You need your short film cut? Yeaaaahhhh.... I'm not the guy. You need 10,000 video clips trimmed by exactly 1 second, then transcoded into 3 different formats, all automatically in the middle of the night? That, that I can do. Between Adobe Media Encoder (expensive, heavy, but effective) and a cron job watching a folder on the video server, most of my video editing is of the, "I'll kick it when I'm back at my desk" variety. This month though, things have been a bit different.

My current challenge is I'm working on a project that involves about 400 hours of interviews, all shot in the Doomsphere at the ICT. The interviews are cut into hundreds of individual responses, and that's where the problems come in. 400 hour long R3D clips isn't so bad, but 10,000+ individual clips requires some work. Suddenly, small choices matter, and sometimes, you accidentally torpedo the project schedule. Each clip's in/out points were delivered in an Excel sheet in HH:MM:SS. No fractional seconds, no frames. This was a problem. Thankfully, I had two tools at my disposal that have put us back on track.

First, know when to automate

To get those I/Os from Excel to a format our NLE could read was our first use of automation. I wrote a Python script that pulls in the Excel file, grabs the In, Out, and Clip ID, converts the HH:MM:SS to HH:MM:SS:FF, inserts an extra frame to the out to make sure the clip is at least 1 frame long, then drops the result in a text file ready for import to Vegas. Every time the timecode rolled over, split the output to a new file. Piece of cake, and saved hours of manually slicing up Excel sheets. 

This left us with 400 hundred tab delimited text files ready to be dragged into Vegas, but somebody still had to go through and adjust every single cut. We budgeted 2 interns for 3 months. We didn't think that'd be enough. We needed a better plan.

Second, know when to hire a professional

We posted a job for a video editor, a simple AE type job, meh pay, few perks, but all the coffee you could drink. We figured they could get it done faster than the interns, and would have a better eye for when to cut than a total novice. So we made a hire.

It took her just over a week to finish the first 60 hours. She got bored waiting for the farm to render more proxies and went back and reworked the first sessions. Then she labeled all of the audio glitches and started learning RX. That's why you hire someone who knows what they're doing. 3 weeks ahead of schedule, now we had to figure out how to get the new I/O's out of Vegas and onto our render farm.

Go with what works

The task was simple, open the Vegas project, open the edit details tab, select all of the regions, then copy them into a text file. My first thought was a custom Vegas script, or maybe using the SDK to launch the project and extract the data, but the SDK is a bit uh... thin on documentation. I was about to put the interns on it when somebody mentioned AutoHotKey on #gameaudio. So today, I exported the regions to text. Well, my computer did. I listened to BeyoncĂ©. My mouse danced over the Vegas interface, spawned Notepad++, named files, and waited patiently while I occasionally checked the output.

Okay, so how?

For the Python bits, I cribbed big parts of the technique from Al Sweigart's excellent Automate the Boring Stuff with Python, specifically the chapter on Openpyxl, which I've used for some other things. There's a Python module, Timecode, that is vital for this kind of work. As for the AHK script, it isn't perfect, but you can hit me on Twitter if you want a copy.