ADOBE AFTER EFFECTS: Forum Expressions Tutorials Creative Cloud

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

COW Forums : Adobe After Effects

<< PREVIOUS   •   FAQ   •   VIEW ALL   •   PRINT   •   NEXT >>
Susan Dempster
Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 2:35:25 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!


Return to posts index

Walter Soyka
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 4:31:46 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


Return to posts index

Susan Dempster
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 5:28:41 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?"


Return to posts index



Walter Soyka
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 5:42:57 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


Return to posts index

Susan Dempster
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 5:51:43 pm

Thanks!!!


Return to posts index

Erik Waluska
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 5:46:42 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


Return to posts index


Susan Dempster
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 5:51:18 pm

Thanks so much to all of you for your help - I'll investigate further!
Cheers!


Return to posts index

Kevin Camp
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 30, 2013 at 5:56:37 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


Return to posts index

Walter Soyka
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 31, 2013 at 2:35:26 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


Return to posts index


Walter Soyka
Re: Text data from excel to update as video overlay based on Timecode?
on Aug 31, 2013 at 2:40:26 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


Return to posts index

Susan Dempster
Re: Text data from excel to update as video overlay based on Timecode?
on Sep 2, 2013 at 4:12:14 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...."


Return to posts index

Walter Soyka
Re: Text data from excel to update as video overlay based on Timecode?
on Sep 2, 2013 at 4:23:04 pm

Sorry -- posting on the forum has mungled a couple other characters in the post.

Try this:
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


This can be a couple frames off for dropframe timecode. I'll have a more robust solution later.

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


Return to posts index


Susan Dempster
Re: Text data from excel to update as video overlay based on Timecode?
on Sep 2, 2013 at 7:40:54 pm

Walter! I'll have to try it on a longer 1 hour section, but it seems to work like a charm on a 10 minute test - you're a genius!!! Wow, I'm so impressed!


Return to posts index

Nathan Dean
Re: Text data from excel to update as video overlay based on Timecode?
on Jul 9, 2014 at 9:17:30 pm

Anyway to do this based on comp markers??


Return to posts index

Jaakko Rinne
Re: Text data from excel to update as video overlay based on Timecode?
on Mar 2, 2016 at 2:09:20 pm

I'm resurrecting an ancient thread here, so sorry for that. First my thanks to the original posters: this thread helped me find a solution to my own work, which was adding temperature displays to an industrial video where we had measurement times and temperatures for multiple sensors in an Excel sheet.

The original data was 1 column with a timestamp and 1 column with temperature. I massaged that in Openoffice Calc to 1 column with start time in seconds, 1 column with stop time (that is just the next measurement time) and a final column with the temperature and a degrees Celcius sign. The data looks like this:

0 1.796 20,20°C
1.796 2.874 20,20°C
2.874 3.952 19,89°C
3.952 5.03 19,89°C
5.03 6.108 19,77°C

The tabs show up as spaces.

I modified Walter Soyka's original expression so that it uses the time in seconds instead a timecode format and ended up with this:
textLines = text.sourceText.split('\r');
result = "";

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

result


This expression is sadly quite slow for large-ish amounts of data. I have data from 9 temperature sensors with 1490 lines each. AE really doesn't seem to like this amount.

Thankfully my data only updates about once every second, so I created my composition at 2fps.

I hope my post helps if someone is looking for a solution to a similar Excel to After Effects problem.


Return to posts index

<< PREVIOUS   •   VIEW ALL   •   PRINT   •   NEXT >>
© 2017 CreativeCOW.net All Rights Reserved
[TOP]