ADOBE AFTER EFFECTS: Forum Expressions Tutorials Creative Cloud

Re: Getting Values from csv like LOOKUP in excel

COW Forums : Adobe After Effects Expressions

FAQ   •   VIEW ALL   •   ADD A NEW POST   •   PRINT
Respond to this post   •   Return to posts index   •   Read entire thread


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;




Posts IndexRead Thread 


Current Message Thread:





© 2019 CreativeCOW.net All Rights Reserved
[TOP]