Maxwell asks how best to pull web data into Excel. He notes that data pasted from a webpage often ends up only in column A. Episode 642 will show you how to solve this problem using a web query.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today we have a question sitting by Maxwell.
Maxwell wants to know what the best way to get data from a web page into Excel.
Now, this is usually a couple of step process the first question is how do we get it into Excel?
And then how do we parse it into columns?
Asked Maxwell for a sample of what he was trying to do he sent me a URL from the Bureau of Labor Statistics.
Basically, I'm gonna start with a blank Excel spreadsheet go to Data, Import External Data, and New Web Query and what that does is it opens up a browser right here in Excel.
And so, we'll take this browser and basically browse to whichever webpage we want to grab the data from.
Give this a second to load.
You have to wait till the data has completely loaded.
So, wait till the page is finished and then you'll notice that Excel draws in tiny little yellow arrows and those yellow arrows identify all of the various information, tables that are on the web page.
So, you want to basically get down to the point where you have just the table that has the data, we'll choose that and click Import and Excel will pull that into cell A1, you'll see when I click OK, here I get a Getting Data message for a couple of seconds until it goes out and pulls all that data in from the web page.
Now, some interesting things is if this is a table, that would change every day we setup.
So, that way it'll go out and grab the data in from the web page.
Now, the big advantage here is that Excel was able to sense the data that we had it kind of messed up the August, September headings up here, but it did go through successfully and parse the data without any problem at all.
Now, in other websites here let me open up a blank spreadsheet, and I'll just go back to the original Internet Explorer.
I'll copy the data from Internet Explorer and use control+C to copy and then control+V to paste that into Excel.
You'll see that when we use that method instead of a Web Query, what we're going to find is that all of the data comes in and it's basically in column A, instead of in the individual columns, what we wanna do there is use Data, and then Text to Columns and say that our data is Fixed Width.
We'll click Next and you'll see that Excel actually draws in fairly good lines here to break out the data.
I mean always want to make sure it didn't add an extra line and here we have an unprintable character at the end.
I'm gonna add a new line to separate that from the rest of the data.
In general try and leave everything as general you don't wanna change anything to text unless you need to keep some leading zeros in an account number.
This last column I'll say Do not import, click Finish.
And now, we have basically the same data.
Although, we're gonna have to use format column AutoFit to make things fit a little bit better.
So, either way whether you're copying and pasting the data in which case you'll usually have to use text to columns or the better way of course is to go and just use Data, Import External Data, New Web Query, where you'll get a Web Query and then if you need to, you can actually click here the Data Range Properties and so they we want to refresh this Data every time we open the file.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today we have a question sitting by Maxwell.
Maxwell wants to know what the best way to get data from a web page into Excel.
Now, this is usually a couple of step process the first question is how do we get it into Excel?
And then how do we parse it into columns?
Asked Maxwell for a sample of what he was trying to do he sent me a URL from the Bureau of Labor Statistics.
Basically, I'm gonna start with a blank Excel spreadsheet go to Data, Import External Data, and New Web Query and what that does is it opens up a browser right here in Excel.
And so, we'll take this browser and basically browse to whichever webpage we want to grab the data from.
Give this a second to load.
You have to wait till the data has completely loaded.
So, wait till the page is finished and then you'll notice that Excel draws in tiny little yellow arrows and those yellow arrows identify all of the various information, tables that are on the web page.
So, you want to basically get down to the point where you have just the table that has the data, we'll choose that and click Import and Excel will pull that into cell A1, you'll see when I click OK, here I get a Getting Data message for a couple of seconds until it goes out and pulls all that data in from the web page.
Now, some interesting things is if this is a table, that would change every day we setup.
So, that way it'll go out and grab the data in from the web page.
Now, the big advantage here is that Excel was able to sense the data that we had it kind of messed up the August, September headings up here, but it did go through successfully and parse the data without any problem at all.
Now, in other websites here let me open up a blank spreadsheet, and I'll just go back to the original Internet Explorer.
I'll copy the data from Internet Explorer and use control+C to copy and then control+V to paste that into Excel.
You'll see that when we use that method instead of a Web Query, what we're going to find is that all of the data comes in and it's basically in column A, instead of in the individual columns, what we wanna do there is use Data, and then Text to Columns and say that our data is Fixed Width.
We'll click Next and you'll see that Excel actually draws in fairly good lines here to break out the data.
I mean always want to make sure it didn't add an extra line and here we have an unprintable character at the end.
I'm gonna add a new line to separate that from the rest of the data.
In general try and leave everything as general you don't wanna change anything to text unless you need to keep some leading zeros in an account number.
This last column I'll say Do not import, click Finish.
And now, we have basically the same data.
Although, we're gonna have to use format column AutoFit to make things fit a little bit better.
So, either way whether you're copying and pasting the data in which case you'll usually have to use text to columns or the better way of course is to go and just use Data, Import External Data, New Web Query, where you'll get a Web Query and then if you need to, you can actually click here the Data Range Properties and so they we want to refresh this Data every time we open the file.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
