UPDATE 09.17.2010 Updated for AE CS5
UPDATE 08.16.2007 Interim solution for AE CS3, see details at the end of this post.
I’ve been posting for a while about integrating databases into our workflow. 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.
(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.
Several people have emailed or left comments that version 1 of this script fails in After Effects CS 3, It looks like there was a change in the Regex engine for CS3 that was causing the last element of the CSV to not be found by the Regex which was causing the script to fail. I’m working with the AE team and some fellow scripters to try and figure out what is going on, but for now I’m posting an interim solution here.
This version is a bit of a kludge, because in AE CS3 the script is likely to fail if you use a comma in the initial match Text Layer and this wasn’t a problem in the AE 7 version, but it’s probably not a good idea to use commas in the match layer anyway.
MORE FOR ONLY THE EXTREMELY GEEKY
This was interesting technically. When I first started investigating the problem, I noticed that the Regex was failing on the last item of the CSV. I’m using this Regex
pattern = new RegExp("(^|\t|,)("*|'*)(.*?)\2(?=,|\t|$)", "g");
so I was immediately suspicious of this section
I’m still going to do some looking around to see if I can get a more elegant solution, but hopefully this will keep things working for now. Give me a heads up if it breaks anything.