ADOBE AFTER EFFECTS: Forum Expressions Tutorials Creative Cloud

Getting Values from csv like LOOKUP in excel

COW Forums : Adobe After Effects Expressions

<< PREVIOUS   •   FAQ   •   VIEW ALL   •   PRINT   •   NEXT >>
Frederic Antoinette
Getting Values from csv like LOOKUP in excel
on Nov 16, 2018 at 4:39:48 am
Last Edited By Frederic Antoinette on Nov 16, 2018 at 4:45:47 am

So I have a table which would like that:



My Basic ideas would be to import it in AE as CSV
In my comp I will have
- the CSV,
- a Control layer with 3 sliders; Day, Session and Pres
- and two text layers, Title and Names

So by setting the values on the slider the Title and Names change accordingly
ex: if Day = 1 and Session = 1 and Pres = 1 then value for Title = "Title One" and value for Name = "Name one"

My table will have more than 50 rows so using a if or switch expression is not really an option.

Any help?


Return to posts index

Søren Storm
Re: Getting Values from csv like LOOKUP in excel
on Dec 6, 2018 at 6:57:55 pm

I stumpled upon this video, explaining how JSON files can be linked to your after effects project. Maybe they can be of use for you?







Return to posts index

Filip Vandueren
Re: Getting Values from csv like LOOKUP in excel
on Dec 9, 2018 at 1:39:51 pm

I made a mock table like yours in excel,
copied it all, and went to:
https://www.csvjson.com/csv2json

that gave me a JSON-string like this:

[{"day":1,"session":1,"pres":1,"title":"AAA","name":"aaa"},{"day":1,"session":1,"pres":2,"title":"AAB","name":"aab"},{"day":1,"session":1,"pres":3,"title":"AAC","name":"aac"},{"day":1,"session":1,"pres":4,"title":"AAD","name":"aad"},{"day":1,"session":1,"pres":5,"title":"AAE","name":"aae"},{"day":1,"session":2,"pres":1,"title":"ABA","name":"aba"},{"day":1,"session":2,"pres":2,"title":"ABB","name":"abb"},{"day":1,"session":2,"pres":3,"title":"ABC","name":"abc"},{"day":1,"session":2,"pres":4,"title":"ABD","name":"abd"},{"day":1,"session":2,"pres":5,"title":"ABE","name":"abe"},{"day":2,"session":1,"pres":1,"title":"BAA","name":"baa"},{"day":2,"session":1,"pres":2,"title":"BAB","name":"bab"},{"day":2,"session":1,"pres":3,"title":"BAC","name":"bac"},{"day":2,"session":1,"pres":4,"title":"BAD","name":"bad"},{"day":2,"session":1,"pres":5,"title":"BAE","name":"bae"},{"day":2,"session":2,"pres":1,"title":"BBA","name":"bba"},{"day":2,"session":2,"pres":2,"title":"BBB","name":"bbb"},{"day":2,"session":2,"pres":3,"title":"BBC","name":"bbc"},{"day":2,"session":2,"pres":4,"title":"BBD","name":"bbd"},{"day":2,"session":2,"pres":5,"title":"BBE","name":"bbe"},{"day":3,"session":1,"pres":1,"title":"CAA","name":"caa"},{"day":3,"session":1,"pres":2,"title":"CAB","name":"cab"},{"day":3,"session":1,"pres":3,"title":"CAC","name":"cac"},{"day":3,"session":1,"pres":4,"title":"CAD","name":"cad"},{"day":3,"session":1,"pres":5,"title":"CAE","name":"cae"},{"day":3,"session":2,"pres":1,"title":"CBA","name":"cba"},{"day":3,"session":2,"pres":2,"title":"CBB","name":"cbb"},{"day":3,"session":2,"pres":3,"title":"CBC","name":"cbc"},{"day":3,"session":2,"pres":4,"title":"CBD","name":"cbd"},{"day":3,"session":2,"pres":5,"title":"CBE","name":"cbe"}]


I copy-pasted that data into a text-layer I called "Data", then hid the text-layer.
Since we need to do a lookup on 3 numbers to get out two values, I thought it would be easiest to just concatenate the three lookup values, so day 3, session 1, pres 2 will become the unique string: "3|1|2"

I'm creating a new object that holds data like this:

({"1|1|1":{title:"AAA", name:"aaa"}, "1|1|2":{title:"AAB", name:"aab"}, "1|1|3":{title:"AAC", name:"aac"}, "1|1|4":{title:"AAD", name:"aad"}, "1|1|5":{title:"AAE", name:"aae"}, "1|2|1":{title:"ABA", name:"aba"}, "1|2|2":{title:"ABB", name:"abb"}, "1|2|3":{title:"ABC", name:"abc"}, "1|2|4":{title:"ABD", name:"abd"}, "1|2|5":{title:"ABE", name:"abe"}, "2|1|1":{title:"BAA", name:"baa"}, "2|1|2":{title:"BAB", name:"bab"}, "2|1|3":{title:"BAC", name:"bac"}, "2|1|4":{title:"BAD", name:"bad"}, "2|1|5":{title:"BAE", name:"bae"}, "2|2|1":{title:"BBA", name:"bba"}, "2|2|2":{title:"BBB", name:"bbb"}, "2|2|3":{title:"BBC", name:"bbc"}, "2|2|4":{title:"BBD", name:"bbd"}, "2|2|5":{title:"BBE", name:"bbe"}, "3|1|1":{title:"CAA", name:"caa"}, "3|1|2":{title:"CAB", name:"cab"}, "3|1|3":{title:"CAC", name:"cac"}, "3|1|4":{title:"CAD", name:"cad"}, "3|1|5":{title:"CAE", name:"cae"}, "3|2|1":{title:"CBA", name:"cba"}, "3|2|2":{title:"CBB", name:"cbb"}, "3|2|3":{title:"CBC", name:"cbc"}, "3|2|4":{title:"CBD", name:"cbd"}, "3|2|5":{title:"CBE", name:"cbe"}})


So ideally, you would do some pre-processing in another app (or you could even do that in Excel, to get data like this into after effects, and looking up the value would be rather simple.

Just as a purely academical example, here is the inefficient way to just do it every frame in an expression:


json=eval(thisComp.layer("data").text.sourceText.value);

// create a lookup table, ideally this should
// only be done once and stored that way,
// instead of doing it every frame…
lookup={};
for (i in json) {
line=json[i];
compositeKey = line.day+"|"+line.session+"|"+line.pres; // these 3 should exactly match the column names of your excel
lookup[compositeKey] = {title: line.title, name: line.name};
}

// get the rounded down values of three sliders.
day=Math.floor(effect("day")("Slider"));
session=Math.floor(effect("session")("Slider"));
speaker=Math.floor(effect("speaker")("Slider"));

compositeKey= day+"|"+session+"|"+speaker;

lookup[compositeKey].name;
// or:
// lookup[compositeKey].title;




Return to posts index

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