2.4 Load data from a website - Video Tutorials & Practice Problems

<v Instructor>In this sub lesson,</v> we are going to look at loading data from a website. So, we've shown you how to connect into a CSV file, an Excel spreadsheet, and then how to take advantage of the folder data type to save time and maintenance when loading files that are similar. As mentioned in the sub lesson, we'll take a look at website data. So, what we'll do is once again, go to the Data tab. We'll go over to Get Data. We are going to go down to Other Sources, and choose From Web. When choose that, it is going to present us with a dialogue box that asks us, where do we want to get that information from? So, I'm just gonna paste in a URL pointing to the federal reserve to go ahead and connect into. So, I'm just going to click OK. And, we will get a dialogue box that asks us a couple of different questions. So, it asks us how do we want to go ahead and connect into this website? So, I can connect as an anonymous user, and I can have those anonymous credentials apply at any one of these different levels. So, starting at the actual HTM file routine, like all the way down to the root of the website itself. I'm just going to leave it at that right there. Or, I could go ahead and connect in using my current Windows credentials, if they were valid. I can use basic authentication if I needed to log into the website, in API key if I needed to connect into some type of API, or some type of organizational account if I wanted to actually sign in using Microsoft 365, for example. In this example here, we're just going to go ahead and click Anonymous and choose Connect. And so, it's going to take a moment here while it goes out to the federal reserve and looks at that website and sees what type of data can be connected into. So, the navigator window is just churning its way through that website to see what is actually available. So, we can see now that the Navigator has shown us what is available on that website to connect into. So, I can connect on this table here called Document, and I can see this as just a little bit of metadata about the actual website itself. And, this thing down here called Table 0 is actually what we're going to want to connect into. So, I can take a look at this and see that I have various dates and exchange rates. So, what this data source is, is a history of US dollar to Canadian dollar foreign exchange rates since the year 2000. So, this is what the table looks like in tabular view. I can also click on this web view here, and this is going to show me the piece of the website that we're actually going to connect into to extract that data out of. So, we can confirm that's what we want. We'll go back to the table view here. And, now what we're going to do is we're actually going to choose to load this data somewhere. So, I'm going to go down to my Load To, and I'm going to click on this Load To, right here. And, now we'll get the import dialogue box showing up. And this time, let's go ahead and put this data into the actual table to start with first. So, previously we'd been doing only create connection across the board. Let's show you what this looks like when we actually deliver this into an Excel table. So, I choose the Table radio button. I'm going to click OK, let it go ahead and connect into the actual website itself. And, right now it's making that connection, and it will load the data here. And, we can see now that I get this Excel table of data, and I can tell it's an Excel table, cause with my cursor inside of the table, I get this table design tab showing up, and I can see that I have the same drop called Table_0. So, it's borrowing the object name from the navigator to connect into. Plus, it is also the name of the query over here, is indicated the same. So, and I did this to show you that we could load this out to an Excel table. But, let's go ahead and remove this, and get this set up so it's only created as a connection. So, how we are going to go about doing that, is we will go over to this Queries &amp; Connections pane. Then, I'm going to right click on the query itself, and I'm going to say Load To. That's going to pop my Import Data dialog box back up. And, now I'm going to choose Only Create Connections, like I had done before. When I go ahead and click OK, it's letting me know that there's going to be a possible data loss because, what if I had brought this table in and started building some reporting on the other side of this table, using it as a source? It's just warning me now that by switching that back, that that data is now going to be gone. I don't care because I don't have anything built on top of that Excel table. Just go ahead and click OK. And, we'll notice now the table disappears from over here. And, it indicates now that only the connection has been created. Once again, I don't like this name that has been set up here. So, what I'm going to do is I'm going to go down and right click on Table 0, click on the Properties, we'll get moved back over to here. And, I'm just going to go ahead and call this USD to CAD FX. Like so. Go ahead and click OK. So, now my query has a brand new name. Something that makes a little bit more sense. And once again, I can highlight over top of it and see some of the metadata about that particular query. And, that brings an end to this sub lesson on connecting to web data sources.