CompsFromSpreadsheet
Version: 1.21 Sign up to be notified by email when a new version of this script is posted
Save As Tab Delimited File
The compsFromSpreadsheet script allows you to make multiple versions of your compositions using data from a spreadsheet or database. The script goes through your spreadsheet line by line, making a new version of your comp with text layers in the composition receiving text from the spreadsheet automatically.
Before After Effects can read your spreadsheet, it needs to be saved as a tab-delimited text file (most spreadsheet and database programs do this easily). This is a universal text format that will allow After Effects to read in your data and organize it by rows and columns.
The first line of your file needs to be the heading names for each column, this will allow you to sync up columns in your spreadsheet with text layers in you composition.
Spreadsheet Heading
It is very easy to set up your compositions to be used by the script. To enable a text layer to be targeted by the script, you need to change the text so that it begins with a caret: ^ (shift 6). For instance, you may have a text layer that you want to receive phone numbers from the spreadsheet. In the spreadsheet the phone numbers have a heading named phone. So you would change name of the layer to ^phone.
Use a ^ on layers to be used
When the script runs, it will ask which compositions in your project are going to be used as templates by the script. If you are using more than one comp you need to have a column in your spreadsheet that lists the name of the composition to be used. If you create a folder called ‘TEMPLATES’ the comp select window will be bypassed and the program will assume that all the comps in this folder are to be used. You add the comps you want to use by double-clicking on the comp name.
Create a column for Comp to be used as template
See the sample spreadsheet for an example of how to do this (they are under the Core Spot heading).
The script will then ask you to locate the tab-delimited text file you created from your spreadsheet.
Choose tab delimited file
When the script reads the first line (the heading line) it will pop up a window which will allow you to assign columns in your spreadsheet with text layers in your compositions, you do this by clicking on the dropdown box underneath the column name. The dropdown will contain the names of the text layers you created with the ^ but you wont see the ^, for example, the ^phone text layer we created will appear as phone in the dropdown box.
Assign column headings to layers
(If you have a really long label, only the first 15 characters of the label will show up in the dropdown)
If you choose to use more than one comp you will have to assign one of your colums in the spreadsheet to give the script comp names. This appears at the very top of the dropdown box as COMP. If you only have 1 comp you are using for the script, COMP will not be in the dropdown, and the script will duplicate that comp for every line in the spreadsheet.
When you click ok the script will begin the grunt work. It will go through each remaining line in your file. For each line it will make a duplicate of the composition referenced by the COMP column of that line, and any text layer in the comp that has been synced to a column in your file, will have the text updated with the text from that line of the spreadsheet. The script will rename the composition to the line number of the file, so the first comp will be named 2 (because line 1 is your headings).
Comps created by script
If there are any typos in the spreadsheet the script will allow you to correct it during execution.
Typo in spreadsheet
The comp is put in the render queue with default settings and output module, the location of the render is a folder named renders in the same directory as your current project (it’ll create it if not there) so save your project file on the drive you’d like your renders to be.
Version History
- v1.21
- Fixed a bug in the program that caused the script not to run in certain situations. Previously, the script would try to create a folder called “Renders” in the same directory as your project to put your rendered files. The script no longer does this. Now there is a button in the field init window which allows you to designate a folder for renders. If you do not choose one, renders will go wherever your render que is set to put them
- v1.2
- The comp select window now uses scrolling lists to accommodate projects with a lot of comps. The way it works now is there is a list of all comps in the project on the left side of the window. Any comp you want to use, double click on it and it will go the the list on the right. The list on the right is all the comps to be used by the program.
- If you create a folder called ‘TEMPLATES’ the comp select window will be bypassed and the program will assume that all the comps in this folder are to be used.
- v1.1
- Added the ability to choose the naming convention of new comps. Previously the comp was always named the line number of the database, now you can choose if you want line number, original comp name (with an incrementing number), or sync it up with a column in your spreadsheet.
- Fixed a bug in the new version check.The function is only supposed to run once every 10 days, but the function checked every time the script was run. This has been corrected.
- v1.0 Initial Release
Follow on Twitter
Stay updated via email
This is a very handy script. I recommend that you add a comment to After Effects Help to tell people about this script in the context in which they will most likely need it—such as here:
http://help.adobe.com/en_US/AfterEffects/9.0/WS3878526689cb91655866c1103906c6dea-7cf2a.html
Very nice script Mike – Good work !
It would be nice if you also could set the comp. / file name and not just have numbers.
In the current script version I have to open all the movies and rename them afterward.
>kenneth
what do you think about having a dropdown box when you select fields that gives you the choice of having the new comp named either [line number, original comp name and a unique number, database field...].
do you think that would make the field init more confusing?
Mike Cardeiro
Hey Kenneth,
I have updated the script to allow you to change the way the script renames compositions. Now there is a dropdown box underneath where you sync fields with text layers.
this dropdown allows you to have the comps named by either line number (the way it used to), original comp name, or you can have the comp named from a field in your spreadsheet
Mike Cardeiro
Thanks Mike for updating with support for setting comp name.
Haven’t tested it yet – but a dropdown box sounds fine to me.
>kenneth
Ok, so the project I’m currently working on has a TON of comps, so when I go to use the script, the list is so long that I can’t select the comp I want to use AND hit select. Now, I have them organized in folders, so is it possible in a future release to have the comp template selector recognize these folders as well?
Sounds annoying! I’ll get out a patch in the next couple of days.
Hey Nathaniel,
I updated the script so now the select composition window should be easier to manage if you have a lot of comps.
You can also bypass this window altogether if you put all the comps to be used in a folder called ‘TEMPLATES’ (all caps), then the script will use these comps and not ask you.
Mike Cardeiro
Thanks a ton Mike! I’ve really enjoyed using the script… it’s made my workflow a ton quicker!
I am having a problem with the script. Once I set up the layers to correspond with the drop down menu items, it asks if I want to do the work in a copy of the project, I say no and it saves. Then the script does nothing. It doesn’t create a render queue and it doesn’t make more comps. Am I doing something wrong? I am working on a intel mac in AE CS4. Any help would be appreciated as I am working on time sensitive material.
Kyle,
try saving a copy see if your results are different. If not, forward me a copy of your project and tab-delimited text doc at mcardeiro@yahoo.com and I’ll see whats up
Mike Cardeiro
I have been working with Kyle on solving his problem. He now has a working version of the script. It seems to be a problem with the script creating the “Renders” folder to put your renders in.
I am working on a real patch, in the meantime, if anybody has the same problem, email me mcardeiro@yahoo.com and I can get you the temporary patch that I gave Kyle.
Mike Cardeiro
there is now a new version of the script that fixes the issue. The way the script previously worked was that it tried to create a folder named “Renders” in the same directory as the script. This worked for most people but failed for some.
The script no longer creates this folder. Now there is a button on the “field init” window where you can choose the folder to render to. If you dont choose a location, it will put the rendered files wherever the last render went (just as if you added a comp to the render que on your own).
Hopefully this is the last bug!
Mike Cardeiro
Hey guys I just wanted to let everyone know how great Mike has been to work with. He is dedicated to getting this thing working and I really appreciate his hard work.
I just want to add too, I had the same issue as kyle, and Mike got back to me within the hour with a new and perfectly working script. seriously, hours of work in minutes. minutes! this is amazing. thank you so much for your hard work.
Request: Have the next version with a column in the SS for a project folder name (source containing assets), and a field for the names of those assets.
The script inserts assets from the source folder into each new comp, based on asset names in the spreadsheet asset field.
Excellent work, very inspiring. THANK YOU!
Hey guys !
This script is really usefull, very good job !
I’m french and I’ve got problems with accent. Is it possible this script work in utf-8 ?
In speaking with Jeff, the problem he was encountering had to do with creating the tab-delimited text file on a PC and opening it on a mac for use with the script, somewhere in the process the accents in the text doc got mangled.
How do you remove all the extra empty cells when saving to a tab-delimited text file?
Never mind, I was able to remove the extra fluff from the text doc!
Too bad Excel can’t export to tab-delimited and keep comma’s for city,state separation.
this seems to be a bug with excel. There are 2 different delimited file types, tab-delimited and comma-delimited. Now if you were exporting something comma-delimited, you obviously would not want commas from your field to make it into a comma-delimited doc because this would cause the program you are importing into to think the comma is the start of a new field rather than just a comma.
I believe Microsoft in their infinite wisdom is using the same code in both tab-delimited and comma delimited docs and stripping out commas when it is not necessary, and actually wrong to do in a tab-delimited doc.