Using After Effects CC 2018, Excel and JSON to create Graphs
Daniel Walter Scott || VIDEO: 2 of 4
So I've got a new project open and I'm on the default setting for my work space, if you want to match it. I'm going to create a New Composition, a new button here. And I'm going to call this Comp 'Weather Chart'. I'm going to pick, from the drop-down here, 'HDTV 1080 25'. Perfect. Let's all make ours '5 secs' long. And match all of that, let's click 'OK'. Let's bring in some graphics. If you're not following along with my examples, obviously you can do with your own. I've built mine in Illustrator because I'm quicker and faster in it, but in the exercise files, there's one called 'Background-Outlined'. Bring him in, and he should fit nicely on to our stage. What we'll do is we'll lock him. Down the bottom here, add the locking icon so that he doesn't get moved.
Now the next thing we need to do is get our data, so let's have a look at how to do that. So wherever you get your data from you need to eventually turn it into this thing called JSON. It's our data interchange format, and that is the only file that After Effects is going to use. So if it's like me, a CSV or an Excel sheet, it's very easy to convert. So you select all your data, copy it, and put it into something like this. There's a JSON-- sorry, this one is called csvjson.com It's just a conversion tool, you can just paste your stuff here You can upload your CSV. I'm going to deleted that and paste that in there. And that is what a JSON format looks like. The cool thing about it is that it's really user friendly to look at. It's not a big, ugly data file, looks nice, the JSON.
Copy it into something, we're going to make a JSON file. You can do it with a text document, I'm using Dreamweaver here. You can use anything, 'New Document', 'JSON', 'Create'. Paste it in, save it. And that is the file that we can use. I've saved two files for us already, you don't have to if you're following along with the exercise files, but with your own data you're going to have to do that. It is not natively in JSON, you're going to have to convert it from either Excel or CSV, or if you've got SQL data, it's easy enough to convert to JSON, you have to figure out with an online conversion tool. So I've done that, saved it, let's go have a look now in After Effects.
So we import our JSON file just like we do any images or video. So I'm to going to go to 'File', 'Import', we'll just double click this gray area here. Let's go into 'Data Files', and let's have a look. We'll do Dublin to start with. I'm going to save my project, I'll save it into my 'Desktop and I'll just call this one 'Weather Reports'. The first bit, and the easiest way to understand that is, remember, at the beginning we had the temperatures in Dublin, and that updated from our database. So we'll do that bit of text connection first, and the people that understand Expressions can go off from there. For the rest of us, we'll do that connection, then we'll look at the Line Chart.
So first up, grab the 'Type Tool'. Click somewhere up here, put in some text. Style it nicely. That's me styling it nicely. Actually I'm going to have two text boxes. So that's my first bit, and I'll leave that, just as a simple bit of text. Down here, I'm going to copy and paste it, so I've got two of them. I'll move this across, double click it, and call this one 'City'. Not writing in Dublin, because I'm going to replace the word 'City' with all the cities potentially that I can use. I'll make sure it's left aligned, 'Character' 'Left Aligned', so that it goes this way. Can't help myself, but I want to use a nice big, thick font. So, I want this to update. To do it, we're going to go into 'City', we're going to twirl this down, and we're going to find 'Text'. I'm going to find 'Source Text', and we're going to turn this into an Expression. If you've never done it before, hold down 'Alt' key on a PC, or 'Option' key on a Mac, and click the little stopwatch. And weird stuff happens.
This bit here, this is where we're going to start doing our typing. What I'm going to do is make it a lot bigger because there's a bit of typing that goes on here. I don't need what's in there currently, and we're going to bring in our JSON file. So first up, to bring it in, all you do is use your 'Pick Whip' here, this little thing here will steal our JSON file. So, that's the first bit, loads our footage from our Dublin weather. I'd like to set it as Source Text. If you are new to Expressions you need to be spelling this all perfectly, like I am here. I'm going to load it into a Variable. This variable is going to be called 'Weather'. Add that to it. And to make it all work, and execute, we need to wrap it up in this thing, in this brackets here called 'eval'. That will make it work, and we need to close it off with a semi colon.
That's the magic, that will bring in your JSON file and load it into this variable that we can start using. So in this case the next thing I want to do is, I want to look at my data and print it on a screen. I'm going to look at this spread here, what I want is the name. So, there's one, two, three, there's only three options in here just to keep the data small and easy to look at, but you can have as many as you like. So I've got three bits of data, and this data says we got 'City'. That's the one I want first, and there's 'year', and the different 'months' we need. And these are the average temperatures. I want to find 'City', I'm going to copy that. Now I need to use this, sort of load all of my JSON file into 'Weather'. So, inside 'Weather', I'd like you to have a little look. So we're looking inside my JSON file, that's now called 'Weather' and I'd like to say, I'd like you to look in the first record, square brackets '[ ]'. So the first record actually in JSON, or an Expression is 0. The first one is always 0. We'll look in the first bit of data. My first option of the three. And I would like to look at dot ' . ', and I would like to look at 'city'.
So that is the thing I'd like to look in and close it off at the end with a semi colon, and hopefully now, 'undefined' will change to 'Dublin'. Awesome! You might be using it just for this option. I use it lots for my intro text, for lots of videos because I have a spreadsheet with all the text that needs to go onto it. And you can set up your animations here and lots of Comps in After Effects, and just relink and change the text based on the spreadsheet, very handy. And because I want the date down here what I'm going to do is, this 'City' layer here, I'm going to copy and paste it. I'll get down here, I'll re-use you. And this one here, instead of the word 'City' we'll give it a name, and we'll call this one 'Date'. And in the text here, so under 'Text', 'Source Text' - we still got my Expression. - all we want to do is, instead of 'City', I want to look at 'Year'. Yes, I'm pretty sure it's 'Year'. I'm just going to move him, and land him over there. Awesome!
So that text box there and that text box there are actually coming from the database. So I can go over here, switch it, and say-- actually let's look at using 'Auckland'. Look at that. Awesome! That one there has the same data set. So it's still looking at '16, but we could go in here and say actually I want to look at the next year. Look at 'Year', or the second option, which is 2015. So I'm going to undo both of those, and go back to Dublin. Hit 'Save' and get on to our Line Chart.