Importxml formula to help scraping from web solving paging problem

Andrei Rusak 0
Viewed 38

The site i am trying to scrape from is a marketplace with a number of pages with output results. When scraping the importxml formula is valid for the defined page (e.g. https://cars.av.by/filter?brands[0][brand]=6&brands[0][model]=1992&year[min]=2010&year[max]=2018&page=3)

Is there any solution for importxml formula tp get data from all pages?

the same request was here: list with multiple autogenerated importxml queries

My formula example for page 1: =FLATTEN(IMPORTXML("https://cars.av.by/filter?brands[0][brand]=6&brands[0][model]=1992&year[min]=2010&year[max]=2018&page=1","//main//h3/a"))

paging xml google-sheets-formula google-sheets
2 Answers

try:

=INDEX(FLATTEN(SPLIT(QUERY(IFNA(BYROW(A1&SEQUENCE(20), 
 LAMBDA(x, "×"&TEXTJOIN("×", 1, IMPORTXML(x, "//main//h3/a"))))),,9^9), "×")))

enter image description here

player00

use:

=INDEX(QUERY(REGEXREPLACE(HLOOKUP("×", {"×"; QUERY(FLATTEN(IFERROR(SPLIT(FLATTEN({
 IFERROR(ARRAY_CONSTRAIN(IMPORTXML(B1&"&page=1", "//div[@class='listing__items']/div/div/div"), 9^9, 3), {"","",""}); 
 IFERROR(ARRAY_CONSTRAIN(IMPORTXML(B1&"&page=2", "//div[@class='listing__items']/div/div/div"), 9^9, 3), {"","",""}); 
 IFERROR(ARRAY_CONSTRAIN(IMPORTXML(B1&"&page=3", "//div[@class='listing__items']/div/div/div"), 9^9, 3), {"","",""}); 
 IFERROR(ARRAY_CONSTRAIN(IMPORTXML(B1&"&page=4", "//div[@class='listing__items']/div/div/div"), 9^9, 3), {"","",""}); 
 IFERROR(ARRAY_CONSTRAIN(IMPORTXML(B1&"&page=5", "//div[@class='listing__items']/div/div/div"), 9^9, 3), {"","",""})}), ", ", ))), 
 "where Col1 is not null and not Col1 ends with 'VIN' and not Col1 contains 'ТОП'", )}, 
 SEQUENCE(5*ROWS(B:B)/11, 11, 2), ), " г.| \$$|^≈ ", ), 
 "select Col1,Col3,Col8,Col5,Col6,Col7,Col10 where Col1 is not null and not Col1 = '#REF!'", ))

0

demo sheet

player00
Related