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

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.

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.

Initial AE Comp Window

And your initial project window something like.

Initial AE Project Window

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.

Excel Category Setup

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.

Final AE Project Window

AE Lower Third Demo Comp

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.

68 Comments »

  1. TJ Walker said

    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

  2. Dale said

    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

  3. 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

  4. Allan W. said

    Most excellent – many thanks! I’ve needed this capability for a while.

  5. Thanks a lot for this! Really, really useful and has already saved me hours of work..

  6. Aleandre Teles said

    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….

  7. Dale said

    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

  8. Tim said

    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!

  9. Dale said

    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

  10. Alexandre Teles said

    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

  11. Dale said

    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

  12. Alexandre Teles said

    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

  13. Jeff McBride said

    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!

  14. Dale said

    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

  15. Jeff McBride said

    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.!

  16. Dale said

    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

  17. Dale said

    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

  18. Max said

    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

  19. Dale said

    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

  20. Aaron Cobb said

    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?

  21. Dale said

    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

  22. Andrew said

    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

  23. Dale said

    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

  24. Tom said

    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!

  25. Dale said

    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.

  26. Nick said

    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.

  27. Dale said

    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

  28. [...] ow to use a spreadsheet to generate Lower Thirds, Slates, Titles, etc. in After Effects [After Effects] [...]

  29. Jared said

    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 ;)

  30. Stephen said

    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

  31. Jason said

    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!

  32. Steve B said

    This has saved me at least 3 days of work, i just wish id found it three days ago.

    Thanks very much for sharing it.

    Cheers!

  33. Lloyd said

    Dale,

    I’m wondering if what you have be able to be used to help be generate program lineups for bumpers? What do you think. I’m generating a tab delimited file and then need some way to import the text into FCP and I can use AE if I need to.

    Thanks for any advice.

    Lloyd

  34. Dale said

    Hey Lloyd,

    Yes, that’s exactly the kind of task this script was designed to facilitate. It’d be fairly straightforward to write an XML transform for a text generator in FCP also, but I’ve got to admit that my plate is pretty full these days and you’d need to find somebody else to assist you for that project.

    btw. the script above uses comma separated values so you’d need to adjust for TSV by finding an alternate JS parsing function or re-export your list.

    Dale

  35. [...] avec des calques textes reprenant les intitulés de la ligne 1 du tableau. (fig. 2) Grâce à ce super script, chaque ligne du tableau est convertie en une composition que je n’ai plus qu’à rendre. (fig. 3) [...]

  36. Jeannette said

    Hi Dale,

    I enjoy using your script, and I have a question for you. How difficult would it be to add to it so that you could add a column in Excel that points After Effects to an image file, like a Jpeg, and replace it automatically also?

    Thanks! :)
    Jeannette

  37. Hey Jeannette,

    It would be pretty easy to write a custom script to handle something like that, but I’m not sure how to make it generalizable without knowing which specific image and which specific column you were going to replace because you’d have to loop through all of the layers to match the header.

    If you were to write a custom script you’d match a specific header and replace a specific graphic in the flow of the script execution. If you need help with something like that give me a shout at dale[at]creative-workflow-hacks[dot]com and with specifics and I’ll see if I can help you out.

    Best,

    Dale

  38. Nikitas Mikas said

    Hallo
    the tutorial is very very good!!!
    BUT IF CAN HELP ME
    I need an extra field in order to place a graphic layer beside the text.
    The graphic layer will be selected from a group consisted of 10 (tga sequences)
    which can be in the same Comp and be visible or not. OR replaced

    So the idea is to have a script or expression which will ALSO TEXT CONTROL
    the visibility of a layer in the comp.

  39. Seb J said

    Hi there, I’m looking to use this script to aid quickly changing Text layers for Questions and Answers from an Excel file (for a quiz) in an After Effects comp. I have gotten to the stage of where your guide states:
    “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 try to run the script from within After Effects, I get the error message “Unable to execute script at line110. Fuction fileGetDialog is undefined”
    I’m a complete novice at Javascript, this is the first time within working with it so apologies if the question seems a little basic! I have tried googling an answer but cannot find a response!
    Any help would be much appreciated. Thank you

  40. Dale said

    Hey Seb,

    Sounds like you are moving in the right direction. What version of After Effects are you using?

    Best,

    Dale

  41. Marcus said

    Hi Dale

    Thanks for writing this script, it has saved me so much time over the years.

    But I am having the same issue as Seb, the script works fine in CS3 and CS4, but in CS5 I get the “Unable to execute script at line110. Fuction fileGetDialog is undefined”
    Any ideas on what it could be, as i use this great script a lot.

    Cheers

    Marcus

  42. Dale said

    Hey Marcus,

    It looks like the file access method I was using has been deprecated in CS5. I’ll need to update the script. Should be a quick fix but I need to carve out a bit of time to rewrite a small section. Thanks for your patience while I get this set up.

    Dale

  43. Dale said

    Hey Guys,

    I’ve updated the script for AE CS5. Please give me a shout with any issues introduced with this change.

    Dale

  44. MDR said

    Thanks a real time saver!!

  45. [...] Dale Bradshaw developed a script (CSV to Text Layers) posted at his blog Creative Workflow Hacks in How to use a spreadsheet to generate Lower Thirds, Slates, Titles, etc. in After Effects. It was recently updated for CS5.Also, Harry Frank and others developed expressions for similar [...]

  46. MArcio Mendes said

    Amazing Script, but where i can find the cs5 version, i download the one from the 2007 post and it keep saying: ” i can find text layeers that match with name x or Y”

  47. Dale said

    Hey Marcio,

    I updated it for CS5, but it may be that I missed something. Want to send your AE project file (sans support image and videos, etc) and the CSV file you are trying to use?

    Thanks,

    Dale

  48. Met said

    Great job, man!
    I was wandering if there is a way to change a textLayer type from “point” to “paragraph” (and vice-versa) via JavaScript, after creating it with “addText(…)”.
    I’ve googleing for a while but couldn’t find anything close to the topic.

    Thanks,
    Met

  49. Mark Grody said

    Dale,
    I’ve tried to do this and I can’t find the option to CSV to Text layers.jsx and I can’t find it when I open scripts. What can I do? thanks

  50. Dale said

    Hey Mark,

    The easiest way is to download the zip above, unzip it, move the script to the scripts folder in your After Effects application folder. Restart AE. Under the scripts menu the script should then be available. Can you try those steps and write back if you have problems?

    Dale

  51. Eric Santiago said

    Just tried a simple csv made from Google Docs.
    Tried it on CS 5.5 OS X.
    Errors are:
    Unable to execute script at line 212. undefined is not an object.
    Ill try making one from Excel on Windows when I get a chance.
    Thanks for updating this script.
    Stopped using it since we moved to CS5.

  52. Dale said

    Thanks Eric for the bug report. If it continues to be a problem can you send me a version of the file? I’d like to keep this script current.

    Dale

  53. Math Notermans said

    Sounds great…

    Actually allready got a jsx-Photoshop script running that auto generates a PSD, exports PNGs and a preview JPEG for 21 languages for projects we do. For now i got an array with all the texts in jsx… Next step is using CSV files from excel to making it even better… Gonna try and use your technique for that ;-)

    Thx..

  54. Martin said

    Can anyone tell me how to put a newline inside the quotes in the csv file?

  55. admin said

    Hey Martin,
    I’m not in front of a production machine right now, but can you try using “line1\nline2″ and see if that works.

  56. Miroslav said

    Hello sir,
    i have a problem with importing CSV to After Effects, that is i can’t import Cyrillic characters.
    I have set the options in exel to use Latin Cyrillic(that’s what i need)
    but when i import the file in AE it gives ? sign instead of Ć and letters like that.

    Can you please help me with this.

    Kind Regards,
    Miroslav

  57. admin said

    Miroslav,

    That’s a great question. It must be something to do with the encoding. We probably want it to be UTF. Do the characters look correct in the .csv file? I’m interested in where the translation is getting munged.

    Dale

  58. Justin said

    Hello,

    I am currently using AE cs6. Is there an updated script as I noticed the above is for cs3 or earlier versions than cs6. I am exactly trying to accomplish what your above example does for lists that contain 1000′s of names. I am also wondering if there is a way that I can then render each layer into its own .mov file? Would I need a different script for that?

    Thanks,

  59. admin said

    Hey Justin,

    The script was updated for CS5, are you having new problems in CS6?

    Dale

  60. Shir said

    Hello Dale! I read all the comments here, still today, when I’m using CS5, it doesn’t recognize my headlines in order to import the text information…What can be the problem?

  61. admin said

    Hey Shir,

    Can you send me the .csv that you’re having problems with to dale[AT]creative-workflow-hacks[DOT]com? I’m away from a production machine but I can take a look on Monday.

    Dale

  62. Hey Dale,

    I’ve recently found your script and I’m trying to use in the After Effects CS6, but I’m getting the following errors:

    “Couldn’t find matching text layer for CREDIT” (my first row is “CREDIT, NAME”)

    and then

    “Unable to execute script at line 212. undefined is not an object”

    Any advice? Thank you!

    • admin said

      Hey Gillian,

      It’s a little hard to visualize what is going awry. If you want to package up your .AEP project file along with your CSV and send it my way at dale[AT]creative-workflow-hacks(dot)com, I’ll be happy to take a look.

      Best,

      Dale

  63. Fede said

    Hey great site. I have a problem and I was hoping for a solution.

    I have a lot of time markers in an excel file how can I transfer that to my timeline?

  64. admin said

    hey Fede,

    You have a list of time codes/frames and you want to make new markers on your timeline?

    Dale

  65. DionV said

    Dale,

    Thank you so much! This script is great. Seems to work well in AE CC, too.

    Any suggestions on where to look in order to modify the script so that it will determine the size of the text layer and the size of the text to be inserted, then adjust the size of the text so that it will fit within the text layer?

    Thanks!

    DionV

  66. Jacob Canon said

    I’ve been looking for this fix for a while. I saw in one of the last comments from DionV that is was working well in AE CC. I’m curious if this is the same original file or an updated version? Also is it possible to send me the script link via my email?

    Thank you for your time.

    Jacob

RSS feed for comments on this post · TrackBack URI

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="" highlight="">