HomeХобби и стильRelated VideosMore From: Computergaga

Import Data from the Web into Excel

426 ratings | 202204 views
http://alanmurray.blogspot.co.uk/2013/06/import-data-from-web-into-excel.html Import data from the web into Excel. Importing data from the web creates a connection. This connection can be refreshed to ensure your spreadsheet is up to date.
Html code for embedding videos on your blog
Text Comments (87)
Lewis Allen (2 months ago)
Just watching this video and when i try to do this on my version of excel there is an error message saying table highlighting is disabled as im using internet explorer compatibility mode, is there a way i can get past this ?
Kirandeep Choudhry (2 months ago)
Plz reply soon how to get back data from scientific notation without losing any data plz it's my humble request
Kirandeep Choudhry (2 months ago)
Hello, I got a problem one spreadsheet mailed to me there were numbers with scientific notation and they were more than 17 or 19 digits, how to get those numbers back without losing any data is there any solution in excel 2007 plz reply soon plz
rapnationcrew (2 months ago)
Everytime i put the youtubelink to show the static of visitor, it stops with the message "no connect to page" how to solve?
Computergaga (2 months ago)
Not something I have done. There may be an add-in or api which helps to connect this.
rapnationcrew (2 months ago)
How it is possible to show from Youtube Videos the actuel Visitor (count) in Excel? For what i have to look for?
André Novaes (2 months ago)
Very good.
Computergaga (2 months ago)
Thank you André.
Mike Wilson (3 months ago)
In Excel 2016 when I select from the web on my toolbar I get a different screen than the dialog box your showing. Mine just asked for the URL - Basic or Advanced.
Computergaga (3 months ago)
This is how the new version of Excel look Mike. It has changed since my video was recorded. What you see is Power Query/Get & Transform creating the web query.
Louie Josette (4 months ago)
thumbs up
Computergaga (4 months ago)
Thank you Louie.
FAt shafa (5 months ago)
Thank you so much for uploading..it's very helpful
Computergaga (5 months ago)
You're welcome Fatima, thannk you.
Jarred Miner (6 months ago)
Is there a way to have it import the whole table if the webpage limits the number of rows per page ?
Anvesh Sameer (7 months ago)
But the website i want to import data is not supporting internet explorer, what to do?
Computergaga (7 months ago)
There are improvements in this area in recent version of Excel. Check out Power Query if you have 2013+.
jhad vital (7 months ago)
Thx a lot for this video. But i'm struggling about importing data from google sheets but can import from any other website .It says the navigator is not compatible even though i have the lastest internet explorer. I'll be gratefull for your help guys.
Yuri.77 Alves (7 months ago)
the =importdata("") is not working for me in excel 2013... how u enable it?
Computergaga (7 months ago)
? Click on the Data tab and then From Web as in the video. The video shows Excel 2010, but 2013 has not changed in this regard.
himanshi contractor (11 months ago)
I have 1 of 2000 pages ..so how can I...do..this.....because this is only..one page selected and imports....
Computergaga (11 months ago)
Yes so it is a manual process until the query is created. You want to look at an automated solution via VBA or some other programming or connection.
Mike Delgado (1 year ago)
Indeed an excellent video. Very helpful.   Thank you for sharing.
Computergaga (1 year ago)
You're welcome Mike.
deepika tailor (1 year ago)
Really Awesome.....!!!!
Computergaga (1 year ago)
Thanks Deepika.
Kapil Morrison (1 year ago)
Whenever i do this , My web query window always freezes up
Jurij Fedorov (1 year ago)
I went to a website with tables on it. Tables I can easily copy into Excel. But there are no yellow arrows on them when I do it this way. This is super weird but also makes this little trick useless to me. You basically have to trust that these arrows will appear and save your day.
Computergaga (1 year ago)
With this built in Excel feature, yes. It all depends how the web page was developed. How the table was constructed and whether Excel recognises it as one. You can Google web scraping to see how to extract web data using VBA and Python for a more advanced technique.
Shelby Creed (1 year ago)
this is awesome thans so much
Computergaga (1 year ago)
Your welcome Shelby. Thank you.
Irfan Pathan (1 year ago)
How can I add, edit and store data in excel by html
Computergaga (1 year ago)
There are various techniques for adding data from the web including the web query tool, VBA and Python. As for editing and storing in Excel using HTML, that is not really its job.
Heri grosir (1 year ago)
A very useful tutorial , thanks
Computergaga (1 year ago)
Your welcome Heri.
mahesh kumar (1 year ago)
nice video . how to reterive data which has multiple page numbers ?
Computergaga (1 year ago)
Thanks Mahesh, this is a bit beyond a basic web query and would probably require VBA for web scraping.
HellPatrol92 (1 year ago)
Thanks a lot for your video! Yet, I still have a problem. After I click "from web", the internet window pops open and I can search for any websites, BUT another window keeps popping out, reading: "Page script error". I can shut it down, but it will open again, more and more often, eventually it will either make Excel crash, or only allow me to import the whole page and not a single table. Any clues about the reason why? Thanks in advance
Hi, I have the same problem "Script Error". Do you solve it somehow? If I reinstall my internet explorer or Microsoft do you think that this will help?
HellPatrol92 (1 year ago)
Thanks!
Computergaga (1 year ago)
Thanks for your comment. The importing whole page or single table is probably to do with how that web page is structured i.e. tables, div tags etc. I'm not a web designer/developer so getting beyond my knowledge a little. The script error can probably be stopped as we can ask for scripts not to be run but once again a little outside the Excel realm here. Hopefully someone else can answer this one better.
Master tactic (1 year ago)
sss
Thanks for help!
manolis kokotsakis (1 year ago)
Νice video. Is it possible to collect a result of one team, not the all list , to make a list of teams that i am interesting for ?
Computergaga (1 year ago)
After the import a connection is created. The data between the spreadsheet and the webpage can then be updated by clicking Refresh on the Data tab. It would however bring across all the pages data, if that is what was imported first time around. You may need to create a macro that removes the data you are not interested in to make this process quicker.
manolis kokotsakis (1 year ago)
Thanks for your answer sir. Yes, the site i visit allowes me to import data on excel page, but as on your video, i import the all page of data. I know that after i can delete what i dont need, but would the excel file upate the info only for the soccer teams i am interesting for ? I dont data for all team, only those i follow.
Computergaga (1 year ago)
The website would need to have a page or table with this information for us to use. Then you may be able to do it with a technique like in the video.
Wilmer Bernal (1 year ago)
Is there a way to do this in Microsoft Excel for mac? Under the data tab, I have an option to import From HTML rather than From Web, and it opens to a page without the option. I've already spent hours trying to figure this out!
Computergaga (1 year ago)
That sounds like the same thing. I don't use a Mac cannot help more though.
Matthew Urke (2 years ago)
Any way to retrieve a bank balance to excel from a bank site
Computergaga (2 years ago)
This would require programming to scrape the associated site. And being a bank site security aspects.
Mohamed Saadi (2 years ago)
I am usually searching for the daily exchange rates for foreigners currency from difference web sites and that is take a lot of time .!! Is there anyway to choose one item only from the schedule imported to excel sheet , could be USD and on the second line in excel sheet I put a source for update for the same item chosen but from another Webpage or another site to have finally ten difference update for one item (ex: USD ) from 10 or 20 difference sites
Mohamed Saadi (2 years ago)
Computergaga Thank you for reply and support.
Computergaga (2 years ago)
This would require some programming from VBA or python to scrape the different sites for the currency that you want.
andreikilla (2 years ago)
How to avoid scientific notation when doing this??
Trouble Shooter (2 years ago)
Hi, I wanted to get data from truecaller.com on my excel sheet for mobile numbers. can you kindly explain how can I programme it ? I need quiry based data pulling . Thank you
Mahim A (2 years ago)
Hi there, I used this to import some data from an online source a few weeks ago but now the online data has changed and introduced more options. Is there away to find out the source of my data quickly (I.e. the url) so I can change my table.
Computergaga (2 years ago)
Sure, click on Data > Connections, select the connection in the list and select Properties. Click the Definition tab and then Edit Query (bottom left)
jammapcb (2 years ago)
How do you refresh the data?
Computergaga (2 years ago)
There is a Refresh button on the Data tab. You could also click Properties on the Data tab and set the refresh to occur every x number of minutes or when the spreadsheets is opened.
Bexar (2 years ago)
My browser crashes everytime I open the "New Web Quary" :/
Ravi Ravindran (2 years ago)
what to do if we get an error saying that no data to extract?pls advise
WY LAM (2 years ago)
A very good video like your accent I need help here on the same website http://www.soccerstats.com/timing.asp?league=spain The problem is excel only gets the data on the "Total " tab, while I need two separate tables for "HOME"and "AWAY"enter image description here Excel cannot identify Home or Away tab i click. It just give me the data under the "total" tab. How can I get the data under the tab of home and away in this web Plesae ?
John Smith (3 years ago)
Hi, thanks for the video. General question regarding time series data. Say for example you have stock market data coming in by the 1 minute interval. Instead of overwriting the excel box, is there a way to create a log for each minute occurrence, thus giving you a one day minute chart by the end of the day?
Brett Kellogg (3 years ago)
What if the data table you want is not recognized by excel i.e. doesn't have a yellow arrow-box in the corner. I can paste the entire webpage, but I don't want all the junk surrounding it - just the table of statistics. Every time I hit refresh the rest of the page shows back up in excel.
Computergaga (2 years ago)
Hi Ewen, I have various macro and VBA tutorials on my YouTube channel and Blog. You can also get more structured learning from my online course - http://goo.gl/oe72Rj
Ewen Wang (2 years ago)
Could you please talk more about how to create a macro in Excel to remove excess? THX
Computergaga (3 years ago)
+Brett Kellogg You can only import what is possible and this is determined by the structure of the webpage. If you don't have control of the webpage to change it, then you could create a macro in Excel that after the import automatically removes all the excess that you don't want.
MaliDaki (3 years ago)
What if there are several pages? Like i have link to click next and i have let's say 20 pages, can i automate whole procces?
Computergaga (3 years ago)
+MaliDaki It is possible to automate the process but would require VBA. You can automate the process from the 20 different URL's.
Tyson Price (3 years ago)
how do you bring data from a spreadsheet cell to an html textarea?
Alejandro T (27 days ago)
Did u ever get a solution?
Korey Ward (3 years ago)
Great video, I read the comments and I was wondering if you had an answer for the reason boxes don't show up for some people? I only get boxes to show up at the top left of a site to import the entire page when I only want certain data. This happens for every site I try on my PC. Do you know why this might be? Thanks.
Korey Ward (3 years ago)
+Computergaga​ Could it be the excel file? Do I need to have it saved as a certain type of file in order to be able to import data from the Web via the query?
Korey Ward (3 years ago)
+Computergaga Right. My computer however does not allow me to get the boxes on nearly every site (Yahoo Finance being the only one it does) and I get a script error when trying to use queries that I have to click out of repeatedly. 
Computergaga (3 years ago)
+Korey Ward The boxes indicate the parts of the webpage you can extract. You will always get one in the top left, and then maybe additional ones dependent upon the site.
The NetFreak (3 years ago)
im having an issue importing the fantasypremier league website i cant import specific tables instead it only gives me one option that is to import the entire website how can i solve this please help ASAP :(
The NetFreak (3 years ago)
no it works on my friends PC but not mine and we use the same version of Excel 2013
Computergaga (3 years ago)
+Achillles Son of Peleus Don't think you can. The website is out of your control. Depends how the website is structured on which elements you can import. You could create a macro to tidy it up after everything is imported.
HarvestrX (3 years ago)
Until they add in the ability to import specific CSS divs and enclosed content, this feature is outdated and useless to me. If this were an Adobe app, such a feature would have been added in years ago!
Louie Josette (4 months ago)
This is good for newbies and not suitable for you kid.
CabinDoor (6 months ago)
And I agree. Useless and could easily be made useful. MS don't care though.
Computergaga (3 years ago)
+HarvestrX Yes this feature could do with some improvements
Daniel van den Aarssen (3 years ago)
Computergaga, great video, very informative!
Andrej Haluška (4 years ago)
Thx 4 video, its helpfull
aswad_Bangladesh (4 years ago)
i really don't understand how can it be that there is no comment on such an awesome vedio. hello sir, i really don't know how to thank u. so awesome u'r upload is. really awesome.
what's new (10 months ago)
Poda

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.