How to use a spreadsheet to generate Lower Thirds, Slates, Titles, etc. in After Effects
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.
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.
UPDATE 08.16.2007
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.
Source JSX script
Downloadable Zip Version
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
(?=,|\\t|$)
which matches on 3 conditions, a positive lookahead assertion for a comma character, a tab, or $ which is the end of string character. It was failing on the $, so I substituted a \Z to match the absolute end of the string, which matched. A ha! so let’s just substitute a negative look behind assertion(?<!,) for the comma and we’re set. Hmmm, bummer, there is no negative look behind assertion in Javascript Regex. So for now, we do a positive look behind assertion with (?<,)$ and deal with it after the match. There are a few workarounds, but none that seem to be foolproof, so for now we just use a substring to chomp the leading comma if the match field starts with a comma. Not great, but it mostly works.
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.
TJ Walker said,
August 18, 2006 at 9:35 am
Awesome!. Took some fiddling on our part to get the CSV formatting right but worth every minute of the effort. Had to make sure the font was all the same style, format etc and all our problems went away. This is exactly the sort of thing we’ve been looking for. Thanks for the article
Dale said,
August 18, 2006 at 12:12 pm
Thanks for the feedback TJ….
I’m in the middle of some crazy element tapes myself, so I’m eating my own dogfood with this script. I made about 500 slates with countdowns in a fraction of the time I used to need.
Dale
Jonas Hummelstrand said,
September 12, 2006 at 4:42 pm
Dale,
Thanks for the great script, it will save me hours! Keep up the good work!
Thanks also for the link to my humble blog in your sidebar!
— Jonas
Allan W. said,
March 19, 2007 at 1:52 pm
Most excellent - many thanks! I’ve needed this capability for a while.
Eivind Lie Nitter said,
March 30, 2007 at 6:06 am
Thanks a lot for this! Really, really useful and has already saved me hours of work..
Aleandre Teles said,
May 23, 2007 at 3:31 pm
thaks a lot, it helps me a lot
but how can i change to 3 lines instead of 2 lines
i´m editing a music presentation and i need to make about 16 slates with the name of the music, the compositors and the singer….
Dale said,
May 26, 2007 at 11:12 pm
Hey Aleandre,
You should be able to place your titles on as many lines as you like as long as you have a corresponding field in the .csv file as described above. You’d just have 3 columns in that file to accomodate the 3 lines.
Best,
dale
Tim said,
May 31, 2007 at 12:17 pm
Have you published the FCP version yet? I’ve been looking for it - this would be the perfect thing for a project I’m working on and I’d love the advice! Thank you so much! If nothing else, I now know it’s possible and I’ll be tinkering to find a way on my own…thanks again!
Dale said,
May 31, 2007 at 12:22 pm
Hey Tim,
No, I haven’t posted the FCP version of this script, I’m really juggling too many balls between personal projects and a crazy work schedule at the moment, but you’re right it’s a pretty straightforward project on the FCP side due to the XML support. Good luck.
Dale
Alexandre Teles said,
June 13, 2007 at 4:09 pm
Dale,
once again, thank you very much for this script. i´m using it very much, it made my work a lot of faster…
but i´m having a little problem, can you change from using a (,) to separate values to a (;)???
some lines i need to separate the names using a (,) . like when a music has more than one writer…
i´m a designer… not a programmer.. and to tell you the truth, i don’t know nothing about scripts…. i´m a underpaid public tv motion graphic designer who doesnt know nothing about writng codes, even html i hate!!….. lol…
again, thak you so, so much… you are one of my heros!!
ps: sorry for the bad English
Dale said,
June 13, 2007 at 4:51 pm
Alexandre,
I can’t change the comma delimiter, that’s an industry standard, but you should be able to have commas in the data. When I export from Excel, it uses this format…
a, b
“first, last”, “second, third”
so I have column a and b and the data when I run my script is two text items one with the contents
first, last
and the second with the contents
second, third
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.
so ” “a strong candidate” ”
looks like
a strong candidate
but
“a “strong” candidate”
looks like
a “strong” candidate
hope that helps and thanks for the kind words.
Best,
Dale
Alexandre Teles said,
June 29, 2007 at 4:55 pm
Dale,
once again, thanks very much!!
i´ll try do the csv in excel, i was using an word processor.
Thank you for your patience and for you help
keep the excellent work
AT
Jeff McBride said,
August 7, 2007 at 10:45 am
I want to do a massive list but there seems to be a limitation with the counting- it only goes to 099 and then stops adding the numbers on the front.
Also would it be possible to tweak it so that instead of creating new comps it creates new text layers? Thanks for the help!
Dale said,
August 7, 2007 at 10:58 am
Hey Jeff,
There should be a check to go over 100 elements. I’ll have to check and see if it’s failing some how. If you want to add a bunch of text areas instead of comps just make a single really long row, it will create each column in the CSV.
Best,
Dale
Jeff McBride said,
August 7, 2007 at 11:16 am
Hey Dale- Thanks for the quick response! I’m not seeing a checkbox for going over 100- is that what you’re referring to? I’m using AE 7 Pro if that helps. As far as making a single really long row, is that referring to the text box or to the csv? Thanks again.!
Dale said,
August 7, 2007 at 11:49 am
Hey Jeff,
nope, there isn’t a checkbox, this bit of code
//pad and name comp, we could put a nice interface to the name of the comps in the next version if(elementLength < 10){ compElementName = "0"+x + "_"; }else if(elementLength < 100 && elementLength >= 10){ //pad if(x < 10){ compElementName = "0"+x+"_"; }else{ compElementName = x+"_"; } }else if(elementLength < 1000 && elementLength >= 100){ //pad if(x < 100 && x >10){ compElementName = “0″+x+”_”; }else if(x < 100 && x < 10){ compElementName = "00" + x+"_"; }Is supposed to pad the name of elements up to 1000 layers. It sounds like it’s not working in your case and I’m not in front of an AE setup to test right now. I’ll need to check it out. If you want to send your CSV and AE file to dale(at)creative-workflow-hacks(dot)com that should help me understand where it’s going awry.
The long row was in the CSV, but maybe I’m misunderstanding what you want to do. Do you want the text layers to stack in a single comp at the same position?
Dale
Dale said,
August 7, 2007 at 1:50 pm
So, Jeff did catch a bug in the script. I’ve uploaded a new version that should handle between 100 and 1000 layers correctly. After that, all bets are off. grin.
Dale
Max said,
August 9, 2007 at 7:10 am
Hi!
Just tried the (otherwise brilliant) script in AE CS3, and I only get the message “Couldn’t find matching text layer for” . Nothing more.
Works ok in AE 7.
Have an idea why?
/Max
Dale said,
August 9, 2007 at 8:59 am
Hey Matt,
I’ll have to take a look. There are definitely some changes in AE CS3 vs AE 7 as far as scripting, but I haven’t done an audit of the whole library. Hopefully, it’ll be an easy change.
Dale
Aaron Cobb said,
August 15, 2007 at 1:54 pm
Is there some compelling reason to use CSV’s instead of a tab-delimited file? I’m working on a versioning script of my own, and it just seems like tab-delimited text is cleaner and simpler to work with. Is there some special case I’m missing?
Dale said,
August 15, 2007 at 3:01 pm
Hey Aaron,
No, no really specific reason to favor CSV over TSV, other then it was easier to debug because I didn’t need to turn on invisibles to make sure I had a single \t in my text editor. Ideally, I would have prefered XML, but to the best of my knowledge there isn’t a native AE JS XML parser and I haven’t found a JS XML parsing library that I’m happy with.
If you end up developing a TSV script, send a link my way and I’ll add it to the post for folks that would prefer a tab-delimited solution.
Best,
Dale
Andrew said,
August 16, 2007 at 9:00 am
Hi,
Do you find the reason to AECS3 error?
This is a amazing solution, but will
be very interesting using this on AECS3
Thanks
Andrew
Dale said,
August 16, 2007 at 9:13 am
Hey Andrew,
Haven’t had a chance to dive into CS3 updates for my scripts yet, but this one will be top of the list. Keep an eye out for it.
Dale
Tom said,
October 17, 2007 at 10:08 am
Hi,
I really need something like this for FCP! If you haven’t got time to document it fully, if you could give us some clues as to how to get going, I’d really appreciate it!
Dale said,
October 17, 2007 at 10:24 am
Hey Tom,
I’ve had an FCP version of this script on my backburner forever and just haven’t put the resources towards it to get it out the door. sorry.
If you’re looking for an already baked solution. Automotion from Digital Heaven is pretty sweet.
If you’re looking to roll your own, you’ll want to use FCP-XML to populate one of the text-generators in FCP. The lower third generator is pretty limited, but you can write your own or modify one of the freeware fxscript plugins pretty easily to get the text setup.
You’ll need to parse the spreadsheet. There are a lot of csv, tsv, and xml parsing options depending on what language you write the script in and then iterate over that data to write out xml. You’ll probably want some inputs for placement and length and that’s about it. Not too hard if you’ve got a bit of a scripting background.
Nick said,
January 22, 2008 at 4:25 am
Hey,
I am using your script at the moment to populate about 20 text fields
with numbers, I don’t know if we can avoid this but we need the Decimial Point to be the Paragraph alignment but AE doesn’t have so much support for this.
So we have a mostly automated import however alignment for decimal point is a problem and also kerning.
By Decimal Point its working with numbers like 80.9 and 100.9 etc and the alignment would like to be of the decimal also the kerning info well i don’t know if you have any suggestions but i think thats going to have to be done by hand.
Dale said,
January 22, 2008 at 9:55 am
Hey Nick,
I’m pretty sure that’s a limitation of AE Text Fields at this point, maybe somebody else has some idea of an easier workaround.
Dale
Motion graphics tutorials | Zen of Film said,
April 28, 2008 at 9:58 am
[...] ow to use a spreadsheet to generate Lower Thirds, Slates, Titles, etc. in After Effects [After Effects] [...]
Jared said,
April 28, 2008 at 9:39 pm
Dale, thank you SO much for sharing this. I am working on the title cards for the SIGGRAPH 2008 Computer Animation Festival and OMG, you just gave me my weekend back! A really powerful workflow (and ensures that everything makes it on without typos or miscopies).
I’m just getting into this stuff (scripting)…but this kind of application really makes me want to learn jump right in. We’re starting to use more scripts at my workplace (for workflow and rendering) - hopefully I’ll learn more and add to that pipeline.
Again, HUGE thanks! Hopefully I’ll contribute in the not-so-distant future
Stephen said,
July 9, 2008 at 11:24 pm
This is a great tool! It will certainly speed up my work replicating lowers and such!
I was searching for something similar to this. Andrew Kramer of http://www.videocopilot.net just put out some “Breaking News” FX templates and I started to think about how to get the text fields (i.e.–stock symbols, headlines) to “tick”. Meaning, the fields animate and the text replaces within the same Comp.
Not being ungrateful or trying to push for additional features, just a thought for how this could be expanded. Of course, I’m not a script writer/programmer, just a designer.
Again, thanks for this tool!
Referenced URL: http://www.videocopilot.net/blog/?p=208
Jason said,
October 10, 2008 at 11:18 am
This script is greatness! Is it possible to add code that will add the new comps to the render que, with a render template already chosen? I also read a note that said there my be a naming UI added at some point. That would really be sweet too.
Thanks again!