How to use a spreedsheet to generate Lower Thirds, Slates, Titles, etc. in After Effects

How to Data Bind to an RSS Feed (Blog Posts) in Windows Forms

A good database and workflow architecture can really maximize your productivity. But the bottom line is that most collaborative work gets done in a piecemeal fashion either by email, cut and paste or sending documents back and forth to clients and collaborators. I’m sure you’ve run into a situation where you find your self cutting and pasting between an Excel document into a text field while making Lower Thirds, Slates, Titles or other text-centric project. In this article I’ll explain a workflow to automate creating Lower Thirds in After Effects and I’ll follow up with an article on how to do the same thing in Final Cut Pro shortly.

We’ll start with our spreadsheet. Spreadsheets are the kitchen sink of data organizers. My copy of Excel has 31 different formats in the File:Save As dialog. There are a few formats that I use most often though. When I need to munge data with the native version .xls file, I turn to the PHP Pear class Spreadsheet_Excel_Writer and PHP-ExcelReader. I’ve also been investigating the new XML spreadsheet format and am leaning toward using it for the Final Cut Pro version of this project. Finally, I’m going to use a pretty straight forward format for the After Effects part of this project. We’ll use a CSV file. Short for Comma Separated Values, a CSV file gives us a really straight forward approach to our data. Since this task is not complicated from the data side it makes sense to use CSV because we can parse the format relatively easily in AE Javascript without taking a stop off at another scripting language for parsing.

(note)One note for developers. It may seem like all we need to do is split() the CSV, but because of the way commas and other characters internal to the fields are handled we need to run a set of Regex’s on the CSV. One known problem is a fully quoted string like “string” is stripped of quotes while a fully quoted string with leading characters like a “strong” candidate works. The Regex method is in the script, if you find any other anomalies or better Regex solutions give me a holler at dale(at)creative-workflow-hacks(dot)com.

So, I’ll walk through the process of using the script. First we need to create a comp that contains the text fields that you want to import. For our demo I’m going to use 2004 United States presidential candidates. Your comp setup might look something like this.

And your initial project window something like.

Keep the Text Layer text really simple. We’ll need to match the text layers text with the matching fields in our spreadsheet so we don’t want to complicate things. Be careful with spaces and symbols and check for these things if you have problems when you run the script.

Now we’ll move to our spreadsheet. Open up Excel,Open Office or your spreadsheet of choice and set up your data something like.

Notice how the first row of data matches the Text Layers in your After Effects document? This is a really important part of the setup. The script loops through the layers in your Comp looking for matches to the fields you set up in the first row of the spreadsheet. So if you have problems here is the first place to look.

After we set up our data, select File:Save As from your spreadsheet application and select CSV as the data format. If I open the CSV file in a text editor I get something that looks a lot like…


candidate,party
George W. Bush,Republican Party
John Kerry,Democratic Party
Ralph Nader,Independent Reform Party
Michael Badnarik,Libertarian Party
Michael Peroutka,Constitution Party
David Cobb,Green Party

Let’s move back to After Effects. Select the Comp you created as your template and then select File:Scripts and select CSV to Text Layers.jsx, the file you downloaded. From here point the script to the CSV file you created. When I run the script like so, I get output that looks like this.

The same techniques work great for Slates, Titles, Tournament Brackets…there really are a million uses for this kind of data munging. If you have a particular use it will be worth your time to build out a User Interface for your project, but you’ll find yourself using the same techniques in a lot of different ways. Hope you’ve found this useful.

Source JSX script
Downloadable Zip Version including Project Demo

Thanks to http://www.creative-workflow-hacks.com/2006/06/08/how-to-use-a-spreadsheet-to-generate-lower-thirds-slates-titles-etc-in-after-effects/ for this how-to.