0

I'm new to XPath and having trouble retrieving the Facility and City data from a table. My intention is to get the four facility names and their cities from the bottom left table on this webpage on Google Sheets. Being the novice I am, I use Edge's Inspect function to find the "overarching" XPath of this table that led to the formula "=TRANSPOSE(IMPORTXML("https://www.peeringdb.com/ix/863","/html/body/div2/div/div2/div1/div2/div1/div[3]"))".

GSheets Formula

This is, however, not ideal as the facilities aren't sorted into separate rows. What would be the XPath syntax for IMPORTXML to separate the Facility and City names into their respective rows? If that's too hard to achieve, I don't mind including the Country column as well even if it's redundant. Thanks in advance.

stack91
  • 51
  • 6

2 Answers2

1

With xpath 1.0, you can probably only do something like this:

Assuming your url is in A1, enter in B1

=IMPORTXML(A1,"//div[@id='list-facilities']//div[@class='row item']//div[@class='facility']")

and in B2

=IMPORTXML(A1,"//div[@id='list-facilities']//div[@class='row item']//div[@class='city']")

You should get

Cogeco Peer 1 Vancouver 21 North        Vancouver
Cologix VAN2                    Vancouver
Cologix VAN3                     Vancouver
Harbour Centre Vancouver        Vancouver

which is close to what I think you are after.

Jack Fleeting
  • 24,385
  • 6
  • 23
  • 45
1

In A2

=importxml(A1;"//div[@class='facility']")

In B2

=importxml(A1;"//div[@class='city']")

enter image description here

Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Beautiful, thanks Mike! – stack91 Sep 18 '21 at 00:22
  • Thx for your feedback, upvote and close the topic – Mike Steelson Sep 18 '21 at 00:47
  • Hey @Mike, I applied your formula =IMPORTXML("https://discover.cloudscene.com/data-center/united-kingdom/london/telehouse-telehouse-london-docklands-north-north-two","//div[@class='j1lbxw-0 sc-1pnc2j7-0 jHZiQS JgKwX']") to get the # of Service Providers from this [webpage](https://discover.cloudscene.com/data-center/united-kingdom/london/telehouse-telehouse-london-docklands-north-north-two) but it didn't work. Do you mind teaching me what I did wrong? – stack91 Sep 18 '21 at 19:00
  • Because the web page is built by javascript on customer side, not on server side ... see how to detect this here https://webapps.stackexchange.com/questions/115664/how-to-know-if-google-sheets-importdata-importfeed-importhtml-or-importxml-fun. So you will not be able to fetch data with importxml function. – Mike Steelson Sep 19 '21 at 00:28
  • Oh dear, is there no way to grab data from this page using GSheets IMPORT functions? – stack91 Sep 19 '21 at 07:09
  • Unfortunately, I guess not! I didn't find any json inside the webpage like others ... (yahoo for instance). – Mike Steelson Sep 19 '21 at 07:26
  • Oh :( Thanks nonetheless – stack91 Sep 20 '21 at 16:56
  • Hi @mike-steelson Sir, sorry to bother you; I was wondering if you could help with some IMPORTXML problems I came across (https://stackoverflow.com/questions/69318890/how-to-format-aws-regions-azs-data-into-table) – stack91 Sep 24 '21 at 17:23
  • ok, i will take a close look tomorrow morning (paris time) – Mike Steelson Sep 24 '21 at 17:41
  • Hi Mike, as a follow-up to this question, do you mind helping me check what's wrong with my formula in this new question (same website; different data to grab this time)? https://stackoverflow.com/questions/70353452/trouble-retrieving-numbers-using-importxml – stack91 Dec 14 '21 at 17:55
  • ok, done with a more complete answer to get all data from the table – Mike Steelson Dec 14 '21 at 18:38