Skip to main content

How to import an Html table, list or feeds into a spreadsheet

Google Spreadsheet has an extremely useful function that allows you to import various kinds of data into a spreadsheet file. Suppose you find a table of useful data on a web page that you want to have it as an Excel file. That's possible with Google Spreadsheet's Import function.

The import function makes it possible to grab all kinds of online data and turn them into spreadsheet files to make analysis, create graphs etc. The =ImportHTML function has the following format:

=ImportHTM(”URL",”query”,index)

URL is the URL of the target web page; query can be either "list" or "table" and index is the order of the element (query) on the page. If a page contains multiple tables (or lists) and you want to import only the third table (or list) than the index value will be 3. Quotation marks around URL and query is necessary.

As an example, let us take this table "list of deserts in the world ordered by area" found on Wikipedia and try to import it into a spreadsheet.

google-sp-wikitable

Create a new spreadsheet file and click on any empty cell. Now type this:

=ImportHTM(”http://en.wikipedia.org/wiki/List_of_deserts_by_area", ”table”,1)

google-sp-import

The data inside the table now magically appears into the spreadsheet.

google-sp-table

To import XML files use the command =importXML("URL","query")

Here query is the XPath query to run on the data. For example, =importXml("www.yahoo.com", "//a/@href") will get you all the links on Yahoo's page.

Similarly, =importData("URL") imports all the data from a comma separated CSV or TSV file.

=ImportFeed("URL") imports an RSS or ATOM feed into the spreadsheet. For example, =ImportFeed("http://feeds.feedburner.com/InstantFundas") will import this blog's feed into a spreadsheet.

Google Spreadsheet is a winner!

[via OUseful]

Comments

  1. Also check out the firefox extension TableTools at https://addons.mozilla.org/en-US/firefox/addon/2637

    ReplyDelete

Post a Comment

Popular posts from this blog

How to Record CPU and Memory Usage Over Time in Windows?

Whenever the computer is lagging or some application is taking too long to respond, we usually fire up task manager and look under the Performance tab or under Processes to check on processor utilization or the amount of free memory available. The task manager is ideal for real-time analysis of CPU and memory utilization. It even displays a short history of CPU utilization in the form of a graph. You get a small time-window, about 30 seconds or so, depending on how large the viewing area is.

How to Schedule Changes to Your Facebook Page Cover Photo

Facebook’s current layout, the so called Timeline, features a prominent, large cover photo that some people are using in a lot of different creative ways. Timeline is also available for Facebook Pages that people can use to promote their website or business or event. Although you can change the cover photo as often as you like, it’s meant to be static – something which you design and leave it for at least a few weeks or months like a redesigned website. However, there are times when you may want to change the cover photo frequently and periodically to match event dates or some special promotion that you are running or plan to run. So, here is how you can do that.

Diagram 101: Different Types of Diagrams and When To Use Them

Diagrams are a great way to visualize information and convey meaning. The problem is that there’s too many different types of diagrams, so it can be hard to know which ones you should use in any given situation. To help you out, we’ve created this diagram that lays out the 7 most common types of diagrams and when they’re best used: