After trying to scrape some data from a web table into Excel, some modern web trickery is causing the data to copy to the clipboard in a way that all the data pastes into a single cell in Excel. Power Query comes to the rescue, pulling the data correctly into Excel.
Transcript of the video:
The MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast, episode 1947.
Get Ugly Web Data into Excel with Power Query.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I'm working on a project where I need to create some fake data and I need last name.
So I go out to Google Search for common surnames and I end up at this site that I don't think I've ever seen before: mongabay.com.
But sure enough they have a long, long list of surnames there.
Alright, beautiful.
So I'm just going to take this data, I'm going to copy it over to Excel using good old fashioned Copy and Paste.
And I don't need ten thousand names.
I just, you know, I need a few hundred names to make it worthwhile.
Ctrl+C to copy and then I bounce over to Excel, where I Ctrl+V and…what's this?
They've somehow taken that table and comma-separated it without commas between the last column and the first column.
And then the link to the whole thing, right.
Which, you know, it just seems completely evil to me, that they've somehow obscured that data, so that way I can't just use it for my fake data.
So, I go back to the browser.
I copy the URL and we're going to use a free tool from Microsoft called Power Query.
You know, you need Excel 2010 or Excel 2013.
Any version of 2010 or one of the higher levels of 2013, they get the Power Queries and we're going to get the data from the web.
Here's the URL, Ctrl+ V and click OK.
And Power Query is going to go out to that website, analyze the website, look for tables in the website.
Now, this takes a few seconds.
They come back with what they perceive as two tables.
Now I'm going to hover over each one in this case.
And you'll see the preview there shows that this is what I'm looking for.
I'm taking it must be the entire spreadsheet.
Yes, so go to Table 0, choose that and I can either just say Load.
But let's go through and Edit the query, because in fact I don't care about anything other than column 1.
So I choose column 1 and I open the Remove Columns and say Remove the Other Columns and then Close & Load.
At this point, over on the right-hand side you see that they've loaded a hundred preview rows, but it's still trying to go back out to the website to get the rest of the data.
There we go.
A few seconds later we're up to a thousand and one rows loaded.
So, you know, some sort of a modern website.
Their web page that was doing something that a regular old Copy and Paste just wouldn't work.
But thanks to the free Power Query tool from Microsoft I was able to get that data into Excel.
Alright, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
Learn Excel from MrExcel podcast, episode 1947.
Get Ugly Web Data into Excel with Power Query.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today I'm working on a project where I need to create some fake data and I need last name.
So I go out to Google Search for common surnames and I end up at this site that I don't think I've ever seen before: mongabay.com.
But sure enough they have a long, long list of surnames there.
Alright, beautiful.
So I'm just going to take this data, I'm going to copy it over to Excel using good old fashioned Copy and Paste.
And I don't need ten thousand names.
I just, you know, I need a few hundred names to make it worthwhile.
Ctrl+C to copy and then I bounce over to Excel, where I Ctrl+V and…what's this?
They've somehow taken that table and comma-separated it without commas between the last column and the first column.
And then the link to the whole thing, right.
Which, you know, it just seems completely evil to me, that they've somehow obscured that data, so that way I can't just use it for my fake data.
So, I go back to the browser.
I copy the URL and we're going to use a free tool from Microsoft called Power Query.
You know, you need Excel 2010 or Excel 2013.
Any version of 2010 or one of the higher levels of 2013, they get the Power Queries and we're going to get the data from the web.
Here's the URL, Ctrl+ V and click OK.
And Power Query is going to go out to that website, analyze the website, look for tables in the website.
Now, this takes a few seconds.
They come back with what they perceive as two tables.
Now I'm going to hover over each one in this case.
And you'll see the preview there shows that this is what I'm looking for.
I'm taking it must be the entire spreadsheet.
Yes, so go to Table 0, choose that and I can either just say Load.
But let's go through and Edit the query, because in fact I don't care about anything other than column 1.
So I choose column 1 and I open the Remove Columns and say Remove the Other Columns and then Close & Load.
At this point, over on the right-hand side you see that they've loaded a hundred preview rows, but it's still trying to go back out to the website to get the rest of the data.
There we go.
A few seconds later we're up to a thousand and one rows loaded.
So, you know, some sort of a modern website.
Their web page that was doing something that a regular old Copy and Paste just wouldn't work.
But thanks to the free Power Query tool from Microsoft I was able to get that data into Excel.
Alright, hey, I want to thank you for stopping by, we'll see you next time for another netcast – MrExcel.
