Using Google Sheets for web scraping. Importxml function xpath correction

Question

I am trying to use the importxml function in google sheets scrape the MPN (Manufacturer part number) from a website for a list of products (about 2000).

The website: http://gun.deals/search/apachesolr_search/640832007756

The instructions said to use the importxml formula. The link is A1, then used inspector in chrome to copy the xpath. The result:

 =IMPORTXML(A1,"//*[@id='content']/div[3]/div[2]/div/div/div/div/div/div[2]/dl/div[2]/dd/a")

(Instructions also said to change double quotes to single around content.)

The error is imported "content in empty" when running the formula. I have tried to make edits and I think the XPath is incorrect. Some say that you can't copy-paste from inspector as the instructions said.

One other thing I tried was =IMPORTHTML(A1, "list", 8). This returns UPC and MPN. But it puts the MPN on the second line.

I am new to this and have searched for the last couple of nights for a solution. any help would be much appreciated.

Also Bonus question. What would the formula be to scrape the UPC if we had the MPN? https://gun.deals/search/apachesolr_search/J941PSL9

Tanaike · Accepted Answer · 2019-10-29 23:49:55Z

2

You want to retrieve the value of "MPN" from the value of "UPC".
You want to retrieve the value of "UPC" from the value of "MPN".
You want to achieve this using the built-in functions of Google Spreadsheet.

If my understanding is correct, how about this sample formula? Please think of this as just one of several answers.

Pattern 1:

In this pattern, the value of "MPN" is retrieved from the value of "UPC".

Sample formula:

=IMPORTXML("http://gun.deals/search/apachesolr_search/"&A2,"//li[contains(text(),'MPN')]/a")

the xpath is //li[contains(text(),'MPN')]/a.
In this case, the value of "UPC" is put in the cell "A2".

Result:

Pattern 2:

In this pattern, the value of "UPC" is retrieved from the value of "MPN".

Sample formula:

=IMPORTXML("http://gun.deals/search/apachesolr_search/"&A2,"//li[contains(text(),'UPC')]/a")

the xpath is //li[contains(text(),'UPC')]/a.
In this case, the value of "MPN" is put in the cell "A2".

Result:

Reference:

IMPORTXML

If I misunderstood your question and this was not the result you want, I apologize.

Added:

From your replying, I modified the formulas for using the following values.

Here is a list of UPC 787450038417 787450230576 661120974888 859462004015 82442306667 810237023013 798681538782 787450348196 604206120816

UPC --> MPN:

=IMPORTXML("http://gun.deals/search/apachesolr_search/"&A2,"//dd/a[../../dt[contains(text(),'UPC')]]|//dd/span[../../dt[contains(text(),'UPC')]]")

the xpath is //dd/a[../../dt[contains(text(),'UPC')]]|//dd/span[../../dt[contains(text(),'UPC')]].
In this case, the value of "UPC" is put in the cell "A2".

MPN --> UPC:

=IMPORTXML("http://gun.deals/search/apachesolr_search/"&B2,"//dd/a")

the xpath is //dd/a.
In this case, the value of "MPN" is put in the cell "B2".

Result:

edited Oct 29, 2019 at 23:49

answered Oct 29, 2019 at 7:14

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Planterguy Over a year ago

Getting an error " Imported content is empty" on most of the items. About 1 in 20 will pull in the MPN. Here is a list of UPC 787450038417 787450230576 661120974888 859462004015 82442306667 810237023013 798681538782 787450348196 604206120816 <-- this one works. The empty content error doesn't make sense because I can see the data if I visit that page. Thank you for your help on this. It feels like we are very close.

Tanaike Over a year ago

@Planterguy Thank you for replying. I apologize for the inconvenience. In my answer is for your question. So I confirmed about the URLs of http://gun.deals/search/apachesolr_search/640832007756 and https://gun.deals/search/apachesolr_search/J941PSL9. But in your replying, I couldn't notice you want to also retrieve for other URLs. This is due to my poor skill. I deeply apologize for this. For your replying, I updated my answer. Could you please confirm it? If that was not the result you want, I have to apologize again.

Planterguy Over a year ago

Thank you for your help. This will save me a lot of time.

Collectives™ on Stack Overflow

Using Google Sheets for web scraping. Importxml function xpath correction

1 Answer 1

Pattern 1:

Sample formula:

Result:

Pattern 2:

Sample formula:

Result:

Reference:

Added:

UPC --> MPN:

MPN --> UPC:

Result:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Pattern 1:

Sample formula:

Result:

Pattern 2:

Sample formula:

Result:

Reference:

Added:

UPC --> MPN:

MPN --> UPC:

Result:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related