May 14, 2014

Web Scraping for Non-Programmers: 3 easy Tools to Extract Data from Websites

If you work with data and use the web as your main source for datasets, then you might have heard the words "web scraping". If you have not come across it yet, well surely you happened to find some interesting data on the web, but no available download options. No csv file or excel download. Nothing. Nada. Niente. And even your desperate copy-and-paste attempt has failed you. This is where web scraping comes in handy.

This post is about introducing web scraping, and I am going to present 3 tools anyone of us can use to "scrape" the web. Two of them can be used directly from your browser, while the other option is available through Google Spreadsheets. But, most importantly, they are all free, very quick and easy to use and do not require programming skills.

All right, let's define the topic of this post first. What the heck is Web Scraping?

web scraping




What is Web Scraping?


Web Scraping refers to the software technique of extracting information from websites. The information extracted can be both text or grafic. And, once gathered, it can be used for various purposes: from business to academic research or for any other personal purpose.

An important aspect of web scraping, which differentiates it from web crawling (the process of indexing info on the web, like Google and other search engines do), is that web scraping focuses on the transformation of unstructured data, typically in HTML format on the web, into structured data that can be stored and analyzed in a central local database or spreadsheet.

web scraping

As I mentioned above, web scraping can be performed with several diffferent techniques and technologies, each of them offering a different level of automation for finding and extracting data from the web. I am not going in depth on web scraping technologies since I am not an expert. However, to get an idea we can think of different levels of web scraping automation ranging from:


  • on one extreme, the very basic human copy-and-paste which is a very long and tedious operation if you need to scrape lots of datasets. Nevertheless, sometimes copy-and-paste is the only workable solution and even the a very advanced technology cannot replace it. This happens in cases like the website built barriers preventing automated programs scraping the content.

  • on the other extreme, a web scraping software that interacts with websites in a similar way as web browser. But instead of displaying the HTML document on screen, the web scraping software quicky extracts the desired content (for example only some specified fields like product, sku, price) from the HTML syntax and saves it in a local file of your machine or in an external database.



Case Uses: Why Should you Want to Scrape the Web?


The practical uses of web scraping are potentially endless. Each person or business has its own specific needs for extracting data from the web. While it's impossible to create a complete list of web scraping uses, here below I am providing a couple of popular reasons for scraping the web.

  • Research: finding the right data on the web is a very important activity for academic, scientific, marketing researchers or financial analysts. Whatever the field of research, they all have to answer some specific questions.  And to do that, they need to find appropriate data, possibly from several different websites, combine it in a single spreadsheet and analyse it. Having some handy web scraping tool will make their work much more effective.

  • Competition Analysis: a key activity for marketers and sales people is researching competition, which often means visiting competitor websites, industry directories, etc. The data they will look for can be prices, product features, and are probably displayed in HTML tables. Beside marketers, everyone of us is a potential customer on the web: we look for products, services and often do price comparisons before making a decision. Why not saving the data we found on different web pages into a spreasdsheet, and make a decision from there?     

  • Lead Generation: again, this is a fundamental task for marketers, which involves visiting companies websites, industry directories/exhibitions, yellowpages or social networks like Linkedin in order to find potential buyers. The data they look for are customer names, address, phone numbers, email, etc.  



3 Web Scraping Tools for Non-Programmers


1. Table Capture (Chrome Extension)


Table Capture is an extension that you can add to your Google Chrome browser and use it while you navigate through web pages. What this extension does, is giving you the ability to quickly copy HTML tables to the clipboard and use them in a spreadsheet, like Microsoft Excel, Google Docs or Open Office.

Installation

I assume you already have Google Chrome installed in your machine. Once installed, go to Google Chrome Extensions page, search for "Table Capture" and add it to your browser. Make sure the extension is active (you can disable it whenever you like, by going to Settings-->Extensions from the Chrome main menú).

Google Chrome Table Capture Extension


How to Scrape Data with Table Capture

Let say we are looking for some markets data from the Financial Time webpage. There are various tables on this page, however getting this data into a spreadsheet is not really easy.  Without this extension we would probably try to select the first table, copy it and paste it into a spreadsheet. But we will realize that excel will put all the data in only in a wrong format, so not very useful.

Using Table Capture extension the data scraping process is easy. While navigating through web pages inclusive of tables, you will see a red icon appearing on the top of your browser. If you click on the icon, it will bring up a list of all the tables that it found on the webpage. If there is a small number of tables, you can quickly scan the list of tables displayed by Table Capture and identify the one you like to export (look at the size). Otherwise, if you find difficult identifying the right table from the menu,  I recommend clicking click on "display inline" and a copy-to-clipboard menu will appear every time you mouse over a table in the web page.


Scraping Data with Table Capture


Once Table to Capture detects a table, you can either:

 a) copy it to the clipboard and then paste it (ctrl+v) into an Excel spreadsheet, or
 b) extract it directly into a Google doc spreadsheet (you must be looged in with a Google account)




I found that this Table Capture extension can be very useful especially if you work on projects where you research a lot on the web and need to answer questions quickly, based on data. This tool will allow you to get data out of webpages quickly, and import it into your favourite spreadsheet tool where you will process it further (cleaning, etc.) or directly perform some analysis.


2. Clipboard to Table (Firefox Extension)


If you prefer using Firefox to browse the web, luckily there is web scraping add-in too. It works pretty much the same as Chrome extension, with the difference that it also allow selecting only certain rows/columns of an HTML table.

Installation:

Assuming you have already installed Mozilla Firefox, you can download Clipboard to Table from the Mozilla Add-ons page. Make sure the add-in is enabled in your browser settings.


Firefox Clipboard to Table add-on


How to Scrape Data with Clipboard to Table:

Scraping web data with Clipboard to Table is even easier than the previous tool. Just place your mouse cursor over a table, right click and among the varius options you will see one names "Table2Clipboard". From there you can choose to copy the whole table or only a specific row/column, like in the image below. That´s it. The table is saved in your clipboard and ready to be pasted on your favourite spreadsheet.


Scrape Data with Clipboard to Table


3. Google Docs Spreadsheets


Very few people maximize the potential of Google docs tools. Google docs Spreadsheet has been through many improvements over the last year, and among the many features offered, a very interesting one is the possibility to extract data from HTML tables and import it directly in the spreadsheet.

Installation

You must have a Google account to access Google docs. Once logged in Google, go to Google Drive page and click on Create--> Spreadsheets.


How to Scrape Data with Google Spreadsheets 

Go to any blank within Google Spreadsheet and type in the following formula:

= importHTML("","table",N)

which has 3 arguments:
- the first one is the URL of the webpage containing the table; it needs to be placed between double quotes
- the second argument indicates that it is a table. Just leave "table" in this case (it depends on the type of query you like to do, for example you could also request a "list" of elements within the web page)
- and the third argument N indicates the number of the table within the web page; counting starts from 1. My recommendation, in order to find the right table number, is to start trying from 1 and increment the number until you get the correct table.

As an example, let´s try to extract the same table above from Financial Times, this time through Google Spreadsheet. Te formula would be:

= importHTML("http://markets.ft.com/research/Markets/Overview","table",1)

If everything goes well, the data table should be extracted from the web page and appear directly into your spreadsheet, as below. Amazing!


Scrape Data with Google Docs Spreadsheet


The very cool thing about this data scraping option is that if the HTML table will be updated in the website, the data in your spreadsheet will be updated too when you refresh the Google doc spreadsheet.


In this post I wanted to give a brief introduction to web scraping and present 3 simple tools everyone one of us can use to extract data whithout coding. Of course there are much more sophisticated scraping tools in the market, and if you have programming skills you can write your own script to extract data from web pages (R is a good option).

Please share comments and any other interesting web scraping tool we can add to the ones presented here. Thanks!


60 comments:

  1. Hello just wanted to give you a quick heads up. The text in your post seem to be running off the screen in Safari. I'm not sure if this is a format issue or something to do with internet browser compatibility but I thought I'd post to let you know. The design and style look great though! Hope you get the problem fixed soon. Thankshome business ideas singapore

    ReplyDelete
  2. I was recommended this web site by my cousin. I'm not sure whether this post is written by him as nobody else know such detailed about my difficulty. You're incredible! Thanks! .com.sg domain name


    ReplyDelete
  3. hello!,I like your writing so a lot! proportion we communicate more approximately your post on AOL? I need an expert on this space to solve my problem. May be that is you! Taking a look forward to peer you. Singapore beauty salon

    ReplyDelete
  4. Does your website have a contact page? I'm having problems locating it but, I'd like to send you an e-mail. I've got some recommendations for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it grow over time. retail website development

    ReplyDelete
  5. Hey just wanted to give you a brief heads up and let you know a few of the pictures aren't loading properly. I'm not sure why but I think its a linking issue. I've tried it in two different web browsers and both show the same results. used industrial robots

    ReplyDelete
  6. Superb blog! Do you have any tips for aspiring writers? I'm planning to start my own website soon but I'm a little lost on everything. Would you propose starting with a free platform like Wordpress or go for a paid option? There are so many choices out there that I'm completely overwhelmed .. Any tips? Bless you! civil lawyer singapore

    ReplyDelete
  7. With havin so much written content do you ever run into any issues of plagorism or copyright infringement? My site has a lot of unique content I've either authored myself or outsourced but it seems a lot of it is popping it up all over the web without my permission. Do you know any methods to help stop content from being stolen? I'd really appreciate it. superfood in Singapore

    ReplyDelete
  8. Good day! I know this is kinda off topic nevertheless I'd figured I'd ask. Would you be interested in trading links or maybe guest authoring a blog article or vice-versa? My site covers a lot of the same topics as yours and I believe we could greatly benefit from each other. If you are interested feel free to shoot me an e-mail. I look forward to hearing from you! Wonderful blog by the way! family office in Singapore

    ReplyDelete
  9. Hello! Someone in my Facebook group shared this website with us so I came to check it out. I'm definitely enjoying the information. I'm bookmarking and will be tweeting this to my followers! Terrific blog and wonderful design and style. search engine marketing agency

    ReplyDelete
  10. Great post I would like to thank you for the efforts you have made in writing this interesting and knowledgeable article. anonymous blockchain

    ReplyDelete
  11. What i don't realize is in fact how you are not actually much more well-preferred than you might be now. You're so intelligent. You realize therefore considerably on the subject of this topic, produced me for my part believe it from numerous numerous angles. Its like women and men don't seem to be fascinated unless it’s one thing to accomplish with Woman gaga! Your individual stuffs excellent. At all times care for it up! value hotel

    ReplyDelete
  12. Thanks a lot for providing individuals with an extremely splendid opportunity to read from this website. It's usually so amazing plus stuffed with fun for me and my office fellow workers to visit your blog at a minimum thrice in 7 days to read the fresh guidance you will have. And definitely, I'm just certainly astounded for the stunning tricks you serve. Some two points on this page are essentially the very best we have all had.shower tile repair

    ReplyDelete
  13. Does your site have a contact page? I'm having a tough time locating it but, I'd like to send you an email. I've got some ideas for your blog you might be interested in hearing. Either way, great website and I look forward to seeing it expand over time. HDB painting package

    ReplyDelete
  14. Therefore we acknowledge we have the blog owner to appreciate because of that. All of the explanations you made, the simple site navigation, the friendships you will help to promote - it's got many astonishing, and it's leading our son in addition to us know that this matter is pleasurable, which is really important. Thanks for everything!zonk 100 ml

    ReplyDelete
  15. Today, I went to the beachfront with my children. I found a sea shell and gave it to my 4 year old daughter and said "You can hear the ocean if you put this to your ear." She placed the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is completely off topic but I had to tell someone! Youtube views

    ReplyDelete
  16. I needed to draft you one tiny observation just to thank you so much as before for your personal magnificent concepts you have shown here. This has been really wonderfully generous of people like you in giving publicly all that most of us could possibly have marketed as an e book to help with making some money for their own end, and in particular considering the fact that you might well have done it if you ever decided. endermologie los angeles

    ReplyDelete
  17. I'm curious to find out what blog system you happen to be working with? I'm experiencing some small security issues with my latest website and I'd like to find something more safe. Do you have any solutions? bed bug exterminator nyc

    ReplyDelete
  18. Hello just wanted to give you a quick heads up. The words in your post seem to be running off the screen in Chrome. I'm not sure if this is a formatting issue or something to do with internet browser compatibility but I thought I'd post to let you know. The style and design look great though! Hope you get the issue resolved soon. Cheers branding design agency singapore

    ReplyDelete
  19. Have you ever thought about writing an ebook or guest authoring on other websites? I have a blog centered on the same ideas you discuss and would love to have you share some stories/information. I know my audience would value your work. If you are even remotely interested, feel free to shoot me an e-mail. seo companies

    ReplyDelete
  20. It is appropriate time to make a few plans for the longer term and it is time to be happy. I've read this submit and if I could I wish to recommend you some attention-grabbing issues or suggestions. Maybe you can write subsequent articles referring to this article. I want to read more things about it!houses western cape

    ReplyDelete
  21. I was suggested this website by my cousin. I'm not sure whether this post is written by him as no one else know such detailed about my difficulty. You're amazing! Thanks! good website design singapore

    ReplyDelete
  22. Today, I went to the beachfront with my children. I found a sea shell and gave it to my 4 year old daughter and said "You can hear the ocean if you put this to your ear." She placed the shell to her ear and screamed. There was a hermit crab inside and it pinched her ear. She never wants to go back! LoL I know this is totally off topic but I had to tell someone! How to write CV

    ReplyDelete
  23. Hi! Someone in my Myspace group shared this site with us so I came to look it over. I'm definitely enjoying the information. I'm bookmarking and will be tweeting this to my followers! Superb blog and amazing design.
    seo service

    ReplyDelete
  24. Wow! Thank you! I always wanted to write on my blog something like that. Can I take a fragment of your post to my site?
    web design singapore

    ReplyDelete
  25. I was reading some of your content on this website and I conceive this internet site is really informative ! Keep on putting up. Webdesign

    ReplyDelete
  26. Yesterday, while I was at work, my sister stole my iPad and tested to see if it can survive a 40 foot drop, just so she can be a youtube sensation. My apple ipad is now destroyed and she has 83 views. I know this is entirely off topic but I had to share it with someone!
    Video ads

    ReplyDelete
  27. Thanks for sharing the post.. parents are worlds best person in each lives of individual..they need or must succeed to sustain needs of the family. Webdesign

    ReplyDelete
  28. I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. Webdesign

    ReplyDelete
  29. Webdesigner waar u een professionele en betaalbare website kan laten maken? De nr. 1 webdesigner in Limburg, Antwerpen en Vlaams-Brabant voor SEO websites. Webdesigner

    ReplyDelete
  30. The reason for your site is to satisfy your particular business destinations so allude to the pith of your field-tested strategy. Webdesign

    ReplyDelete
  31. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. Webdesign bureau

    ReplyDelete
  32. Right now it looks like Wordpress is the preferred blogging platform out there right now. (from what I've read) Is that what you are using on your blog?
    ad agencies in singapore

    ReplyDelete
  33. Very good blog! Do you have any recommendations for aspiring writers? I'm hoping to start my own site soon but I'm a little lost on everything. Would you propose starting with a free platform like Wordpress or go for a paid option? There are so many choices out there that I'm totally confused .. Any ideas? Kudos!
    learn digital marketing singapore

    ReplyDelete
  34. You made some decent points there. I looked on the internet for the subject and found most persons will consent with your website. Improve Your Website’s UX

    ReplyDelete
  35. It was a very good post indeed. I thoroughly enjoyed reading it in my lunch time. Will surely come and visit this blog more often. Thanks for sharing. Web Design Company

    ReplyDelete
  36. Have you ever considered about including a little bit more than just your articles? I mean, what you say is important and all. However think of if you added some great photos or video clips to give your posts more, "pop"! Your content is excellent but with pics and videos, this blog could certainly be one of the greatest in its field. Fantastic blog! digital marketing singapore

    ReplyDelete
  37. Greetings from Carolina! I'm bored to death at work so I decided to check out your website on my iphone during lunch break. I love the information you present here and can't wait to take a look when I get home. I'm surprised at how quick your blog loaded on my phone .. I'm not even using WIFI, just 3G .. Anyhow, excellent site!
    Sec 1 maths tuition

    ReplyDelete
  38. The main motivation they ought to be maintained a strategic distance from for the genuine representative, is that their work looks unprofessional, which makes your organization look crude. Who needs that? Web Design Curriculum

    ReplyDelete
  39. Every agency and consultant has their own SEO methodology; therefore they may use different methods to achieve high organic rankings for websites.seo-ranking

    ReplyDelete
  40. i never know the use of adobe shadow until i saw this post. thank you for this! this is very helpful. web agency software

    ReplyDelete
  41. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. software gestionale negozio

    ReplyDelete
  42. The consistent requirement for organizations to process individual data implies that the DPA impacts upon most associations, regardless of size. data hk

    ReplyDelete
  43. Pasarqq tempat Bermain judi bandarq online tentunya menjadi di antara pilihan yang tidak sedikit dipilih oleh pemain website bandarq .Bisa anda lihat profile terkait pasarqq ternama melalui metode klik link https://findery.com/pasarqq , lalu https://www.thebaynet.com/profile/noverakasanti888 kemudian https://fontlibrary.org/en/member/pasarqq/ dan juga http://www.myfolio.com/pasarqq.
    Selain itu juga pasarqq ini memiliki homepage personal seperti https://pasarqq.cabanova.com/
    http://rebuildingtogethernb.org/selamat-datang-di-situs-poker-online-pasarqq/
    http://sonidoslibertarios.org/selamat-datang-di-situs-poker-online-pasarqq/
    http://jonnymugwump.com/selamat-datang-di-situs-poker-online-pasarqq/

    ReplyDelete
  44. hadir dengan fitur lengkap mengenai Data hk menjadi sesuatu yang sangat anda butuhkan serta kingdomtoto memberikan pelayanan togel online terbaik dengan pengeluaran hk

    ReplyDelete
  45. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. peter web designer

    ReplyDelete
  46. Extremely beneficial job you have done on this lovely page. While I was scrolling my mouse pointer on my web browser to gain some ideas on web development and design, suddenly I came across to your great post title on the serp page and simply hit on and explored me into your web scraping zone. Definitely, pretty helpful job for the visitors and readers and learners like me. Seriously you depicted the whole concept in an easy and smooth manner. Love your amazing work. Also I would like to share a site that I had visited a few moment back resort web design belize hoping that it might be useful to others here like me.

    ReplyDelete
  47. Halo pemburu data togel seperti data sydney bisa dilakukan melalui halaman ini dan juga data sgp beserta pengeluaran sydney.

    ReplyDelete
  48. The article was up to the point and described the information very effectively. Thanks to blog author for wonderful and informative post.
    website development company pakistan

    ReplyDelete
  49. I am incapable of reading articles online very often, but I’m happy I did today.It is very well written, and your points are well-expressed.I request you warmly, please, don’t ever stop writing. κατασκευη eshop

    ReplyDelete
  50. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. Digital marketing company

    ReplyDelete
  51. A company can provide a much higher level of expertise in web design, programming, content development and more.Web Designing

    ReplyDelete
  52. Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work. Web Designing

    ReplyDelete
  53. In the present aggressive universe of business over the web, each online business website needs most recent web innovation arrangements with best web application advancement.Webdesign

    ReplyDelete
  54. Everybody loves most of the conversations, We really skilled, I would choose more information when it comes to this particular, because it's amazing., Along with because of obtain distributing. kmspico

    ReplyDelete
  55. Its a great pleasure reading your post.Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work
    apply tools

    ReplyDelete
  56. A lot of people having an incorrect image about the cash advance loans or sometimes refer it as bad credit payday loans. website design in Accra

    ReplyDelete
  57. The toughest job to do ever is to stand up your client or boss but as a matter of fact doing this regularly can make your irksome website process a pleasant experience for yourself and for your clients as well.Webdesign

    ReplyDelete
  58. Really I enjoy your site with effective and useful information. It is included very nice post with a lot of our resources.thanks for share. i enjoy this post. HPT

    ReplyDelete