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.

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)

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

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]