• Adobe After Effects

  Text data from excel to update as video overlay based on Timecode?

  Posted by Susan Dempster on August 30, 2013 at 2:35 pm

  I have data in an excel sheet that I need to appear as a text overlay on a video. For each second of timecode, I have specific data. In the excel sheet, I have generated each second of timecode in one column with the text that needs to appear at that second in another column.
  Is there any automated way of importing and displaying this data? Any plug-ins or 3rd party software that can read from my xls and import to After Effects? I also posted this in the Premiere Pro forum – I hope that’s ok!
  Thanks in advance for any ideas!

  Jaakko Rinne replied 10 years, 2 months ago 6 Members · 14 Replies
• 14 Replies

• 

  Walter Soyka

  August 30, 2013 at 4:31 pm

  Are closed captions in Premiere Pro [link] an option?

  If not, I’d do this with an expression, but it won’t be totally straightforward.

  I’d modify the Excel sheet such that its output was a Javascript array definition, then parse the array in an expression in the Source property for a text layer to display the right text at the right time in Ae.

  Walter Soyka
  Principal & Designer at Keen Live
  Motion Graphics, Widescreen Events, Presentation Design, and Consulting
  RenderBreak Blog – What I’m thinking when my workstation’s thinking
  Creative Cow Forum Host: Live & Stage Events
• 

  Susan Dempster

  August 30, 2013 at 5:28 pm

  Thanks Walter, but you lost me at Javascript… : (
  In that case, maybe a better questions is, “Does anyone know of a developer I can hire to do this for me?”
• 

  Walter Soyka

  August 30, 2013 at 5:42 pm

  [Susan Dempster] “Thanks Walter, but you lost me at Javascript… : ( In that case, maybe a better questions is, “Does anyone know of a developer I can hire to do this for me?””

  I can help — this shouldn’t take more than an hour — but I would not be able to take a look at this until later tonight. My contact information is here [link] on the COW.

  You might also try the After Effects Expressions forum [link] in case someone else already has a ready-made solution or is willing to take this on.

  Walter Soyka
  Principal & Designer at Keen Live
  Motion Graphics, Widescreen Events, Presentation Design, and Consulting
  RenderBreak Blog – What I’m thinking when my workstation’s thinking
  Creative Cow Forum Host: Live & Stage Events
• 

  Erik Waluska

  August 30, 2013 at 5:46 pm

  There’s a script that creates AE comps from spreadsheets. I know that’s not quite what you need but it seems like it wouldn’t be much of a leap for that developer to make something for your needs.

  CompsFromSpreadsheet script

  Erik Waluska
  EAWmedia
• 

  Susan Dempster

  August 30, 2013 at 5:51 pm

  Thanks so much to all of you for your help – I’ll investigate further!
  Cheers!
• 

  Susan Dempster

  August 30, 2013 at 5:51 pm

  Thanks!!!
• 

  Kevin Camp

  August 30, 2013 at 5:56 pm

  try this…

  copy the info from the excel sheet (just the text you want on the screen, not the timecode) and then paste it into a text layer in after effects.

  then select the source text property for that text layer and choose animation>add expression and paste this expression into the expression property:

  line = text.sourceText.split('\r');
line[Math.floor(time)]

  the expression should tick each line every second.

  would something like that work?

  Kevin Camp
  Senior Designer
  KCPQ, KMYQ & KRCW
• 

  Walter Soyka

  August 31, 2013 at 2:35 am

  Here’s a very quick and dirty solution.

  In Excel, build a three-column arrangement as follows, with the first column as the in point (hours, minutes, seconds, frames with columns in between) for your text, the second column as the out point, and the third column as the text to appear:

  0:00:01:15 0:00:02:00 Text set 1
0:00:02:00 0:00:04:00 Text set 2
0:00:05:00 0:00:08:00 Text set 3

  Select these and copy them to the clipboard.

  In After Effects, make a new text layer, then paste in the Excel data. Twirl open the text layer in the timeline panel, revealing the Text Source property. Alt-click the stopwatch, and enter the following expression:

  function timecodeToTime(input) {
timebase = Math.round(1/thisComp.frameDuration);
inputTimecode = input.split(":");
return inputTimecode[0]*3600 + inputTimecode[1]*60 + inputTimecode[2]*1 + inputTimecode[3]/timebase;
}

  textLines = text.sourceText.split('\r');
  result = "";

  for (i=0; i < textLines.length; i++) { t = textLines[i].split('\t'); adjustedTime = time + thisComp.displayStartTime; if ( (adjustedTime >= timecodeToTime(t[0])) && (adjustedTime <= timecodeToTime(t[1])) ) { result = t[2]; break; } } result

  It's not perfect, but it might do the trick.

  Walter Soyka
  Principal & Designer at Keen Live
  Motion Graphics, Widescreen Events, Presentation Design, and Consulting
  RenderBreak Blog - What I'm thinking when my workstation's thinking
  Creative Cow Forum Host: Live & Stage Events
• 

  Walter Soyka

  August 31, 2013 at 2:40 pm

  I have updated the expression above. Part of it went missing in my original post, because a less than sign was interpreted by the forum as an HTML tag. Looks good now.

  Walter Soyka
  Principal & Designer at Keen Live
  Motion Graphics, Widescreen Events, Presentation Design, and Consulting
  RenderBreak Blog – What I’m thinking when my workstation’s thinking
  Creative Cow Forum Host: Live & Stage Events
• 

  Susan Dempster

  September 2, 2013 at 4:12 pm

  Hi Walter,

  First off, I appreciate your help and your time more than words can express!

  Unfortunately I must be doing something wrong…
  If I follow your directions, I get an error message:

  “After Effects warning: Class ‘Array’ has no property or method named ‘1’ Expression disabled….”

Reply to this Discussion! Login or Sign Up