Getting Values from csv like LOOKUP in excel

[Søren Storm]

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?

https://www.youtube.com/watch?v=JPiyOGyjWvI

Some contents or functionalities here are not available due to your cookie preferences!

This happens because the functionality/content marked as “Google Youtube” uses cookies that you choosed to keep disabled. In order to view this content or use this functionality, please enable cookies: click here to open your cookie preferences.

[Filip Vandueren]

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;