2

does anyone know how to grab the # of networks (one at a time in each cell) from a list on this page? After trial and error, I think =IMPORTXML("https://www.peeringdb.com/fac/167","//div[@data-filter-value='BGP.Exchange' and @'participants']") should be most "correct," but the formula isn't working...What did I do wrong? Thanks in advance for any help.

enter image description here enter image description here

stack91
  • 51
  • 6

2 Answers2

1

try:

=QUERY(IMPORTXML(A1, "//div[@class='scrollable']/div"), 
 "select Col3 where Col3 is not null")

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • Sorry player0, I should've clarified: what if I want only one number at a time in each cell? i.imgur.com/2NTneRN.png . Thus the need to //div[@data-filter-value='BGP.Exchange – stack91 Dec 14 '21 at 18:48
  • 1
    @stack91 see: https://docs.google.com/spreadsheets/d/10PoGCgoczlcEEWhQHnhJXPTzJ85NJ2OuEQKQ-kPyukM/edit#gid=0 – player0 Dec 14 '21 at 19:21
  • Hi player0, is there a way for the formula to include //div[@data-filter-value='BGP.Exchange'? If you go to another facility, the IXs either change sequence or are totally different. This is why I have to grab the network quantity of each IX separately. This formula, =INDEX(IMPORTXML(A1, "//div[@class='scrollable']/div"), 2, 3) , for e.g., does not grab the BGP.Exchange number at a different facility. – stack91 Dec 14 '21 at 20:22
  • If I'm not wrong, "div class=exchange" and "div class = participants" are at the same div hierarchy, requiring a "two-way lookup" IMPORTXML to grab the number of networks. I think =IMPORTXML("peeringdb.com/fac/167", "//div[@data-filter-value='BGP.exchange' and @class='participants']") is close but the formula isn't working... i.imgur.com/POmM4Ph.png – stack91 Dec 14 '21 at 20:41
  • 1
    @stack91 well not rly. they are on same level of hierarchy eg. there is no direct link between them. the only common element they share is `
     – player0 Dec 14 '21 at 21:12
  • 1
    @player0 - in this case you can get 2 steps higher by `/../ ..` then check the number of participants – Mike Steelson Dec 15 '21 at 01:58
1

Try

=importxml($A$1,"//div[@data-filter-value='"&trim(A2)&"']/../..//div[@class='participants']")

enter image description here

Another solution

=arrayformula(vlookup(trim(A2),trim(importxml(A1,"//div[@class='scrollable']//div")),3,0))
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20
  • Sorry Mike, I should've clarified: what if I want only one number at a time in each cell? https://i.imgur.com/2NTneRN.png. Thus the need to //div[@data-filter-value='BGP.Exchange' – stack91 Dec 14 '21 at 18:45
  • 1
    ok, but I think that you could fetch all values at once, I have updated my proposal. – Mike Steelson Dec 14 '21 at 19:35
  • Hi Mike, is there a way for the formula to include //div[@data-filter-value='BGP.Exchange'? If you go to another facility, the IXs either change sequence or are totally different. This is why I have to grab the network quantity of each IX separately. – stack91 Dec 14 '21 at 20:22
  • If I'm not wrong, "div class=exchange" and "div class = participants" are at the same div hierarchy, requiring a "two-way lookup" IMPORTXML to grab the number of networks. I think =IMPORTXML("https://www.peeringdb.com/fac/167", "//div[@data-filter-value='BGP.exchange' and @class='participants']") is close but the formula isn't working :( https://i.imgur.com/POmM4Ph.png – stack91 Dec 14 '21 at 20:40
  • 1
    ok, the solution could be `=importxml($A$1,"//div[@data-filter-value='"&A2&"']/../..//div[@class='participants']")` : when you are at the same level, use /.. to go one step higher – Mike Steelson Dec 15 '21 at 01:52
  • 1
    I have changed my answer. – Mike Steelson Dec 15 '21 at 02:00
  • Hi Mike, solution =importxml($A$1,"//div[@data-filter-value='"&A2&"']/../..//div[@class='participants']") is exactly it but stuck on loading when I apply it in my model. It works fine when I load it in a blank sheet though. Any idea why this is the case and how I can get it working? https://i.imgur.com/vu0Rbag.png – stack91 Dec 15 '21 at 18:53
  • 1
    Check if you have extra space before or after your article, change A2 to `trim(A2)`. Howerver, why not try `=arrayformula(vlookup(trim(A2),trim(importxml(A1,"//div[@class='scrollable']//div")),3,0))` ? – Mike Steelson Dec 15 '21 at 19:45
  • Perfect, it worked! Many thanks, Mike! – stack91 Dec 16 '21 at 19:12
  • Hi Mike, can I ask you sth? When I removed the arrayformula() component, value "25" turned into a #REF (VLOOKUP evals to out of bounds range) error msg. I understand your formula up to the VLOOKUP level (match IX name in 1st col to return corresp value in 3rd col of XML table), but can you explain to me what arrayformula is doing here? – stack91 Dec 17 '21 at 20:55
  • 1
    arrayformula will expand the trim function to the full array, if you just write `=TRIM(importxml(A1,"//div[@class='scrollable']//div"))` you will only fetch one value in one column, therefor the vlookup will not be able to find any third column `=ARRAYFORMULA(vlookup(_____,_____,3,0))` and will give you #REF! error. – Mike Steelson Dec 18 '21 at 02:40
  • In that case, shouldn't VLOOKUP come before ARRAYFORMULA rather than after? – stack91 Dec 18 '21 at 06:31
  • 1
    yes, the result will be the same; but ... in my proposal you can also replace `trim(A2)` with `trim(A2:A)` to retrieve all data at one time with just one website call, that's why l put before ... `=arrayformula(if(A2:A="",,vlookup(trim(A2:A),trim(importxml(A1,"//div[@class='scrollable']//div")),3,0)))` – Mike Steelson Dec 18 '21 at 08:06
  • Interesting, when I tried =vlookup(trim(IX), arrayformula(trim(importxml(URL,"//div[@class='scrollable']//div"))),3,0), it works fine for my purposes and it's easier to explain to my colleagues what I'm doing. So arrayformula() simply "rebuilds" the table after you "destroy" it using trim(). Got it and thanks for patiently explaining! – stack91 Dec 18 '21 at 23:07