Json file import and using the data for animation

[JohnK159]

Hello. 

I'm having some problems on how to use a JSON file to bring data from Excel file into after effects. 

I have a speed log data that has information per second on what the longitude and latitude is in the form of an Excel sheet. I wanna convert that into JSON (or CSV, I'm not sure which is better for afte effects) and then bring it into AE and then just animate it in text. Like I just want the text to animate like a counter but it should parse the json data. Can anyone help on how to do this ? 

P.s. I'm attaching the excel data, in case it's required. 

[Dan Ebberts]

What do you mean by "just animate it in text"? The data appears to have new entries once a second. Do you just want it to update that often? So time 21:31:00 in the data would correspond to time 0 in the comp and every second an new value would appear?

[JohnK159]

Yes. Exactly. 

[Dan Ebberts]

In the simplest form, you'd export your Excel data as a CSV and import that into AE. Then you could have source text expression on a text layer, like this:

t = Math.floor(time);
f = footage("GPS Readings.csv");
f.dataValue([1,t]) + " " + f.dataValue([2,t])

You might want to add some code to keep from running off the end of the data, or maybe format the result in a different way. Also, the degree symbol doesn't seem to survive the trip from the CSV file to the text layer--not sure what's going on there. 

[JohnK159]

Omg thank you so much. But what did you mean by add some code to keep it from running off the end of data? And if they degree symbol doesn't import from CSV data can I convert it into JSON and then import? Did the file format give issues? Or second option in the expression end just add + "°" as a string? 

[Dan Ebberts]

It looks like there's 5 minutes of data in the file, if your comp is longer than that, the expression will generate an error when it tries to access data that isn't there. You could capture the error by surrounding the data access statement with a try/catch clause, like this:

t = Math.floor(time);
f = footage("GPS Readings.csv");
try{
  f.dataValue([1,t]) + " " + f.dataValue([2,t]);
}catch(e){
  "No data";
}

Or you could drop the data file into the comp timeline and use the Number of Rows data to limit the index into the data:

maxIdx = thisComp.layer("GPS Readings.csv")("Data")("Number of Rows") - 1;
t = Math.min(Math.floor(time),maxIdx);
f = footage("GPS Readings.csv");
f.dataValue([1,t]) + " " + f.dataValue([2,t]);

 I'm not really sure what to do about the degree symbol.

[JohnK159]

Hello.

Sorry for the delayed reply. It was the weekend. 

Well I did as you suggested but it's not working as I thought. I want all of them on individual text layers. And this is working very bizzare.