Friday, July 12, 2013

Google Spreadsheet ImportHtml to Extract Table from a Web Page

Question:

( by Irina )

I almost got it. Need just straighten out one little issue.. and cant figure it our for nothing! 
In column A I have a link reference to an html page. using =importXML I am extracting data from a table on that page. Here is wht I came up with: 

=importxml(A1,"//div[@id='Description']//tr[td/text()[contains(.,'Price')]]/td[2]")

It works, but what I need to do is instead of putting in "(.,'Price')" , refer to a cell "L1" that contains that text "Price"


===========

Here is the link:
http://www.parts-express.com/pe/showdetl.cfm?Partnumber=290-398 ... 
"Price" was just an example, I need to extract information from the table below, such as "Power Handling (RMS)", "Power Handling (max)" ect. those are also the headers of my spreadsheet and are in the first row.. and again I would like to refer not to the text in the html, which is how its operating at the moment, but to my headers in the first row... if that makes seance.


Solution:

Have a look at the following screenshot:



I have the following formula in Cell A1:

=ImportHtml("http://www.parts-express.com/pe/showdetl.cfm?Partnumber=290-398";"table";20)


You can also now manipulate the above formula to get any value from the table, you can use query formula along with above formula to manipulate the extracted data. Or try the following formula:

=index(ImportHtml("http://www.parts-express.com/pe/showdetl.cfm?Partnumber=290-398";"table";20);2;2)


I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.


I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

1 comment:

  1. Is it possible to import the html table coloumn with hyperlinks? For example the text in the coloumn is hyperlinked and I need to import the link text along with the hyperlink. Is it possible?

    ReplyDelete