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

[Walter Soyka]

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

[Susan Dempster]

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!

[Nathan Dean]

Anyway to do this based on comp markers??

[Jaakko Rinne]

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.