ADOBE AFTER EFFECTS: Forum Expressions Tutorials Creative Cloud

dealing with commas used for separating thousands in large numbers in .csv or .tsv files

COW Forums : Adobe After Effects Expressions

<< PREVIOUS   •   FAQ   •   VIEW ALL   •   PRINT   •   NEXT >>
peter kovalik
dealing with commas used for separating thousands in large numbers in .csv or .tsv files
on Mar 29, 2020 at 3:48:28 am

I needed to sort a list of Canadian Provinces with attached numeric values representing the Covid-19 cases . The values change daily and each time the code works great. But now that some have surpassed the 1,000 mark the code broke. The values come from web list that is saved daily as a .csv file. The moment that happened, the generated file added quotes around values that now include the thousands comma. At first I assumed that the quotes were the issue. It turns out it's the comma. I tried exporting the values as .tsv thus avoiding the quote marks. Still doesn't work. The problem is the person who needs to generate the graphic cannot go into the source file to edit out the quotes and commas to make the code work, too time consuming and complicated for the junior designer tasked with this job every morning. Also the numbers then do not conform with the rule of separating thousands with a comma. Typical Catch 22 - you can't have the commas for the code to work but have to have them to make the graphic right.

If anyone can help, this would be greatly appreciated.

peter


The code comes from FluxVfx website blog, 12 Examples for Data-Driven Expressions for After Effects 2019.

#12. Sort Number Data Descending with Corresponding Row Text Data


https://fluxvfx.com/blogs/blog-fluxvfx/data-driven-expressions-for-after-ef...

// code on text source representing the numeric values list

values = [];
for(i=0;i&lt;=thisComp.layer("Data-Driven-Sample.csv")("Data")("Number of Rows")-1;i++) {   
  values.push(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data " + i).value);
} values.sort((a, b) => b - a); values.join("\n");


// code on the corresponding item list to be sorted based on descending values of the above numbers list

var examples = [
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 0"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 0"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 1"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 1"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 2"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 2"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 3"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 3"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 4"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 4"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 5"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 5"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 6"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 6"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 7"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 7"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 8"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 8"))
},
{
'text' : thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 9"),
'number' : parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data 9"))
},


];
const list = examples.sort((a,b) => b.number-a.number).map((examples, number, array) => examples.text)
list.join("\n")




Return to posts index

Andrei Popa
Re: dealing with commas used for separating thousands in large numbers in .csv or .tsv files
on Mar 29, 2020 at 8:34:37 am

I'll guess you have to remove the comma and turn them into numbers for your sort to work. Also, making a for loop for the second makes it more elegant and also makes it work if you have more rows in your csv. Haven't tried it, hope it works on the first run:

values = [];
for(i=0;i<=thisComp.layer("Data-Driven-Sample.csv")("Data")("Number of Rows")-1;i++) {
rowVal = thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data " + i).value;
values.push(parseInt(rowVal.replace(",","")));
}
values.sort((a, b) => b - a);
values.join("\n");


// code on the corresponding item list to be sorted based on descending values of the above numbers list
examples = [];
for (i = 0;i <= thisComp.layer("Data-Driven-Sample.csv")("Data")("Number of Rows") - 1;i++) {

var rowVal = {};
rowVal.text = thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 0");
rowVal.number = parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data "+i).replace(",",""));
examples.push(rowVal);

}
const list = examples.sort((a,b) => b.number-a.number).map((examples, number, array) => examples.text);
list.join("\n");


Andrei
My Envato portfolio.


Return to posts index

peter kovalik
Re: dealing with commas used for separating thousands in large numbers in .csv or .tsv files
on Mar 30, 2020 at 7:18:07 am

Hello Andrei,

Thank you for replying so fast. Unfortunately, I am a real neophyte when it comes to expressions so I probably didn't do this right. I applied the first (values = []; ...) to the NUMBER layer's Text Source (as per original) and the second on to the TEXT layer's Text Source. However both expressions return errors.

The first the message: "Error: TypeError: rowVal.replace is not a function" in line 4 > "values.push(parseInt(rowVal.replace(",","")));"

The second: "Error: TypeError: thisComp.layer(. . .)(. . .)(. . .)(. . .)(. . .).replace is not a function" in line 6 >
"rowVal.number = parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data "+i).replace(",",""));"

Was there something else I needed to do?
thank you again
peter


Return to posts index


Andrei Popa
Re: dealing with commas used for separating thousands in large numbers in .csv or .tsv files
on Mar 30, 2020 at 8:26:54 am

Try this
values = [];
for(i=0;i<=thisComp.layer("Data-Driven-Sample.csv")("Data")("Number of Rows")-1;i++) {
rowVal = thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data " + i).value.toString();
values.push(parseInt(rowVal.replace(",","")));
}
values.sort((a, b) => b - a);
values.join("\n");


// code on the corresponding item list to be sorted based on descending values of the above numbers list
examples = [];
for (i = 0;i <= thisComp.layer("Data-Driven-Sample.csv")("Data")("Number of Rows") - 1;i++) {

var rowVal = {};
rowVal.text = thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Text Data")("Text Data 0");
rowVal.number = parseInt(thisComp.layer("Data-Driven-Sample.csv")("Data")("Outline")("Number Data")("Number Data "+i).value.toString().replace(",",""));
examples.push(rowVal);

}
const list = examples.sort((a,b) => b.number-a.number).map((examples, number, array) => examples.text);
list.join("\n");


If this still doesn't work, could you give me the csv so i can test the solution before posting?

Andrei
My Envato portfolio.


Return to posts index

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