-
dealing with commas used for separating thousands in large numbers in .csv or .tsv files
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-effects-2019
// code on text source representing the numeric values listvalues = [];
for(i=0;i<=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") -
3 Replies
-
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. -
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 -
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.
Reply to this Discussion! Login or Sign Up